[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

--//直接參考連結測試:

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