本文共 3396 字,大约阅读时间需要 11 分钟。
下面做个实验来说明:
1.创建测试分区表
SQL> create table orders( id number, country_code varchar2(5), customer_id number, order_date date, order_total number(8,2), constraint orders_pk primary key (id))partition by list (country_code)( partition part_usa values ('USA'), partition part_uk_and_ireland values ('GBR', 'IRL')); 2 3 4 5 6 7 8 9 10 11 12 13 14 Table created.SQL> insert into orders values (1, 'USA', 10, sysdate, 10200.93);1 row created.SQL> insert into orders values (2, 'USA', 11, sysdate, 948.22);1 row created.SQL> insert into orders values (3, 'GBR', 22, sysdate, 300.83);1 row created.SQL> insert into orders values (4, 'IRL', 43, sysdate, 978.43);1 row created.SQL> commit;Commit complete.
SQL> insert into orders values (5, 'BGR', 96, sysdate, 2178.43);insert into orders values (5, 'BGR', 96, sysdate, 2178.43) *ERROR at line 1:ORA-14400: inserted partition key does not map to any partition
SQL> alter table orders set partitioning automatic;Table altered.SQL> insert into orders values (5, 'BGR', 96, sysdate, 2178.43);1 row created.SQL> commit;Commit complete.
4.或者,我们可以使用AUTOMATIC关键字重新创建表格。
SQL> drop table orders purge;Table dropped.SQL> create table orders( id number, country_code varchar2(5), customer_id number, order_date date, order_total number(8,2), constraint orders_pk primary key (id))partition by list (country_code) automatic( partition part_us 2 3 4 5 6 7 8 9 10 11 12 a values ('USA'), partition part_uk_and_ireland values ('GBR', 'IRL')); 13 14 Table created.SQL> insert into orders values (1, 'USA', 10, sysdate, 10200.93);1 row created.SQL> insert into orders values (2, 'USA', 11, sysdate, 948.22);1 row created.SQL> insert into orders values (3, 'GBR', 22, sysdate, 300.83);1 row created.SQL> insert into orders values (4, 'IRL', 43, sysdate, 978.43);1 row created.SQL> commit;Commit complete.SQL> select * from orders; ID COUNT CUSTOMER_ID ORDER_DAT ORDER_TOTAL---------- ----- ----------- --------- ----------- 1 USA 10 08-FEB-18 10200.93 2 USA 11 08-FEB-18 948.22 3 GBR 22 08-FEB-18 300.83 4 IRL 43 08-FEB-18 978.43SQL> insert into orders values (5, 'BGR', 96, sysdate, 2178.43);1 row created.SQL> commit;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'orders', cascade => TRUE);PL/SQL procedure successfully completed.SQL> set linesize 100SQL> column table_name format a30SQL> column partition_name format a30SQL> column high_value format a15SQL> select table_name, partition_name,high_value,num_rows from user_tab_partitions order by 1, 2;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS------------------------------ ------------------------------ --------------- ----------ORDERS PART_UK_AND_IRELAND 'GBR', 'IRL' 2ORDERS PART_USA 'USA' 2ORDERS SYS_P303 'BGR' 1
SQL> column table_name format a30SQL> column autolist format a8SQL> select table_name,autolist from user_part_tables;TABLE_NAME AUTOLIST------------------------------ --------ORDERS YES
转载地址:http://ndhji.baihongyu.com/