[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