파티션 테이블 생성 및 삭제(partition table)
CREATE TABLE part_test (EMPNO NUMBER(5),name varchar2(20))
PARTITION BY RANGE(EMPNO)(
partition emp_p1 VALUES LESS THAN (100),
partition emp_p2 VALUES LESS THAN (1000),
partition emp_p3 VALUES LESS THAN (MAXVALUE));
SQL> select * from part_test partition(emp_p1) ;
EMPNO NAME
---------- --------------------
1 111
SQL> select * from part_test ;
EMPNO NAME
---------- --------------------
1 111
10001 10001
SQL> select TABLE_NAME,PARTITION_NAME from dba_tab_partitions where table_name='PART_TEST';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PART_TEST EMP_P1
PART_TEST EMP_P2
PART_TEST EMP_P3
alter table part_test drop partition emp_p1 ;
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where table_name='PART_TEST';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------
PART_TEST EMP_P2 1000
PART_TEST EMP_P3 MAXVALUE
SQL> select * from part_test ;
EMPNO NAME
---------- --------------------
10001 10001
SQL> alter table part_test split partition emp_p2 at(500)
2 into (partition emp_p1,partition emp_p2) ;
Table altered.
add partition은 불가능하다.
SQL> alter table part_test add partition emp_p1 values less than(100) ;
alter table part_test add partition emp_p1 values less than(100)
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where table_name='PART_TEST';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------
PART_TEST EMP_P2 1000
PART_TEST EMP_P3 MAXVALUE
PART_TEST EMP_P1 500
SQL> alter table part_test split partition emp_p3 at(2000)
2 into (partition emp_p3,partition emp_p4) ;
DBA_TAB_PARTITIONS