drop table t1 purge; create table t1 (a number,b number,c number) partition by range(a) (partition p1 values less than (1000), partition p2 values less than(2000), partition p3 values less than(3000), partition p4 values less than(4000));
insert into t1 select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level >= 2); commit;
create index idx_local on t1(b) local; create index idx_global on t1(c);
删除分区
1 2 3 4
ALTER TABLE t1 DROP PARTITIONS p1,p2;
Table altered.
检查分区和索引
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
SELECT TABLE_NAME, partition_name FROM user_tab_partitions WHERE TABLE_NAME='T1';
drop table t1 purge; create table t1 (a number,b number,c number) partition by range(a) (partition p1 values less than (1000), partition p2 values less than(2000), partition p3 values less than(3000), partition p4 values less than(4000));
insert into t1 select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level >= 2); commit;
create index idx_local on t1(b) local; create index idx_global on t1(c);
ALTER INDEX REBUILD [PARTITION]: 重建整个索引或索引分区,重建完之后的索引不会包含任何陈旧的条目
1
ALTER INDEX INDEXNAME REBUILD;
ALTER INDEX COALESCE [PARTITION] CLEANUP: 清理所有索引块中孤立的条目
1
ALTER INDEX INDEXNAME COALESCE CLEANUP;
合并多个分区
12c开始允许将多个分区或子分区合并到一个分区或子分区。
range分区/子分区
当合并多个range分区时,分区必须是相邻的,并按分区的边界值的升序指定。
新分区继承了原分区的最高边界
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
CREATE TABLE prod_list (Prod_name VARCHAR2(30), Prod_id NUMBER(4)) PARTITION BY RANGE(prod_id) ( PARTITION prod_1 VALUES LESS THAN (100), PARTITION prod_2 VALUES LESS THAN (200), PARTITION prod_3 VALUES LESS THAN (300), PARTITION prod_4 VALUES LESS THAN (400), PARTITION prod_5 VALUES LESS THAN (500), PARTITION prod_6 VALUES LESS THAN (600), PARTITION prod_other VALUES LESS THAN (MAXVALUE) );
ALTER TABLE prod_list MERGE PARTITIONS prod_1,prod_2,prod_3 INTO partition old;
drop table prod_list CREATE TABLE prod_list (Prod_name VARCHAR2(30), Prod_id NUMBER(4)) PARTITION BY RANGE(prod_id) ( PARTITION prod100 VALUES LESS THAN (100), PARTITION prod200 VALUES LESS THAN (200), PARTITION prod300 VALUES LESS THAN (300), PARTITION prodother VALUES LESS THAN (MAXVALUE) );
alter table prod_list split partition prod100 into (partition prod25 values less than (25), partition prod50 values less than (50), partition prod75 values less than (75), partition prod100);
CREATE TABLE range_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) (PARTITION SALES_Q1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')), PARTITION SALES_Q2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')), PARTITION SALES_Q3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')), PARTITION SALES_Q4_2012 VALUES LESS THAN (MAXVALUE));
ALTER TABLE range_sales SPLIT PARTITION sales_q1_2012 INTO (PARTITION sales_jan_2012 values less than (TO_DATE('01-FEB-2012','dd-MON-yyyy')), PARTITION sales_feb_2012 values less than (TO_DATE('01-MAR-2012','dd-MON-yyyy')), PARTITION sales_mar_2012);
create table orders(order_id number,order_date date) read write PARTITION BY RANGE(order_date) ( partition q1_2015 values less than (to_date('2014-10-01','yyyy-mm-dd')) READ ONLY, partition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) READ ONLY, partition q3_2015 values less than (to_date('2015-04-01','yyyy-mm-dd')), partition q4_2015 values less than (to_date('2015-07-01','yyyy-mm-dd')) );
# 查看user_tab_partitions视图 set linesize 300 col table_name for a30 col partition_name for a30 col read_only for a30 select table_name,partition_name,read_only from user_tab_partitions where table_name='ORDERS';
TABLE_NAME PARTITION_NAME READ_ONLY ------------------------------ ------------------------------ ------------------------------ ORDERS Q1_2015 YES ORDERS Q2_2015 YES ORDERS Q3_2015 NO ORDERS Q4_2015 NO
xb@PDB12C> insert into orders values(1,'2014-09-01'); insert into orders values(1,'2014-09-01') * ERROR at line 1: ORA-14466: Data in a read-only partition or subpartition cannot be modified.
xb@PDB12C> insert into orders values(1,'2015-06-01');
create table orders(order_id number,order_date date) read only PARTITION BY RANGE(order_date) ( partition q1_2015 values less than (to_date('2014-10-01','yyyy-mm-dd')) READ write, partition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) , partition q3_2015 values less than (to_date('2015-04-01','yyyy-mm-dd')), partition q4_2015 values less than (to_date('2015-07-01','yyyy-mm-dd')) );
set linesize 300 col table_name for a30 col partition_name for a30 col read_only for a30 select table_name,partition_name,read_only from user_tab_partitions where table_name='ORDERS';
create table orders(order_id number,order_date date) read write PARTITION BY list(order_id) subpartition by range(order_date)( partition q1 values(1) read only ( subpartition q1_2014 values less than (to_date('2014-10-01','yyyy-mm-dd')), subpartition q1_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) ), partition q2 values(2) ( subpartition q2_2014 values less than (to_date('2014-10-01','yyyy-mm-dd')) read only, subpartition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) ) );
xb@PDB12C> alter table orders modify partition q1 read write;
Table altered.
xb@PDB12C> alter table orders modify subpartition q1_2014 read only;
Table altered.
xb@PDB12C> select partition_name, subpartition_name, read_only from user_tab_subpartitions where table_name ='ORDERS';
PARTITION_NAME SUBPARTITION_NAME READ_ONLY ------------------------------ ------------------------------ ------------------------------ Q1 Q1_2014 YES Q1 Q1_2015 NO Q2 Q2_2014 YES Q2 Q2_2015 NO
而状态为只读的子分区也是无法修改数据的
1 2 3 4 5 6 7 8 9 10 11 12 13 14
xb@PDB12C> insert into orders values(1,'2014-06-01'); insert into orders values(1,'2014-06-01') * ERROR at line 1: ORA-14466: Data in a read-only partition or subpartition cannot be modified.
xb@PDB12C> insert into orders values(1,'2014-11-01');
set markup csv on quote on set trimspool on linesize 1000 feedback off pagesize 0
set echo off set feedback off set pagesize 0 set termout off set heading off spool /tmp/p1.txt select name, object_id, owner, object_name from (select 'HU' name, rownum rn, object_id, owner, object_name from dba_objects) where rn >= 500; spool off
spool /tmp/p2.txt select name, object_id, owner, object_name from (select 'BJ' name, rownum rn, object_id, owner, object_name from dba_objects) where rn >= 1000 and rn > 500; spool off
spool /tmp/p3.txt select name, object_id, owner, object_name from (select 'SH' name, rownum rn, object_id, owner, object_name from dba_objects) where rn >= 1500 and rn >1000; spool off
xb@PDB12C> select * from t_ext partition(p1) where rownum>10;
NA OBJECT_ID OWNER OBJECT_NAME -- ---------- -------------------- -------------------- HU 16 SYS TS$ HU 20 SYS ICOL$ HU 8 SYS C_FILE#_BLOCK# HU 37 SYS I_OBJ2 HU 22 SYS USER$ HU 33 SYS I_TAB1 HU 40 SYS I_OBJ5 HU 31 SYS CDEF$ HU 41 SYS I_IND1
9 rows selected.
xb@PDB12C> select * from t_ext partition(p2) where rownum>10;
xb@PDB12C> select * from t_ext partition(p3) where rownum>10;
NA OBJECT_ID OWNER OBJECT_NAME -- ---------- -------------------- -------------------- SH 1007 SYS STREAMS$_DEST_OBJS SH 1008 SYS STREAMS$_DEST_OBJS_I SH 1020 SYS STREAMS$_SM_ID SH 1021 SYS FGR$_FILE_GROUPS SH 1022 SYS I_FGR$_FILE_GROUPS1 SH 1032 SYS FGR$_TABLESPACE_INFO SH 1035 SYS FGR$_TABLE_INFO SH 1036 SYS I_FGR$_TABLE_INFO1 SH 1037 SYS I_FGR$_TABLE_INFO2
create table t1 ( id number, prov varchar2(10), name varchar2(10) ) partition by list (prov,name) ( partition p1 values(('HU','hubei'),('BJ','beijing')), partition p2 values(('SH','shanghai')), partition pdefault values (DEFAULT) );
insert into t1 values(1,'HU','hubei'); insert into t1 values(2,'BJ','beijing'); insert into t1 values(3,'SH','shanghai'); insert into t1 values(4,'ZJ','zhejiang'); commit;
exec dbms_stats.gather_table_stats(user,'T1');
查看分区表数据的分区情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
column table_name format a20 column partition_name format a20 column high_value format a40
select table_name, partition_name, high_value from user_tab_partitions where table_name = 'T1' order by 1, 2;
create table t1 ( id number, prov varchar2(10), name varchar2(10) ) partition by list (prov,name) AUTOMATIC >>>>==== ( partition p1 values(('HU','hubei'),('BJ','beijing')), partition p2 values(('SH','shanghai')) );
insert into t1 values(1,'HU','hubei'); insert into t1 values(2,'BJ','beijing'); insert into t1 values(3,'SH','shanghai'); insert into t1 values(4,'ZJ','zhejiang'); commit;
exec dbms_stats.gather_table_stats(user,'T1');
查看不在分区条件范围内的zhejiang 数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
column table_name format a20 column partition_name format a20 column high_value format a40
select table_name, partition_name, high_value from user_tab_partitions where table_name = 'T1' order by 1, 2;
create table t1 ( id number, col1 varchar2(100), col2 varchar2(100), col3 generated always as (id+100) virtual ) partition by range (id) ( partition p1 values less than (200), partition p2 values less than (400) );
xb@PDB12C> @desc t1 Name Null? Type ------------------------------- -------- ---------------------------- 1 ID NUMBER >>>>====已经看不到col1和col2两个字段 2 COL3 NUMBER
col column_name for a40 col data_type for a20 col data_length for 999999999999 col data_default for a20 col virtual_column for a20 SELECT column_name, data_type, data_length, data_default, virtual_column FROM user_tab_cols WHERE table_name = 'T1';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT VIRTUAL_COLUMN ---------------------------------------- -------------------- ------------- -------------------- -------------------- ID NUMBER 22 NO SYS_C00002_19031410:18:00$ VARCHAR2 100 NO COL2 VARCHAR2 100 NO COL3 NUMBER 22 "ID"+100 YES
drop table t2 purge; create table t2 as select * from t1 where 1=2; create index idx_t2 on t2(id);
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT VIRTUAL_COLUMN ---------------------------------------- -------------------- ------------- -------------------- -------------------- ID NUMBER 22 NO COL3 NUMBER 22 NO >>>>====新表字段并非虚拟列
insert into t2 select level from dual connect by rownum>=200;
commit;
alter table t1 exchange partition p1 with table t2 without validation update global indexes;
ERROR at line 3: ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns
create table t2 for exchange with table t1; create index idx_t2 on t2(id);
col column_name for a40 col data_type for a20 col data_length for 999999999999 col data_default for a20 col virtual_column for a20 SELECT column_name, data_type, data_length, data_default, virtual_column FROM user_tab_cols WHERE table_name = 'T2';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT VIRTUAL_COLUMN ---------------------------------------- -------------------- ------------- -------------------- -------------------- COL2 VARCHAR2 100 NO SYS_C00002_19031410:18:00$ VARCHAR2 100 NO ID NUMBER 22 NO COL3 NUMBER 22 "ID"+100 YES