在线重定义普通表到分区表的过程
在线重定义表结构的优点:
1.
可以重新组合表结构,压缩表的空间.
2.
在线把表迁移到另外一个表空间中.
3.
把表从普通表到分区表之间互换.
4.
在线删除表的字段.
5.
修改表的参数,如initrans. 此参数在建立表的时候定义.以后不可修改,除非使用
online redefinition 重组表结构.
在线重定义的方式:
1.
使用主键(中间表上面创建,并且可以是没有NULL的唯一约束) 10g之前使用.
2.
使用rowid (10g以上版本,且该模式会创建一个隐式字段M_ROW$$,需要在重定义完成后手工删除该字段.10gR2以上会自动使用set unused)
主要步骤:
1.
使用dbms_redefinition.can_redef_table 检查表是否可以在线重定义.
2.
创建一个类似原来表的空的表结构.也叫中间表.(若重定义的对象是一个非常大的表,可以尝试以下方式,以提高速度.
Alter session force parallel dml parallel ;
Alter session force parallel query parallel ;
3.
使用dbms_redefinition.start_redef_table procedure 定义结构开始,参数如下:
用户名,原表名,中间表名.同步列的名称(可以是添加列或者是删除列)如:
Exec dbms_redefinition.start_redef_table(‘howey’,’int_howey’,’id id,name name ,job job ,deptno+10 deptno ,0 bonus’);
这个过程会执行以下两个步骤 :
1.把原来表中的数据全部插入中间表中,此插入过程并非使用insert命令插入,Oracle使用了类似loader的方式来优化插入速度.
2.创建mlog$_tablename 的快照日志表(临时存放在操作过程中原表中数据改变的log.)和rupd$_tablename (在mv刷新时用到).
4.
在中间表上面创建主键,原来表上面的constraint ,index ,trigger 也要都复制到中间表上面(2种方式:1手工 .2 自动:使用copy_table_dependents过程即可自动拷贝这些结构) 另外,原表的权限也会复制到中间表上面) 主要保证了中间表切换为新表后,可以立即被正常使用.不用其它的准备工作.
附:copy_table_dependents使用如下:
SQL> declare
n number ;
begin
dbms_redefinition.copy_table_dependents('howey','test','int_test',1,true,true,true,true,n,true);
end;
/
PL/SQL procedure successfully completed.
5.
使用 dbms_redefinition.sync_interim_table 刷新日志表中的数据.这一步不是必须的.目的是使中间表和原表中的数据同步.主要防止在实施过程中原表数据更新频繁. 导致后续finish过程切换占用大量时间,从而影响系统正常使用.
6.
使用 dbms_redefinition.finish_redef_table 完成表的最终定义.
该过程会有以下2个步骤:
1, 应用日志表中的日志到中间表中,使中间表中的数据在此刻完全等同于原表数据.
2, 原表和中间表的表名作互换.实际就是修改了涉及这两个表的数据字典,若使用rowid方式,10gR1之前需要删除M_ROW$$字段或者设为unused. 10gR2之后就可以完全oracle自动处理了.
3. 删除mlog$_test和rupd$_test表.
7.
如果重定义失败,使用 dbms_redefinition.abort_redef_table 终止重定义过程.此操作会取消重定义中创建的日志表
使用10g新特性联机重定义表把普通表重定义为分区表,并且重定义后的分区表增加一个字段,以下为详细的执行步骤.
准备工作:
1. 创建一个单独的用户来做以下测试:
create user howey identified by h default tablespace users;
grant connect,resource to howey;
2.
创建一个测试用的普通表
SQL> create table test (a int,b int ,c varchar2(20));
Table created.
在原表中插入1000条数据:
SQL> declare i integer;
2 begin
3 for i in 1..1000 loop
4 insert into test values(i,1000-i,'test table');
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed./
3. 创建主键,并创建测试用的trigger.,赋予该表权限.主要为了观察重定义前后的变化.
SQL> alter table test add constraint pk_a_test primary key (a);
Table altered.
SQL> create trigger tri_test
2 before insert or delete on test for each row
3 begin null;
4 end;
5 /
赋予该表权限.
SQL> grant select on test to system;
Grant succeeded.
SQL> grant insert on test to system;
Grant succeeded.
开始重定义表:
1.
检查该表是否可以重定义(在这里我临时把DBA的权限给了howey,测试完后收回. 或者使用system用户执行dbms包)
SQL> exec dbms_redefinition.can_REDEF_TABLE('howey','test');
PL/SQL procedure successfully completed.
2.
创建重新定义的中间表
创建一个分区表,将会把原来所有的数据在线转移到此表中:
SQL> create table int_test (a int, b int ,c varchar2(20),d int )
2 partition by range (a)
3 (partition p1 values less than (100),
4 partition p2 values less than (500),
5 partition p3 values less than (1200));
Table created.
接下来看一下howey的对象,在以下字典里看到.每个分区也是一个对象:
SQL> select object_name,object_type,object_id,status,data_object_id from user_objects;
OBJECT_NAME OBJECT_TYPE OBJECT_ID STATUS DATA_OBJECT_ID
-------------------- ------------------- ---------- ------- --------------
TEST TABLE 894492 VALID 894492
PK_A_TEST INDEX 894493 VALID 894493
TRI_TEST TRIGGER 894494 VALID
INT_TEST TABLE PARTITION 894499 VALID 894499
INT_TEST TABLE PARTITION 894498 VALID 894498
INT_TEST TABLE PARTITION 894497 VALID 894497
INT_TEST TABLE 894496 VALID
7 rows selected.
3.
执行在线重定义.
SQL> exec dbms_redefinition.start_redef_table('howey','test','int_test','a a,b b,c c,0 d');
PL/SQL procedure successfully completed.
再次查看对象发现为9个.
SQL> select object_name,object_type,object_id,status,data_object_id from user_objects;
OBJECT_NAME OBJECT_TYPE OBJECT_ID STATUS DATA_OBJECT_ID
-------------------- ------------------- ---------- ------- --------------
TEST TABLE 894492 VALID 894492
PK_A_TEST INDEX 894493 VALID 894493
TRI_TEST TRIGGER 894494 VALID
INT_TEST TABLE PARTITION 894499 VALID 894499
INT_TEST TABLE PARTITION 894498 VALID 894498
INT_TEST TABLE PARTITION 894497 VALID 894497
INT_TEST TABLE 894496 VALID
MLOG$_TEST TABLE 894500 VALID 894500
RUPD$_TEST TABLE 894501 VALID
9 rows selected.
多了2个表,一个永久表mlog$_emp ,此表记录test的快照日志.记录finish以前的变化记录.
另外一个是临时表,rupd$_emp 在mv刷新用到.此表在此不做讨论.
检查所有表的数据,以便和之后对照
SQL> select * from test;
A B C
---------- ---------- --------------------
1 999 test table
2 998 test table
3 997 test table
4 996 test table
.
.
.
997 3 test table
998 2 test table
999 1 test table
1000 0 test table
1000 rows selected.
发现中间表int_test会比test表多了一个字段.这就是我们在建立中间表的时候添加进去的.
SQL> select * from int_test where rownum < 10;
A B C D
---------- ---------- -------------------- ----------
1 999 test table 0
2 998 test table 0
.
.
.
998 2 test table 0
999 1 test table 0
1000 0 test table 0
1000 rows selected.
:SQL> select * from mlog$_test;
no rows selected
4.
复制原来表上面的constraint ,index, trigger,
查看test表相关的对象:
SQL> select * from user_tab_privs_made;
GRANTEE TABLE_NAME GRANTO PRIVILEGE GRA HIE
---------- --------------- ------ ---------- --- ---
SYSTEM TEST HOWEY INSERT NO NO
SYSTEM TEST HOWEY SELECT NO NO
SQL> select trigger_name,table_owner ,table_name from user_triggers;
TRIGGER_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ---------------
TRI_TEST HOWEY TEST
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
------------------------------ --------------------------- ------------------------------ ---------------
PK_A_TEST NORMAL HOWEY TEST
执行DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS 把原来表上面的trigger,index,constraint,复制到int_test表上面:
SQL> declare
2 n number;
3 begin
4 dbms_redefinition.COPY_TABLE_DEPENDENTS('howey','test','int_test',1,true,true,true,true,n,true);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from user_tab_privs_made;
GRANTEE TABLE_NAME GRANTO PRIVILEGE GRA HIE
---------- --------------- ------ ---------- --- ---
SYSTEM TEST HOWEY INSERT NO NO
SYSTEM INT_TEST HOWEY INSERT NO NO
SYSTEM INT_TEST HOWEY SELECT NO NO
SYSTEM TEST HOWEY SELECT NO NO
SQL> select trigger_name,table_owner ,table_name from user_triggers;
TRIGGER_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ---------------
TRI_TEST HOWEY TEST
TMP$$_TRI_TEST0 HOWEY INT_TEST
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
------------------------------ --------------------------- ------------------------------ ---------------
TMP$$_PK_A_TEST0 NORMAL HOWEY INT_TEST
PK_A_TEST NORMAL HOWEY TEST
5. 执行dbms_redefinition.sync_interim_table , 刷新日志表中数据,使中间表数据和原来表中数据一致.
在test表中插入一条数据:
SQL> insert into test values ( '1001','3','test data ');
1 row created.
再次查看mlog$_test表中的数据,多了一行数据:
SQL> select * from mlog$_test;
A SNAPTIME$ D O CHANGE_VECTOR$$
---------- --------- - - --------------------------------------------------
1001 01-JAN-00 I N FE
SQL > Select * from int_test; 还是1000行,test表插入一行之后,int_test表并没有发上变化
SQL> exec dbms_redefinition.sync_interim_table('howey','test','int_test');
PL/SQL procedure successfully completed.
同步之后我们在看以下表的数据发生了怎么样的变化:
Test ,int_test , mlog$_test
看到此过程就是把日志里的数据转移到了中间表中, mlog$_test表数据为空.test表和int_test表内的数据完全一致
6.
最后工作.
再添加几个权限给中间表便于finish后的对比.(如果此时把权限赋予int_test表上面,则执行完finish过程后.权限会继承到test分区表上.)
grant update on test to system;
Grant succeeded.
SQL> select * from user_tab_privs_made;
GRANTEE TABLE_NAME GRANTO PRIVILEGE GRA HIE
---------- --------------- ------ ---------- --- ---
SYSTEM TEST HOWEY INSERT NO NO
SYSTEM INT_TEST HOWEY INSERT NO NO
SYSTEM INT_TEST HOWEY SELECT NO NO
SYSTEM TEST HOWEY UPDATE NO NO
SYSTEM TEST HOWEY SELECT NO NO
7.
执行重构完成的过程(此过程的核心就是原表和中间表表名的互换)
SQL> exec dbms_redefinition.finish_redef_table('howey','test','int_test');
PL/SQL procedure successfully completed.
查看权限,index,trigger ,已经全部都转化过来了.
SQL> select * from user_tab_privs_made;
GRANTEE TABLE_NAME GRANTO PRIVILEGE GRA HIE
---------- --------------- ------ ---------- --- ---
SYSTEM INT_TEST HOWEY INSERT NO NO
SYSTEM TEST HOWEY INSERT NO NO
SYSTEM TEST HOWEY SELECT NO NO
SYSTEM INT_TEST HOWEY UPDATE NO NO
SYSTEM INT_TEST HOWEY SELECT NO NO
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
------------------------------ --------------------------- ------------------------------ ---------------
PK_A_TEST NORMAL HOWEY TEST
TMP$$_PK_A_TEST0 NORMAL HOWEY INT_TEST
SQL> select trigger_name,table_owner ,table_name from user_triggers;
TRIGGER_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ---------------
TMP$$_TRI_TEST0 HOWEY INT_TEST
TRI_TEST HOWEY TEST
对象又到刚才的9个了,少了两个rupd$_test和mlog$_test. 已经被删除了.
SQL> select object_name,object_type,object_id,status,data_object_id from user_objects;
OBJECT_NAME OBJECT_TYPE OBJECT_ID STATUS DATA_OBJECT_ID
-------------------- ------------------- ---------- ------- --------------
TMP$$_TRI_TEST0 TRIGGER 894494 INVALID
TRI_TEST TRIGGER 894503 INVALID
TMP$$_PK_A_TEST0 INDEX 894493 VALID 894493
PK_A_TEST INDEX 894502 VALID 894502
INT_TEST TABLE 894492 VALID 894492
TEST TABLE PARTITION 894499 VALID 894499
TEST TABLE PARTITION 894498 VALID 894498
TEST TABLE PARTITION 894497 VALID 894497
TEST TABLE 894496 VALID
从以下命名可以看到 ,转化已经成功完成.
SQL> desc test;
Name Null? Type
------------------------------------------------------------------- -------- ---------------------------------------------
A NUMBER(38)
B NUMBER(38)
C VARCHAR2(20)
D NUMBER(38)
SQL> desc int_test;
Name Null? Type
------------------------------------------------------------------- -------- ---------------------------------------------
A NOT NULL NUMBER(38)
B NUMBER(38)
C VARCHAR2(20)
oracle完成了普通表test到分区表int_test的转化工作.
其实核心就是在数据字典中把两个表名互换了一下,这对系统的影响是相当小的.
8.
结束
删除中间表,index, trigger
Drop table int_test ;
如若重定义失败 .或者想取消重定义过程,运行以下dbms包 ;
Dbms_redefinition.abort_redef_table(‘howey’,’test’,’int_test’) ;
这里就结束了 整个在线重构就完成了 .
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13023909/viewspace-609743/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/13023909/viewspace-609743/
587
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
