Oracle练习系列一：表自动分区

一、说明

Oracle12.2版本前，如果LIST分区插入一个不存在的分区键，将会报错。

Oracle12.2版本以后，通过AUTOMATIC关键字可实现插入一个不存在的分区键，并自动分区。

二、AUTOMATIC的使用

创建测试表和插入测试数据

CREATE TABLE test_part (test_id INT, people_type VARCHAR2(50), people_name VARCHAR2(50));
INSERT INTO test_part VALUES (1,'GoodPeople','今晚穿越直播');INSERT INTO test_part VALUES (2,'GoodPeople','明晚穿越直播');INSERT INTO test_part VALUES (3,'BadPeople','小小强苟仗人势');INSERT INTO test_part VALUES (4,'BadPeople','小小洋舔苟一个');
COMMIT;

查询数据

SELECT * FROM test_part;

查看TEST_PART表是否分区

SELECT table_name,partition_name FROM User_Tab_Partitions WHERE table_name = 'TEST_PART';

修改TEST_PART表为自动分区表

ALTER TABLE test_part MODIFY PARTITION BY LIST (people_type)AUTOMATIC (PARTITION p_gp VALUES ('GoodPeople'), PARTITION p_bp VALUES ('BadPeople'));

查看TEST_PART表是否为分区表

SELECT table_name,partition_name FROM User_Tab_Partitions WHERE table_name = 'TEST_PART';

查看TEST_PART表是否为自动分区

SELECT table_name,autolist FROM User_Part_Tables WHERE table_name = 'TEST_PART';

查看分区数据是否正确

SELECT * FROM test_part PARTITION(p_gp);

SELECT * FROM test_part PARTITION(p_bp);

插入一条不存在的分区键验证自动分区功能

INSERT INTO test_part VALUES (4,'RegularPeople','普通人');COMMIT;

查看TEST_PART表分区情况，发现自动多了一个分区，

分区名为SYS_P222

SELECT table_name,partition_name FROM User_Tab_Partitions WHERE table_name = 'TEST_PART';

查看分区SYS_P222数据

SELECT * FROM test_part PARTITION(sys_p222);

系统自动命名的分区名难以分辨，

可以修改分区名为自定义名称

ALTER TABLE test_part RENAME PARTITION sys_p222 TO p_rp;

再次查看分区名，发现已修改为自定义名称

SELECT table_name,partition_name FROM User_Tab_Partitions WHERE table_name = 'TEST_PART';

三、如何修改自动分区

修改TEST_PART表为不自动分区

ALTER TABLE test_part SET partitioning MANUAL;SELECT table_name,autolist FROM User_Part_Tables WHERE table_name = 'TEST_PART';

此时插入不存在的分区键会发生错误

INSERT INTO test_part VALUES (5,'Unknown','未知');

将TEST_PART表修改为自动分区表

ALTER TABLE test_part SET partitioning AUTOMATIC;SELECT table_name,autolist FROM User_Part_Tables WHERE table_name = 'TEST_PART';

此时可以插入该数据

INSERT INTO test_part VALUES (5,'Unknown','未知');COMMIT;
SELECT * FROM test_part;

====================

微信公众号：今晚穿越zhibo

墨天轮：今晚穿越直播

CSDN：今晚穿越直播