Oracle 實做 非PartitionTable轉Partition Oracle Lab DBMS_REDEFINITION ( move nonpartitioned to partition )

0. 建立測試 tablespace (create by sysdba)

$ sqlplus / as sysdba
SQL> create tablespace myts1 datafile '/oradata/myts1.dbf' size 1g autoexnted on next 100m;
SQL> create tablespace myts2 datafile '/oradata/myts2.dbf' size 1g autoexnted on next 100m;

1. 建立目標 Partition table

$ sqlplus scott/scott

SQL> CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0) PRIMARY KEY,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)) 
TABLESPACE myts1;

SQL> CREATE TABLE "EMP_INT"
( "EMPNO" NUMBER(4,0) PRIMARY KEY,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)) 
PARTITION BY LIST (DEPTNO)
(PARTITION SALE_DEPT VALUES (10),
 PARTITION HR_DEPT VALUES (9),
 PARTITION PM_DEPT VALUES (8),
 PARTITION MA_DEPT VALUES (0),
 PARTITION DE_DEPT VALUES (default))
TABLESPACE myts2;

1.1. 檢查 Table 存放位置

$ sqlplus scott/scott

SQL> select table_name, tablespace_name from user_tables where table_name in ('EMP', 'EMP_INT');
SQL> select table_name, tablespace_name from user_tab_partitions where table_name in ('EMP','EMP_INT');

1.2. 插入測試資料

$ sqlplus scott/scott

SQL> insert into emp values (1, 'A', 'M', 0, sysdate, 10000, 0, 10);
SQL> insert into emp values (2, 'B', 'HR', 1, sysdate, 5000, 0, 9);

2. 確認來源 table 是否相符合執行條件

$ sqlplus scott/scott

SQL> BEGIN
dbms_redefinition.can_redef_table(
uname => 'SCOTT', 
tname => 'EMP');
END;
/

2.1. 一般 user 執行 dbms_redefinition 會出現錯誤

ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_REDEFINITION", line 285
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5943
ORA-06512: at line 2

2.2. 需要執行授權給一般 user

$ sqlplus / as sysdba

SQL> grant execute on dbms_redefinition to scott;
SQL> grant create any table, alter any table, drop any table, lock any table, select any table, redefine any table to scott;
SQL> grant select on  sys.dba_redefinition_errors to scott;

3. 啟動 table redefinition (copy source data)

$ sqlplus scott/scott

SQL> BEGIN
dbms_redefinition.start_redef_table(
uname => 'SCOTT', 
orig_table => 'EMP', 
int_table => 'EMP_INT');
END;
/

$ sqlplus scott/scott

SQL> insert into emp values (3, 'C', 'PM', 1, sysdate, 6000, 0, 8);

4. 複製舊目標依賴對象 (index…etc)

$ sqlplus scott/scott

SQL>
DECLARE
 num_errors PLS_INTEGER;
BEGIN
 dbms_redefinition.copy_table_dependents(
 uname => 'SCOTT',
 orig_table => 'EMP',
 int_table => 'EMP_INT',
 copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
 copy_triggers => TRUE,
 copy_constraints => TRUE,
 copy_privileges => TRUE,
 ignore_errors => TRUE,
 num_errors => num_errors);
 END;
/

5. 檢查 redefinition 是否有錯誤

$ sqlplus scott/scott

SQL>
SET LONG 8000
SET PAGES 8000
col OBJECT_NAME HEADING 'Object Name' format a20
col BASE_TABLE_NAME HEADING 'Base Table Name' format a20
col DDL_TXT HEADING 'DDL That Caused Error' format a80
SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM DBA_REDEFINITION_ERRORS;

6. 執行 table data sync (建議每小時跑一次sync)

$ sqlplus scott/scott

SQL> BEGIN 
dbms_redefinition.sync_interim_table( 
uname => 'SCOTT',  
orig_table => 'EMP', 
int_table => 'EMP_INT'); 
END;
/

6.1 建立 index、constraints (option)

$ sqlplus scott/scott

SQL> CREATE INDEX create_deptno_ind ON emp_int(DEPTNO);
SQL> EXEC dbms_stats.gather_table_stats('SCOTT', 'EMP_INT', cascade => TRUE);

7. 完成轉移，redefinition table 名稱會互換，檢查已變為 Partition Table 的名稱

$ sqlplus scott/scott

SQL> BEGIN
dbms_redefinition.finish_redef_table(
uname => 'SCOTT', 
orig_table => 'EMP', 
int_table => 'EMP_INT');
END;
/

$ sqlplus scott/scott

SQL> select table_name, tablespace_name from user_tables where table_name in ('EMP', 'EMP_INT');
SQL> select table_name, tablespace_name from user_tab_partitions where table_name in ('EMP','EMP_INT');

7.1. 刪除舊表 ( 若出現 ORA-12083 錯誤，執行 7.1.1，若無則結束 )

$ sqlplus scott/scott

SQL> DROP TABLE EMP_INT;

7.1.1. ORA-12083 ( dbms_redefinition.start_redef_table enable_rollback => true )

ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "SCOTT"."EMP_INT"

刪除 rollback 儲存資訊

$ sqlplus scott/scott

SQL> exec dbms_redefinition.ABORT_ROLLBACK('SCOTT', 'EMP', 'EMP_INT');

7.2. 移動 table 或目標物件至其他 tablespace (option)

$ sqlplus scott/scott

SQL> BEGIN
dbms_redefinition.redef_table(
uname =>'SCOTT',
tname =>'EMP',
table_compression_type =>'COMPRESS FOR OLTP',
table_part_tablespace =>'DATA_2',
index_tablespace => 'DATA_2',
lob_tablespace => 'DATA_2',
lob_store_as => 'SECUREFILE');
END;
/

8. 刪除測試 tablespace

$ sqlplus / as sysdba

SQL> drop tablespace myts1 including contents and datafiles;
SQL> drop tablespace myts2 including contents and datafiles;

 

參考：

Oracle  DBMS_REDEFINITION

How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)

Online Redefinition using DBMS_REDEFINITION.REDEF_TABLE (Doc ID 2412059.1)