具体过程
1、我们创建一个单独的测试用户用来测试整个过程
SQL> create user mytest identified by mytest;
User created
SQL> grant connect,resource to mytest;
Grant sUCceeded
SQL> connect mytest/mytest;
Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0
Connected as mytest
2、首先,我们创建一个原表,假定是我们需要在线重新定义的表,由于工作需要,我们需要把该表(不是分区表)重新定义为一个分区表,而且不能影响应用程序的运行。
SQL> create table test(a int,b int) tablespace users;
Table created
模拟一个原表
declare i integer;
begin
for i in 1..100 loop
insert into test values(i,100-i);
end loop;
commit;
end;
插入100条模拟数据
create or replace trigger tr_test
before insert or update or delete on test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update audit_test set c=c+1;
commit;
end;
在上面创建一个触发器,模拟原表可能会有的触发器,这个触发器的作用就是假如有任何DML操作,将在audit_test中增加1。
Audit_test现在的数据是:
SQL> select c from audit_test;
100
检查触发器是否工作正常
SQL> insert into test values(101,0);
1 row inserted
SQL> commit;
SQL> select c from audit_test;
101
可以看到,触发器工作正常。
3、检查该表是否能重定义
SQL> exec dbms_redefinition.can_redef_table('MYTEST', 'TEST');
begin dbms_redefinition.can_redef_table('MYTEST', 'TEST'); end;
ORA-12089: cannot online redefine table "MYTEST"."TEST" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 247
ORA-06512: at line 1
可以看到,假如重新定义,需要主键,所以我们增加该表的主键
我们定义主键
SQL> alter table test add constraint pk_test_id primary key(a);
Table altered
SQL> exec dbms_redefinition.can_redef_table('MYTEST', 'TEST');
PL/SQL procedure successfully completed
现在发现,我们可以定义该表了