Oracle list partitions add values or drop values contain data changes

As mentioned in introducing add values and drop values statements, add values and drop values are just changes in the data dictionary and do not involve changes in the data. Therefore, if the add values or drop values statements are executed, the new or deleted key values already exist in the database, an error is made.

Still borrow the example from the previous article:

sql> CREATE TABLE t_part_list

2 (

3 OWNER VARCHAR2 (30),

4 NAME VARCHAR2 (30),

5 Tablespace_name VARCHAR2 (30),

6 TYPE VARCHAR2 (18)

7)

8 PARTITION by LIST (Tablespace_name)

9 (

PARTITION P1 VALUES (' SYSTEM '),

One PARTITION P2 VALUES (' yangtk '),

PARTITION P3 VALUES (' USERS '),

PARTITION P4 VALUES (DEFAULT)

14);

Table has been created.

Sql> INSERT into T_part_list

2 SELECT OWNER, Segment_name, Tablespace_name, Segment_type

3 from Dba_segments;

5628 lines have been created.

Sql> COMMIT;

Submit completed.

In general, we do not execute the following sql:

sql> ALTER TABLE t_part_list

2 MODIFY PARTITION P2

3 ADD VALUES (' USERS ');

ALTER TABLE T_part_list

*

Line 1th Error:

ORA-14312: Value ' USERS ' already exist in partition 3

Obviously the key value ' USERS ' corresponds to another partition, which requires only the merge partitions operation:

sql> ALTER TABLE t_part_list

2 MERGE partitions P2, P3

3 into PARTITION P2;

The table has changed.

Sql>coltable_name FORMAT A15

Sql>colpartition_name FORMAT A15

Sql>colhigh_value FORMAT A30

Sql> SELECT table_name, partition_name, High_value

2 from User_tab_partitions

3 WHERE table_name = ' t_part_list ';

TABLE_NAME Partition_name High_value

--------------- --------------- ------------------------------

T_part_list P1 ' SYSTEM '

T_part_list P2 ' USERS ', ' YANGTK '

T_part_list P4 DEFAULT

The need for this add values is easy to solve. The more likely types of requirements are the following SQL:

sql> ALTER TABLE t_part_list

2 MODIFY PARTITION P1

3 ADD VALUES (' Sysaux ');

ALTER TABLE T_part_list

*

Line 1th Error:

ORA-14324: The value you want to add already exists in the default partition

sql> SELECT DISTINCT Tablespace_name

2 from T_part_list PARTITION (P4);

Tablespace_name

------------------------------

Sysaux

UNDOTBS1

In this case, there is no way to use a SQL to complete the operation, you need to first split the default partition, and then the merge:

sql> ALTER TABLE t_part_list

2 SPLIT PARTITION for (' Sysaux ')

3 VALUES (' Sysaux ')

4 into (PARTITION P3, PARTITION P4);

The table has changed.

sql> ALTER TABLE t_part_list

2 MERGE partitions for (' SYSTEM '), for (' Sysaux ')

3 into PARTITION P1;

The table has changed.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/