Oracle interval partition table lock partition Operation overview

In the document, the partition's locking operation is performed before performing some operations on the interval partition.

Unlike other types of partitioned tables, the partition of the interval partition table does not necessarily exist, and whether the partition exists has to do with the range of data that the user inserts.

As you can see, the document performs a lock partition before performing partition operations on the interval partition, in fact this lock operation is not to avoid the effect of user DML on DDL operations, but to ensure that the partition to be manipulated exists:

sql> CREATE TABLE T_part_inter

2 (ID number,

3 NAME VARCHAR2 (30),

4 create_date DATE)

5 PARTITION by RANGE (ID)

6 INTERVAL (100)

7 (PARTITION P1 VALUES less THAN (100),

8 PARTITION P2 VALUES less THAN (200));

Table has been created.

Sql> SELECT table_name, partition_name, High_value

2 from User_tab_partitions

3 WHERE table_name = ' t_part_inter ';

TABLE_NAME Partition_name High_value

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

T_part_inter P1 100

T_part_inter P2 200

sql> ALTER TABLE T_part_inter

2 Move PARTITION for (250);

ALTER TABLE T_part_inter

*

Line 1th Error:

ORA-02149: The specified partition does not exist

sql> LOCK TABLE T_part_inter

2 PARTITION for (250)

3 in SHARE MODE;

The table is locked.

sql> ALTER TABLE T_part_inter

2 Move PARTITION for (250);

The table has changed.

Sql> SELECT table_name, partition_name, High_value

2 from User_tab_partitions

3 WHERE table_name = ' t_part_inter ';

TABLE_NAME Partition_name High_value

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

T_part_inter P1 100

T_part_inter P2 200

T_part_inter sys_p102 300

As you can see, LOCK table allows Oracle to add a interval partition.

From this phenomenon, the new data will cause interval new partitions, lock PARTITION will also cause new partitions, and new data will cause LOCK PARTITION, Oracle is likely to have added to the interval partition after monitoring the lock partition operation.

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

sql> LOCK TABLE T_part_inter

2 PARTITION for (320)

3 in ROW SHARE MODE;

The table is locked.

sql> LOCK TABLE T_part_inter

2 PARTITION for (430)

3 in ROW EXCLUSIVE MODE;

The table is locked.

sql> LOCK TABLE T_part_inter

2 PARTITION for (560)

3 in SHARE ROW EXCLUSIVE MODE;

The table is locked.

sql> LOCK TABLE T_part_inter

2 PARTITION for (670)

3 in EXCLUSIVE MODE;

The table is locked.

sql> LOCK TABLE T_part_inter

2 PARTITION for (780)

3 in SHARE UPDATE MODE;

The table is locked.

Sql> SELECT table_name, partition_name, High_value

2 from User_tab_partitions

3 WHERE table_name = ' t_part_inter ';

TABLE_NAME Partition_name High_value

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