[20190503]12C R2 分区交换.txt

[20190503]12C R2 分区交换.txt --//12C之前分区交换,如果表存在UNUSED COLUMN时,如果通过ctas建立的表进行交换会报错. --//12c R2 建立表时指定FOR EXCHANGE WITH TABLE参数可以将隐含列一起建立过来. --//通过例子说明: 1. 环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 --//直接参考链接测试:https://oracle-base.com/articles/12c/create-table-for-exchange-with-table-12cr2 CREATE TABLE t1 (   id            NUMBER,   code          VARCHAR2(10),   description   VARCHAR2(50),   created_date  DATE,   data_1        VARCHAR2(1000),   data_2        VARCHAR2(1000),   CONSTRAINT t1_pk PRIMARY KEY (id) ) PARTITION BY RANGE (created_date) (   PARTITION t1_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')),   PARTITION t1_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')) ); ALTER TABLE t1 SET UNUSED (data_1); ALTER TABLE t1 MODIFY data_2 INVISIBLE; SCOTT@test01p> @ desc t1 Name          Null?    Type ------------- -------- ------------- ID            NOT NULL NUMBER CODE                   VARCHAR2(10) DESCRIPTION            VARCHAR2(50) CREATED_DATE           DATE --//DROP TABLE t1_temp PURGE; SCOTT@test01p> CREATE TABLE t1_temp AS SELECT * FROM t1 WHERE 1=2; Table created. SCOTT@test01p> ALTER TABLE t1_temp ADD CONSTRAINT t1_temp_pk PRIMARY KEY (id); Table altered. INSERT INTO t1_temp VALUES ( 1, 'ONE',   'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY')); INSERT INTO t1_temp VALUES ( 2, 'TWO',   'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY')); INSERT INTO t1_temp VALUES ( 3, 'THREE', 'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY')); INSERT INTO t1_temp VALUES ( 4, 'FOUR',  'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY')); COMMIT; SCOTT@test01p> ALTER TABLE t1 EXCHANGE PARTITION t1_2017 WITH TABLE t1_temp WITHOUT VALIDATION UPDATE GLOBAL INDEXES; ALTER TABLE t1 EXCHANGE PARTITION t1_2017 WITH TABLE t1_temp WITHOUT VALIDATION UPDATE GLOBAL INDEXES * ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION --//主要问题在于2个表结构实际上内部不一致. SCOTT@test01p> SELECT column_id, column_name, hidden_column FROM user_tab_cols WHERE table_name = 'T1' ORDER BY internal_column_id;  COLUMN_ID COLUMN_NAME                    HID ---------- ------------------------------ ---          1 ID                             NO          2 CODE                           NO          3 DESCRIPTION                    NO          4 CREATED_DATE                   NO            SYS_C00005_19050321:47:40$     YES            DATA_2                         YES 6 rows selected. --//实际表T1存在2个隐含列. --//重复: DROP TABLE t1_temp PURGE; CREATE TABLE t1_temp TABLESPACE users FOR EXCHANGE WITH TABLE t1; ALTER TABLE t1_temp ADD CONSTRAINT t1_temp_pk PRIMARY KEY (id); INSERT INTO t1_temp VALUES ( 1, 'ONE',   'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY')); INSERT INTO t1_temp VALUES ( 2, 'TWO',   'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY')); INSERT INTO t1_temp VALUES ( 3, 'THREE', 'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY')); INSERT INTO t1_temp VALUES ( 4, 'FOUR',  'Description for ONE', TO_DATE('02-SEP-2017','DD-MON-YYYY')); COMMIT; SCOTT@test01p> SELECT column_id, column_name, hidden_column FROM user_tab_cols WHERE table_name = 'T1_TEMP' ORDER BY internal_column_id;  COLUMN_ID COLUMN_NAME                    HID ---------- ------------------------------ ---          1 ID                             NO          2 CODE                           NO          3 DESCRIPTION                    NO          4 CREATED_DATE                   NO            SYS_C00005_19050321:47:40$     YES            DATA_2                         YES 6 rows selected. --//你可以发现现在T1_temp表结构与T1一致. SCOTT@test01p> ALTER TABLE t1  EXCHANGE PARTITION t1_2017 WITH TABLE t1_temp  WITHOUT VALIDATION  UPDATE GLOBAL INDEXES; Table altered. --//OK现在成功了. SCOTT@test01p> select count(*) from t1 partition(t1_2017)  ;   COUNT(*) ----------          4 SCOTT@test01p> select count(*) from t1_temp  ;   COUNT(*) ----------          0