有以下几个功能
1.修改表storage参数,
2.移动到其它tablespace,
3.加parallelism 查询,
4.增加删除分区,
5.rebulid table减少碎片
6.HEAP TABLE 转为IOT(索引组织表,表就是index,index就是表)或反向操作,
7.add or drop一个列
主要就是使用下面的包
SQL> desc dbms_redefinition;
PROCEDURE ABORT_REDEF_TABLE~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~强制退出
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE CAN_REDEF_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
TNAME VARCHAR2 IN
OPTIONS_FLAG BINARY_INTEGER IN DEFAULT
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE COPY_TABLE_DEPENDENTS
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
COPY_INDEXES BINARY_INTEGER IN DEFAULT
COPY_TRIGGERS BOOLEAN IN DEFAULT
COPY_CONSTRAINTS BOOLEAN IN DEFAULT
COPY_PRIVILEGES BOOLEAN IN DEFAULT
IGNORE_ERRORS BOOLEAN IN DEFAULT
NUM_ERRORS BINARY_INTEGER OUT
COPY_STATISTICS BOOLEAN IN DEFAULT
PROCEDURE FINISH_REDEF_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE REGISTER_DEPENDENT_OBJECT
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
DEP_TYPE BINARY_INTEGER IN
DEP_OWNER VARCHAR2 IN
DEP_ORIG_NAME VARCHAR2 IN
DEP_INT_NAME VARCHAR2 IN
PROCEDURE START_REDEF_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
COL_MAPPING VARCHAR2 IN DEFAULT
OPTIONS_FLAG BINARY_INTEGER IN DEFAULT
ORDERBY_COLS VARCHAR2 IN DEFAULT
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE SYNC_INTERIM_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE UNREGISTER_DEPENDENT_OBJECT
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
DEP_TYPE BINARY_INTEGER IN
DEP_OWNER VARCHAR2 IN
DEP_ORIG_NAME VARCHAR2 IN
DEP_INT_NAME VARCHAR2 IN
关于重定义有2种方法
1.基于primary key(defalut方法)
2.基于rowid (IOT 不能用),重定义后会有一个隐藏列(M_row$$ ,保存表里面被操作行的ROWID，作用和PK是一样的)
过程是
先使用
1.dbms_redefinition.CAN_REDEF_TABLE 看看能 重定义不,比如少primary key,就报错
2.在一个schema中建立一个中间表(中间表建立为重定义后的结构,分区啊,加列啊之类)
3.dbms_redefinition.START_REDEF_TABLE执行重定义 ,要有原始表名,中间表名,列映射规则,重定义方法
关于映射规则:未提供规则,oracle 认为所有包括在中间表中的列用语表的重定义 若给出映射规则方法,只考虑规则方法中的列, 如果未给重定义方法 default使用primary
4.在中间表上建立index,trigger,constraint,然后授权,重定义完成时 中间表的上index ,trigger,constraint和授权 会替换到重定义表上 ,中间表中disable的约束在重定义表
中为enable
5.如果 dbms_redefinition.START_REDEF_TABLE 到FINISH_REDEF_TABLE期间有大量DML 这样的话可多执行几次SYNC_INTERIM_TABLE ,减少最后这步FINISH_REDEF_TABLE锁定表时
间
6. dbms_redefinition.finish_REDEF_TABLE~完成重定义,过程中原始表会被加上X LOCK 加的时间长短 和表数据量有关系
7.可以重新命名index,constraint ,trigger, 要是定义方法用了rowid会有m_row$$列, 可以alter table XX set unused m_row$$(先在数据字典标记未用) 后 drop unused
columns
完成之后原始表使用中间表的属性 进行重新定义,在中间表建立的index,trigger,constraint 和授权 原始表上的index,trigger,constraint(enable)在中间表drop时候也drop了,
中间表中 constraint disable 到原始表中为enable状态
具体实验
SQL> conn xh/a831115
已连接。
SQL> create table t1 (a int);
表已创建。
SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into t1 values(i);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> alter table t1 add constraint pk_t primary key(a);
表已更改。
SQL>
SQL> create or replace trigger tri_t1
2 before insert on t1 for each row
3 begin
4 null;
5 end;
6 /
触发器已创建
建立中间表
1* create table t2 (a int,b int )partition by range(a)(partition p1 values less t
han (100),partition p2 values less than (200),partition p3 values less than (maxval
ue))
2 ; 小于100的在P1分区，小于200的在P2分区,其它在P3分区,还新加了一列 b
表已创建。
SQL> COL high_value format a30
SQL> select partition_name,high_value from user_tab_partitions where table_name='T2
';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
P1 100
P2 200
P3 MAXVALUE
SQL> alter table t2 add constraint pk_t2 primary key(a);
表已更改。
SQL> execute dbms_redefinition.CAN_REDEF_TABLE('XH','T1',DBMS_REDEFINITION.CONS_USE
_PK);
PL/SQL 过程已成功完成。~~~~~~~~~~~没错误 可以重定义
SQL> execute dbms_redefinition.START_REDEF_TABLE('XH','T1','T2','a a,0 b',DBMS_REDE
FINITION.CONS_USE_PK);
PL/SQL 过程已成功完成。
关于列规则
可以看 这个例子 'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus' empno列名没变 后两个一样, depton+10 deptno 列中数加10(还可以其它运算) 名
字没变,0 BOUNS
新加BOUNS列
SQL> select count(*) from t2;
COUNT(*)
----------
1000
SQL> insert into t1 values(1001);~~~~~~~~~~~~此时还有DML 继续操作原始表
已创建 1 行。
SQL> commit;
提交完成。
SQL> select count(*) from t2;
COUNT(*)
----------
1000
SQL> select count(*) from t1;
COUNT(*)
----------
1001
SQL> exec dbms_redefinition.sync_interim_table('XH','T1','T2');~同步一下下
PL/SQL 过程已成功完成。
SQL> select count(*) from t2;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COUNT(*)
----------
1001
给中间表 加trigger
1 create or replace trigger tri_t2
2 before insert on t1 for each row
3 begin
4 null;
5* end;
SQL> /
触发器已创建
SQL> execute dbms_redefinition.FINISH_REDEF_TABLE('XH','T1','T2');~~~~~~~~~这是会LOCK 表(X)
PL/SQL 过程已成功完成。
SQL> desc t1;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER(38)~~~~~~~~~~~~~~~~~~~~~~~~~转过来了
B NUMBER(38)
SQL> desc t2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER(38)
SQL> select partition_name,high_value from user_tab_partitions where table_name='T2
';
未选定行
SQL> select partition_name,high_value from user_tab_partitions where table_name='T1
';
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
P1 100
P2 200
P3 MAXVALUE
SQL> SELECT COUNT(*) FROM T1;
COUNT(*)
----------
1001
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
1001
如果有CONSTRAINT的话也都转换了中间表DISABLE 到 原始表ENABLE
INDEX,TRIGGER 都会转换过来
参考
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2362
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-614620/ ，如需转载，请注明出处，否则将追究法律责任。
