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);
Recovery Manager: Release 12.2.0.1.0 - Production on Tue Aug 27 13:18:24 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA12C (DBID=383814323) connected to auxiliary database: ORA12C (not mounted)
开始执行复制脚本
1 2 3 4 5 6 7 8 9 10 11
run { allocate channel prmy1 type disk; allocate channel prmy2 type disk; allocate auxiliary channel aux1 type disk; allocate auxiliary channel aux2 type disk; allocate auxiliary channel aux3 type disk; allocate auxiliary channel aux4 type disk; DUPLICATE TARGET DATABASE TO 'ora12c' FROM ACTIVE DATABASE USING BACKUPSET; }
contents of Memory Script: { sql clone "alter system set control_files = ''/u01/app/oracle/oradata/ORA12C/controlfile/o1_mf_gcm27myg_.ctl'', ''/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/controlfile/o1_mf_gcm27ndj_.ctl'' comment= ''Set by RMAN'' scope=spfile"; sql clone "alter system set db_name = ''ORA12C'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone from service 'ora12c' primary controlfile; alter clone database mount; }
contents of Memory Script: { sql clone 'alter database flashback off'; set newname for clone datafile 1 to new; set newname for clone datafile 3 to new; set newname for clone datafile 4 to new; set newname for clone datafile 5 to new; set newname for clone datafile 6 to new; set newname for clone datafile 7 to new; set newname for clone datafile 8 to new; set newname for clone datafile 9 to new; set newname for clone datafile 10 to new; set newname for clone datafile 11 to new; set newname for clone datafile 12 to new; set newname for clone datafile 134 to new; set newname for clone datafile 135 to new; set newname for clone datafile 136 to new; set newname for clone datafile 137 to new; restore from nonsparse from service 'ora12c' clone database ; sql 'alter system archive log current'; }
SET COLINVISIBLE ON xb@PDB12C> @desc t2 Name Null? Type ------------------------------- -------- ---------------------------- 1 A NUMBER(38) 2 C NOT NULL NUMBER(38) 3 B (INVISIBLE) NUMBER(38)
xb@PDB12C> @desc t2; Name Null? Type ------------------------------- -------- ---------------------------- 1 A NUMBER(38) 2 C NOT NULL NUMBER(38) 3 B NUMBER(38)
SELECT COLUMN_NAME, COLUMN_ID, HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME ='T2';
COLUMN_NAME COLUMN_ID HID -------------------- ---------- --- A 1 NO B 3 NO C 2 NO
可以看到将字段B重新可见后,它的COLUMN_ID发生了改变,所以要注意的是如果这时你再通过INSERT INTO T2插入数据时,第二个值不是插到B,而是C,所以在写sql的时候一定注意规范,将插入的字段名称都写在语句里。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
INSERT INTO t2 VALUES(6, 7, 8);
SELECT * FROM t2;
A C B ---------- ---------- ---------- 1 2 3 5 4 6 7 8
将NOT NULL字段不可见
1 2 3 4 5 6 7 8
ALTER TABLE t2 modify(c invisible);
INSERT INTO t2 VALUES(5, 6); * ERROR at line 1: ORA-01400: cannot insert NULL into ("XB"."T2"."C")
所以即使字段不可见,但是因为有非空约束,所以还是要插入非空值到列
1 2 3 4 5 6
INSERT INTO t2 (a,b,c) VALUES(5, 6, 7);
1 row created.
当非空字段有默认值的时候,那插入就不会有问题了。
不可见列上的约束键
不可见列上也时可以创建主键和外键约束的,与其他字段没什么区别
1 2 3
CREATE TABLE test1(a number,b number invisible PRIMARY KEY);
CREATE TABLE test2(c number, d number invisible REFERENCES test1(b));
Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "from$_subquery$_002"."ID" "ID" FROM (SELECT "T1"."ID" "ID", "T1"."ID" "rowlimit_$_0", ROW_NUMBER() OVER ( ORDER BY "T1"."ID") "rowlimit_$$_rownumber" FROM "XB"."T1" "T1" WHERE 2>CASE WHEN (2>=0) THEN 2 ELSE 0 END +3) "from$_subquery$_002" WHERE "from$_subquery$_002"."rowlimit_$$_rownumber">=CASE WHEN (2>=0) THEN 2 ELSE 0 END +3 AND "from$_subquery$_002"."rowlimit_$$_rownumber">2 ORDER BY "from$_subquery$_002"."rowlimit_$_0"
*** 2019-08-21T13:50:43.663264+08:00 (PDB12C(3)) 1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber">=CASE WHEN (2>=0) THEN 2 ELSE 0 END +3 AND "from$_subquery$_002"."rowlimit_$$_rownumber">2)) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")>=CASE WHEN (2>=0) THEN 2 ELSE 0 END +3)
create table t1 (id number,name varchar2(10)) row archival;
# 开启或关闭归档 alter table t1 no row archival; alter table t1 row archival;
# 查看字段 col column_name for a20 col data_type for a20 SELECT column_id, column_name, data_type, data_length, hidden_column FROM user_tab_cols WHERE table_name = 'T1' ORDER BY column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH HID ---------- -------------------- -------------------- ----------- --- 1 ID NUMBER 22 NO 2 NAME VARCHAR2 10 NO ORA_ARCHIVE_STATE VARCHAR2 4000 YES >>>>====如果开启数据库内归档,则会自动生成这个隐藏字段
# 关闭归档 alter table t1 no row archival; SELECT column_id, column_name, data_type, data_length, hidden_column FROM user_tab_cols WHERE table_name = 'T1' ORDER BY column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH HID ---------- -------------------- -------------------- ----------- --- 1 ID NUMBER 22 NO 2 NAME VARCHAR2 10 NO >>>>==== ORA_ARCHIVE_STATE字段消失
drop table t2 purge; create table t2 as select * from t1;
# 查询目标表,由于目标表没有开启归档,所有不包含隐藏列 xb@PDB12C> select id,name,ora_archive_state from t2; select id,name,ora_archive_state from t2 * ERROR at line 1: ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier
# 而这个序列也是真实存在的,说明是用户自动创建的 xb@PDB12C> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S K -------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - ISEQ$$_90071 1 1.0000E+28 1 N N 20 21 N N N N
drop table t2 purge; create table t2 (id number generated always as identity, name varchar2(10));
xb@PDB12C> insert into t2 values(1,'xb'); insert into t2 values(1,'xb') * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column
xb@PDB12C> insert into t2 values(null,'xb'); insert into t2 values(null,'xb') * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column
insert into t2(name) values('xb'); insert into t2(name) values('xb2'); insert into t2(name) values('xb3'); commit;
xb@PDB12C> select * from t2;
ID NAME ---------- ---------- 1 xb 2 xb2 3 xb3
xb@PDB12C> update t2 set id=4 where name='xb'; update t2 set id=4 where name='xb' * ERROR at line 1: ORA-32796: cannot update a generated always identity column
由于是使用的序列,那么也可以用到一些序列中的特性,可以指定不同的初始值和累加值
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
drop table t3 purge; create table t3 (id number generated always as identity (start with 2 increment by 2), name varchar2(10));
insert into t3(name) values('xb'); insert into t3(name) values('xb2'); insert into t3(name) values('xb3'); commit;
xb@PDB12C> select * from t3;
ID NAME ---------- ---------- 2 xb 4 xb2 6 xb3
通过USER_TAB_IDENTITY_COLS等视图可以查询到一些标识列的信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
set linesize 300 col table_name for a10 col COLUMN_NAME for a10 col IDENTITY_OPTIONS for a70 select * from USER_TAB_IDENTITY_COLS;
TABLE_NAME COLUMN_NAM GENERATION SEQUENCE_NAME IDENTITY_OPTIONS ---------- ---------- ---------- -------------------- ---------------------------------------------------------------------- T3 ID ALWAYS ISEQ$$_90075 START WITH: 2, INCREMENT BY: 2, MAX_VALUE: 999999999999999999999999999 9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N
T1 ID BY DEFAULT ISEQ$$_90079 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 999999999999999999999999999 9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N
T2 ID ALWAYS ISEQ$$_90073 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 999999999999999999999999999 9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N
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;
insert into t1 values(1,'hubei'); insert into t1 values(2,'beijing'); insert into t1 values(3,'shanghai'); commit;
现在由于业务扩展,需要新增其他省份的数据进来,如果在没有DBA介入的情况下,直接新增数据
1 2 3 4 5
xb@PDB12C> insert into t1 values(4,'guangdong'); insert into t1 values(4,'guangdong') * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition