/*該試驗針對按照遞增列作為分區條件的情況，按照時間作為分區條件的等下次測試*/
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_aaminvalue 1maxvalue 60000start with 1increment by 1cache 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 /*注重：這里刪除的分區要從后面刪，因為oracle不答應向前添加分區，只能向后添加分區*/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);
--本條將報錯，提示無此分區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; --將無數據顯示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); --將無數據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; --將無數據顯示imp test/test@myora FILE=e:/test03.dmp TABLES = (cdr:part_03) IGNORE=yselect 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); --將無數據顯示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;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條數據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條數據select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000;select count(*) from test where aa > 1000001; --將有大量數據統計出select count(*) from test partition(part_02) where aa > 1000001; --結果為0條 alter table cdr split partition part_02 at
(50000) into(partition part_02,partition part_06 tablespace test01p2); 通過這個測試，創建好的分區可以直接使用分區拆分功能對現有的分區進行分割，分割時可以指定表空間。因此，在這里建議以后創建分區的命名和表空間的命名時可以從第二位或者第三位開始命名：如part_01,part_02,part_03.....，這樣可以為以后的分區拆分留下擴展空間（可以按照PB中的DataWindow的思想做）。
