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 :
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
不是相邻的分区不能合并
只能合并相邻的
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
SQL> CREATE TABLE inv_hisTORY
2 (inv_no NUMBER(3), inv_date DATE, inv_amt NUMBER(10,2))
3 partition by range (inv_date)
4 interval (numtoyminterval(1,'month'))
5 (partition p0 values less than (to_date('01-01-2005','dd-mm-yyyy')),
6 partition p1 values less than (to_date('01-01-2006','dd-mm-yyyy'))
7 );
Table created
SQL> insert into INV_HISTORY values(3, '03-dec-2006', 3000);
1 row inserted
SQL> rollback;
Rollback complete
SQL> insert into INV_HISTORY values(1, '30-dec-2004', 1000);
1 row inserted
SQL> insert into INV_HISTORY values(2, '30-dec-2005', 2000);
1 row inserted
SQL> insert into INV_HISTORY values(3, '1-dec-2006', 3000);
1 row inserted
SQL> rollback;
Rollback complete
SQL> insert into INV_HISTORY values(1, '30-dec-2004', 1000);
1 row inserted
SQL> insert into INV_HISTORY values(2, '30-dec-2005', 2000);
1 row inserted
SQL> insert into INV_HISTORY values(3, '1-feb-2006', 3000);
1 row inserted
SQL> insert into INV_HISTORY values(4, '1-mar-2006', 4000);
1 row inserted
SQL> insert into INV_HISTORY values(5, '1-apr-2006', 5000);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT partition_name,high_value FROM user_tab_partitions;
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P0 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42 TO_DATE(' 2006-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P43 TO_DATE(' 2006-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P44 TO_DATE(' 2006-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
6 rows selected
不是相邻的分区不能合并
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
FOR(TO_DATE('15-feb-2006','dd-mon-yyyy')), FOR(TO_DATE('15-apr-2006')) INTO PARTITION sys_py
ORA-14274: partitions being merged are not adjacent
只能合并相邻的
SQL> ALTER TABLE inv_history MERGE PARTITIONS
2 FOR(TO_DATE('2006-02-01', 'YYYY-MM-DD')), FOR(TO_DATE('2006-03-01', 'YYYY-MM-DD')) INTO PARTITION sys_py;
Table altered