oracle在线重定义表

在一个高可用系统中，如果需要改变一个表的定义是一件比较棘手的问题，尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改，但是对于把普通堆表改为分区表，把索引组织表修改为堆表等操作就无法完成了。而且，对于被大量DML语句访问的表，幸运的是，Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的同时允许DML操作。

在线重定义表具有以下功能：

1.修改表的存储参数；

2.可以将表转移到其他表空间；

3.增加并行查询选项；

4.增加或删除分区；

5.重建表以减少碎片；

6.将堆表改为索引组织表或相反的操作；

7.增加或删除一个列。

调用DBMS_REDEFINITION包需要 EXECUTE_CATALOG_ROLE 角色，除此之外，还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。
具体例子如下 ：

实验一：
SQL> conn scott/tiger;
Connected.
SQL> create table daodao_1 as select * from dba_objects;
alter table  daodao_1 add primary key(object_id);
create table daodao_2 as select * from dba_objects where 1=2;
alter table  daodao_2 add primary key(object_id);

select 'daodao_1' table_name, count(*) from daodao_1
union all
select 'daodao_2' table_name,count(*) from daodao_2;


Table created.

exec dbms_redefinition.can_redef_table(user,'DAODAO_1',DBMS_REDEFINITION.CONS_USE_PK);
exec dbms_redefinition.start_redef_table(user, 'DAODAO_1', 'DAODAO_2');  
SQL> select owner,mview_name from user_mviews; 


OWNER                          MVIEW_NAME
------------------------------ ------------------------------
SCOTT                          DAODAO_2


 EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'DAODAO_1', 'DAODAO_2'); 


课题2：  没有主键的处理方式:
使用rowid 的方式:
SQL> create table daodao_1 as select * from dba_objects;
create table daodao_2 as select * from dba_objects where 1=2;
select 'daodao_1' table_name, count(*) from daodao_1
union all
select 'daodao_2' table_name,count(*) from daodao_2;


Table created.


SQL> 
Table created.


SQL>   2    3  
TABLE_NA   COUNT(*)
-------- ----------
daodao_1      50645
daodao_2          0
SQL> exec dbms_redefinition.can_redef_table(user,'DAODAO_1',DBMS_REDEFINITION.CONS_USE_PK);
SQL> exec dbms_redefinition.can_redef_table(user,'DAODAO_1',DBMS_REDEFINITION.CONS_USE_ROWID);
SQL> exec dbms_redefinition.start_redef_table(user, 'DAODAO_1', 'DAODAO_2','',2); 


注：输入参数说明        PROCEDURE start_redef_table
                              (uname        IN VARCHAR2,----用户名
                              orig_table   IN VARCHAR2,----源表名
                              int_table    IN VARCHAR2,----中间表名
                              col_mapping  IN VARCHAR2 := NULL,---源表和中间表列之间的映射，map；
                              options_flag IN BINARY_INTEGER := 1,---重定义方式


select count(1) from DAODAO_2;


select count(1) from DAODAO_2
union all
select count(1) from daobao_1;


insert into daodao_1 select * from dba_objects where rownum =1;


commit


EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'DAODAO_1', 'DAODAO_2'); 

实验3    大数据量的操作: 
create table daodao_1 as select a.object_name,rownum id,5 id1 from Dba_Objects  a;


select count(1) from daodao_1;


begin
   for i in 1..60loop
    insert into daodao_1 select a.object_name,rownum+i*80000,5*i from Dba_Objects a;
      commit;
  end loop;


end;




select count(1) from daodao_1;






alter table DAODAO_1 add constraint pk_id primary key (ID);  会报错不？


如果有重复数据：
执行:
delete from DAODAO_1 a 
where a.rowid != 
(
select max(b.rowid) from DAODAO_1 b 
where a.ID = b.ID 
)


exec dbms_redefinition.start_redef_table(user, 'DAODAO_1', 'DAODAO_2'); 




create table DAODAO_2(
  OBJECT_NAME VARCHAR2(50),
  ID         number,
  ID1        VARCHAR2(20)
);




SELECT * FROM User_Mviews


BEGIN
 dbms_redefinition.start_redef_table(user, 'DAODAO_1', 'DAODAO_2'); 
END;


EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER,'DAODAO_1','DAODAO_2','OBJECT_NAME OBJECT_NAME,ID ID,TO_CHAR(ID1) ID1',DBMS_REDEFINITION.CONS_USE_PK);




SELECT * FROM User_Mviews;




exec dbms_redefinition.sync_interim_table(user, 'DAODAO_1', 'DAODAO_2');


exec dbms_redefinition.finish_redef_table(user, 'DAODAO_1', 'DAODAO_2');

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23474909/viewspace-1373232/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23474909/viewspace-1373232/