联机重定义（Online Redefinition）浅析

--联机重定义（Online Redefinition）浅析


--用途：
1.Modify the storage parameters of a table or cluster
2.Move a table or cluster to a different tablespace
3.Add, modify, or drop one or more columns in a table or cluster
4.Add or drop partitioning support (non-clustered tables only)
5.Change partition structure
6.Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
7.Change physical properties of a materialized view log or an Oracle Streams Advanced Queuing queue table
8.Add support for parallel queries
9.Re-create a table or cluster to reduce fragmentation
10.Convert a relational table into a table with object columns, or do the reverse.
11.Convert an object table into a relational table or a table with object columns, or do the reverse.
12.Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.


--重定义方法：
1.通过主键或伪主键
 For this method, the versions of the tables before and after redefinition should have the same primary key columns. 


2.通过rowid。
 In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table.
 You can then use the ALTER TABLE ... DROP UNUSED COLUMNS statement to drop it.

 You cannot use this method on index-organized tables.

--实验 
1.准备中间表
CREATE TABLE "SCOTT"."TMP"
 ("EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
 CONSTRAINT "PK_EMP_EMPNO" PRIMARY KEY ("EMPNO"))
partition by range (deptno)
(partition p10 values less than (20) tablespace tbs1,
partition p20 values less than (30) tablespace tbs2,
partition p30 values less than (40) tablespace tbs3
);

select table_name,partition_name,tablespace_name from user_tab_partitions;

2.开始重定义 conn / as sysdba
begin
DBMS_REDEFINITION.START_REDEF_TABLE (
'SCOTT','EMP','TMP',
'EMPNO EMPNO,
ENAME ENAME,
JOB JOB,
MGR MGR,
HIREDATE HIREDATE,
SAL SAL,
COMM COMM,
DEPTNO DEPTNO');
end;
/

3.同步数据
begin
DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('SCOTT','EMP','TMP');
end;
/

4.结束重定义
begin
DBMS_REDEFINITION.FINISH_REDEF_TABLE ('SCOTT','EMP','TMP');
end;
/