Oracle DB configuration - 3 metric columns -> Measurements table contains 3 named metric columns: Humidity, Precipitation, Temperature

three.sql

create tablespace TBS1 DATAFILE AUTOEXTEND ON;

truncate table Measurements;
drop table Measurements;
truncate table Sensors;
drop table Sensors;



create table Sensors(   Id NUMBER(6) GENERATED BY DEFAULT ON NULL AS IDENTITY primary key,   Name varchar(20)) Tablespace TBS1;
create table Measurements(   Sensor number(6),
   constraint Sensor_fk foreign key (Sensor) references Sensors(Id),
   SampleTime timestamp(3) not null,
   Humidity float,
   Precipitation float,
   Temperature float,
   constraint Measurements_PK primary key (Sensor, SampleTime)
) Tablespace TBS1;

create unique index idx_measurement on Measurements (Sensor, SampleTime desc);
select bytes from dba_segments where segment_name='IDX_MEASUREMENT';
analyze index idx_measurement validate structure;
select btree_space from index_stats;

create or replace procedure load_test_data as
        start_time timestamp;
        time_counter timestamp;
        time_max timestamp := to_timestamp('25.06.2015 00:00:00', 'DD.MM.YYYY HH24:MI:SS');

        sensor_counter number := 1;
        sensor_max number := 100;
begin
  while sensor_counter <= sensor_max loop
        INSERT INTO Sensors (Id,Name) values (sensor_counter, concat('Sensor',CAST(sensor_counter AS VARCHAR(20))));
        time_counter := to_timestamp( '24.06.2015 00:00:00', 'DD.MM.YYYY HH24:MI:SS');
        while time_counter < time_max loop
                INSERT INTO Measurements (Sensor,Humidity,Precipitation,Temperature,SampleTime) values (sensor_counter,20+DBMS_RANDOM.VALUE()*60, DBMS_RANDOM.VALUE()*3,20+DBMS_RANDOM.VALUE()*60, time_counter);
         --increment by 10 seconds               
        time_counter := time_counter + 1/8640;
        end loop;
        commit;
    sensor_counter := 1 + sensor_counter;
  end loop;
end load_test_data;
/

call load_test_data();

select sum(BYTES) from dba_segments where segment_name='MEASUREMENTS';

VARIABLE total_blocks NUMBER
VARIABLE total_bytes NUMBER
VARIABLE unused_blocks NUMBER
VARIABLE unused_bytes NUMBER
VARIABLE lastextf NUMBER
VARIABLE last_extb NUMBER
VARIABLE lastusedblock NUMBER
exec DBMS_SPACE.UNUSED_SPACE('SYS', 'MEASUREMENTS', 'TABLE', :total_blocks, -
    :total_bytes,:unused_blocks, :unused_bytes, :lastextf, -
    :last_extb, :lastusedblock);
print

exec DBMS_SPACE.UNUSED_SPACE('SYS', 'SENSORS', 'TABLE', :total_blocks, -
    :total_bytes,:unused_blocks, :unused_bytes, :lastextf, -
    :last_extb, :lastusedblock);

analyze table Measurements compute statistics;
compute sum of blocks on report
break on report
select extent_id, bytes, blocks from user_extents where segment_name='MEASUREMENTS'  and segment_type='TABLE';
clear breaks;
select blocks, empty_blocks, avg_space, num_freelist_blocks from user_tables where table_name='MEASUREMENTS';

select bytes from dba_segments where segment_name='IDX_MEASUREMENT';
analyze index idx_measurement validate structure;
select btree_space from index_stats;