Oracle OCP 1Z0 053 Q17(Auto Partition 自動分割槽)

17.The INV_HISTORY table is created using the command: 
SQL>CREATE TABLE INV_HISTORY (inv_no NUMBER(3), inv_date DATE, inv_amt NUMBER(10,2)) 
partition by range (inv_date) interval 
(numtoyminterval(1,'month')) (partition p0 
values less than (to_date('01-01-2005','dd-mm-yyyy')), partition p1 values less than 
(to_date('01-01-2006','dd-mm-yyyy'))); 
The following data has been inserted into the INV_HISTORY table : 
INV_NO INV_DATE INV_AMT
1 30-dec-2004 1000
2 30-dec-2005 2000
3 1-feb-2006 3000
4 1-mar-2006 4000
5 1-apr-2006 5000 


You would like to store the data belonging to the year 2006 in a single partition and issue the command: 
SQL> ALTER TABLE inv_history MERGE PARTITIONS 
FOR(TO_DATE('15-feb-2006','dd-mon-yyyy')), FOR(TO_DATE('15-apr-2006')) INTO PARTITION sys_py; 
What would be the outcome of this command? 
A. It executes successfully, and the transition point is set to '1-apr-2006'. 
B. It executes successfully, and the transition point is set to '15-apr-2006'. 
C. It produces an error because the partitions specified for merging are not adjacent. 
D. It produces an error because the date values specified in the merge do not match the date values 
stored in the table. 
Answer: C 

這裡, 11g之後使用interval來實現自動擴充套件分割槽，簡化了維護。
 interval (numtoyminterval(1,'month'))一個月一個分割槽.
插入資料之後，產生了新的分割槽：



實驗：
SQL> CREATE TABLE INV_HISTORY (inv_no NUMBER(3), inv_date DATE, inv_amt NUMBER(10,2)) 
  2  partition by range (inv_date) interval 
  3  (numtoyminterval(1,'month')) 
  4  (partition p0 values less than (to_date('01-01-2005','dd-mm-yyyy')), 
  5  partition p1 values less than  (to_date('01-01-2006','dd-mm-yyyy')));


Table created.


SQL> insert into INV_HISTORY values(1, to_date('30-dec-2004', 'dd-mon-yyyy'), 1000);


1 row created.


SQL> insert into INV_HISTORY values(2, to_date('30-dec-2005', 'dd-mon-yyyy'), 1000);


1 row created.


SQL> insert into INV_HISTORY values(3, to_date('1-feb-2006', 'dd-mon-yyyy'), 1000);


1 row created.


SQL> insert into INV_HISTORY values(4, to_date('30-mar-2006', 'dd-mon-yyyy'), 1000);


1 row created.


SQL> insert into INV_HISTORY values(5, to_date('30-apr-2006', 'dd-mon-yyyy'), 1000);


1 row created.


SQL> commit;


Commit complete.


SQL> ALTER TABLE inv_history MERGE PARTITIONS
  2  FOR(TO_DATE('15-feb-2006','dd-mon-yyyy')), FOR(TO_DATE('15-apr-2006')) INTO PARTITION sys_py;
ALTER TABLE inv_history MERGE PARTITIONS
*
ERROR at line 1:
ORA-14274: partitions being merged are not adjacent

這裡說的是分割槽不相鄰，不能合併

改變合併分割槽的條件：

SQL> ALTER TABLE inv_history MERGE PARTITIONS
  2      FOR(TO_DATE('15-feb-2006','dd-mon-yyyy')), FOR(TO_DATE('15-mar-2006')) INTO PARTITION sys_py;
ALTER TABLE inv_history MERGE PARTITIONS
Table altered.

合併分割槽之後，分割槽調整為：


從這裡看到，Oracle 11G的自動分割槽特性還算是挺只能的

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2075295/，如需轉載，請註明出處，否則將追究法律責任。