Partition Storage — 2 : New Rows Inserted in 12.1 Partitioned Table

Following up from the previous post, given the current sizes of the Partitions (The underlying Tablespace is AUTOALLOCATE) : SQL> col segment_name format a30SQL> col partition_name format a12SQL> l 1 select segment_name, partition_name, segment_type, bytes/1024 2 from user_segments 3 where segment_name like 'MY_PART_%' 4* order by 1,2SQL> /SEGMENT_NAME PARTITION_NA SEGMENT_TYPE BYTES/1024------------------------------ ------------ ------------------ ----------MY_PART_TBL P_100 TABLE PARTITION 8192MY_PART_TBL P_200 TABLE PARTITION 8192MY_PART_TBL P_300 TABLE PARTITION 8192MY_PART_TBL P_400 TABLE PARTITION 8192MY_PART_TBL_NDX P_100 INDEX PARTITION 64MY_PART_TBL_NDX P_200 INDEX PARTITION 64MY_PART_TBL_NDX P_300 INDEX PARTITION 64MY_PART_TBL_NDX P_400 INDEX PARTITION 648 rows selected.SQL> I shall now insert rows so that a Partition has to grow beyond the first extent.

FBDA — 5 : Testing AutoPurging

Tracking data changes after one row added (ID_COLUMN=2000) on 06-Apr SQL> select systimestamp from dual;SYSTIMESTAMP---------------------------------------------------------------------------06-APR-16 10.53.20.328132 PM +08:00SQL> select scn_to_timestamp(startscn), scn_to_timestamp(endscn), count(*) 2 from sys_fba_hist_93250 3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn) 4 order by 1,2;SCN_TO_TIMESTAMP(STARTSCN)---------------------------------------------------------------------------SCN_TO_TIMESTAMP(ENDSCN)--------------------------------------------------------------------------- COUNT(*)----------02-APR-16 11.32.55.000000000 PM02-APR-16 11.46.11.000000000 PM 45002-APR-16 11.32.55.000000000 PM03-APR-16 11.45.24.000000000 PM 55002-APR-16 11.46.11.000000000 PM03-APR-16 11.41.33.000000000 PM 502-APR-16 11.46.11.000000000 PM03-APR-16 11.45.24.000000000 PM 44503-APR-16 11.41.33.000000000 PM03-APR-16 11.45.24.000000000 PM 503-APR-16 11.45.24.000000000 PM04-APR-16 11.05.33.000000000 PM 100006-APR-16 10.40.43.000000000 PM06-APR-16 10.42.54.000000000 PM 17 rows selected.SQL> SQL> select count(*) from sys_fba_tcrv_93250; COUNT(*)---------- 1002SQL> More changes on 07-Apr SQL> insert into test_fbda 2 select 3000, to_char(3000), trunc(sysdate) 3 from dual;1 row created.SQL> commit;Commit complete.SQL> update test_fbda 2 set date_inserted=date_inserted 3 where id_column=3000;1 row updated.SQL> delete test_fbda 2 where id_column < 1001 ;1000 rows deleted.SQL> commit;Commit complete.SQL> SQL> l 1 select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*) 2 from sys_fba_hist_93250 3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn) 4* order by 1,2,3SQL> /STARTTIME ENDTIME COUNT(*)-------------------------------- -------------------------------- ----------02-APR-16 11.32.55.000000000 PM 02-APR-16