Oracle database table Scope partitioning policy testing process

* * The test is for the next test, according to the condition of the increment column as the partitioning condition, according to the time as the partition condition.

Create tablespace test01 datafile ' D:\oracle\oradata\myora\test01.dbf ' size 50m;


Create tablespace test02 datafile ' D:\oracle\oradata\myora\test02.dbf ' size 50m;


Create tablespace test03 datafile ' D:\oracle\oradata\myora\test03.dbf ' size 50m;


Create tablespace test_idx01 datafile ' D:\oracle\oradata\myora\test_idx01.dbf ' size 20m;


Create tablespace test_idx02 datafile ' D:\oracle\oradata\myora\test_idx02.dbf ' size 20m;


Create tablespace test_idx03 datafile ' D:\oracle\oradata\myora\test_idx03.dbf ' size 20m;


Create table Test (


aa Integer,


BB varchar2 (100)


)


partition by Rang (AA) (


Partition part_01 values less than (10000) tablespace test_idx01,


Partition part_02 values less than (30000) tablespace test_idx02,


Partition part_03 values less than (maxvalue) tablespace test_idx03


);


Create Index idx_test_aa on test (AA) Local


(


Partition part_01 tablespace test_idx01,


Partition part_02 tablespace test_idx02,


Partition part_03 tablespace test_idx03


);


--Create sequence


Create sequence Seq_test_aa


MinValue 1


MaxValue 60000


start with 1


Increment by 1


Cache 10;


INSERT into test values (seq_test_aa.nextval, ' test ');


Select Min (callid), Max (Callid), Count (*) from Test partition (PART_01);


Select Min (callid), Max (Callid), Count (*) from Test partition (PART_02);


Select Min (callid), Max (Callid), Count (*) from Test partition (PART_03);


Select Min (callid), Max (Callid), Count (*) from test Where Callid < 10000;


Select Min (callid), Max (Callid), Count (*) from test Where callid > 10000 and Callid < 30000;


Select Min (callid), Max (Callid), Count (*) from test Where callid >= 30000;


exp Test/test@myora file=e:\test01.dmp buffer=8192000 tables=test:part_01 rows=y


exp Test/test@myora file=e:\test02.dmp buffer=8192000 tables=test:part_02 rows=y


exp Test/test@myora file=e:\test03.dmp buffer=8192000 tables=test:part_03 rows=y


/* Note: The partitions removed here are to be deleted from the back because Oracle does not allow you to add partitions forward, you can only add partitions backwards/


ALTER TABLE CDR drop partition part03;


Select Min (aa), Max (AA), Count (*) from Test partition (PART_01);


Select Min (aa), Max (AA), Count (*) from Test partition (PART_02);


Select Min (aa), Max (AA), Count (*) from Test partition (PART_03); --This article will be an error, indicating no this section


Select Min (aa), Max (AA), Count (*) from Test Where AA < 3000000;


Select Min (AA), Max (AA), Count (*) from Test Where AA > 3000000 and AA < 6000000;


Select Min (aa), Max (AA), Count (*) from Test Where AA >= 6000000; --no data will be displayed


ALTER TABLE Test add partition part03 values less than (MaxValue) tablespace test03;


Select Min (aa), Max (AA), Count (*) from Test partition (PART_01);


Select Min (aa), Max (AA), Count (*) from Test partition (PART_02);


Select Min (aa), Max (AA), Count (*) from Test partition (PART_03); --There will be no data


Select Min (aa), Max (AA), Count (*) from Test Where AA < 3000000;


Select Min (aa), Max (AA), Count (*) from Test Where AA > 3000000 and AA < 6000000;


Select Min (aa), Max (AA), Count (*) from Test Where AA >= 6000000; --no data will be displayed


imp test/test@myora file=e:\test03.dmp TABLES = (cdr:part_03) ignore=y


Select Min (aa), Max (AA), Count (*) from Test partition (PART_01);


Select Min (AA), Max (AA), Count (*) from Test partition (PART_02);


Select Min (aa), Max (AA), Count (*) from Test partition (PART_03);


Select Min (aa), Max (AA), Count (*) from Test Where AA < 3000000;


Select Min (aa), Max (AA), Count (*) from Test Where AA > 3000000 and AA < 6000000;


Select Min (aa), Max (AA), Count (*) from Test Where AA >= 6000000;


Delete from test partition (PART_02);


commit;


Select Min (aa), Max (AA), Count (*) from Test partition (PART_01);


Select Min (aa), Max (AA), Count (*) from Test partition (PART_02); --no data will be displayed


Select Min (aa), Max (AA), Count (*) from Test partition (PART_03);


Select Min (aa), Max (AA), Count (*) from Test Where AA < 3000000;


Select Min (aa), Max (AA), Count (*) from Test Where AA > 3000000 and AA < 6000000; --no data will be displayed


Select Min (aa), Max (AA), Count (*) from Test Where AA >= 6000000;


INSERT into test values (40000, ' test ');


commit;


Select Min (aa), Max (AA), Count (*) from Test partition (PART_01);


Select Min (aa), Max (AA), Count (*) from Test partition (PART_02); --1 data will be displayed


Select Min (aa), Max (AA), Count (*) from Test partition (PART_03);


Select Min (aa), Max (AA), Count (*) from Test Where AA < 3000000;


Select Min (aa), Max (AA), Count (*) from Test Where AA > 3000000 and AA < 6000000; --1 data will be displayed


Select Min (aa), Max (AA), Count (*) from Test Where AA >= 6000000;


Select COUNT (*) from Test where AA > 1000001; --There will be a lot of data to count out


Select COUNT (*) from Test partition (part_02) where AA > 1000001; --the result is 0 strips


ALTER TABLE CDR split partition part_02 at (50000) into (partition part_02,partition part_06 tablespace);

With this test, the created partition can be used to split the existing partition directly using the partition split, and the table space can be specified when splitting. Therefore, it is suggested here that the naming of the partition and the name of the table space should be named later, starting with the second or third place: such as part_01,part_02,part_03 ... This allows for future partitions to be split to leave the extended space (can be done according to the DataWindow of Pb).