oracle 9i提供了dbms_redefinition包来实现数据库的表的在线重定义功能。在实际的应用上,我们可以利用这个包来进行:(1)堆表与分区之间进行转换。(2)重建表以减少HWM。10g能shrink,9i如果用move tablespace and rebuild index在move的时候会锁表,如果想实现在线降低HWM,估计只能用这个了。(3)在线更改表结构,如更改列的前后顺序,将column_a,column_b改成column_b,column_a。
下面就来看看这个包的内容:
---------- --
-- OVERVIEW
--
-- This package provides the API to perform. an online, out-of-place
-- redefinition of a table
- -- =========
- -- CONSTANTS
- -- =========
-- Constants for the options_flag parameter of start_redef_table
cons_use_pk CONSTANT BINARY_INTEGER := 1 ;
cons_use_rowid CONSTANT BINARY_INTEGER := 2 ;
-- NAME: can_redef_table - check if given table can be re-defined
-- INPUTS: uname - table owner name
-- tname - table name
-- options_flag - flag indicating user options to use
PROCEDURE can_redef_table ( uname IN VARCHAR2 ,
tname IN VARCHAR2 ,
options_flag IN BINARY_INTEGER := 1 ) ;
-- NAME: start_redef_table - start the online re-organization
-- INPUTS: uname - schema name
-- orig_table - name of table to be re-organized
-- int_table - name of interim table
-- col_mapping - select list col mapping
-- options_flag - flag indicating user options to use
PROCEDURE start_redef_table ( uname IN VARCHAR2 ,
orig_table IN VARCHAR2 ,
int_table IN VARCHAR2 ,
col_mapping IN VARCHAR2 := NULL ,
options_flag IN BINARY_INTEGER := 1 ) ;
-- NAME: finish_redef_table - complete the online re-organization
-- INPUTS: uname - schema name
-- orig_table - name of table to be re-organized
-- int_table - name of interim table
PROCEDURE finish_redef_table ( uname IN VARCHAR2 ,
orig_table IN VARCHAR2 ,
int_table IN VARCHAR2 ) ;
-- NAME: abort_redef_table - clean up after errors or abort the
-- online re-organization
-- INPUTS: uname - schema name
-- orig_table - name of table to be re-organized
-- int_table - name of interim table
PROCEDURE abort_redef_table ( uname IN VARCHAR2 ,
orig_table IN VARCHAR2 ,
int_table IN VARCHAR2 ) ;
-- NAME: sync_interim_table - synchronize interim table with the original
-- table
-- INPUTS: uname - schema name
-- orig_table - name of table to be re-organized
-- int_table - name of interim table
PROCEDURE sync_interim_table ( uname IN VARCHAR2 ,
orig_table IN VARCHAR2 ,
int_table IN VARCHAR2 ) ;
END ;
需要说明的是,如果采用主键作为在线重定义的关联,options_flag 为1,此为默认值,如果表中没有主键,可以用rowid作为在线重定义的关联,但是options_flag 要为2;col_mapping为需要对应的字段,如需要将原表(orig_table )的id字段和中间临时表(int_table)的col_id 字段对于,则需要col_mapping=>’ID COL_ID’,如果完全对应,col_mapping为null,此为默认值。
下面来看看这个包使用的例子(用PK来同步):
insert into xxx select * from xxx ;
insert into xxx select * from xxx ;
( insert 若干次……)
insert into xxx select * from xxx ;
commit ;
delete from xxx ;
insert into xxx select * from dba_objects ;
insert into xxx select * from xxx ;
insert into xxx select * from xxx ;
( insert 若干次……)
insert into xxx select * from xxx ;
commit ;
update xxx set object_id = rownum ;
alert table xxx add CONSTRAINT P_YY PRIMARY KEY ( OBJECT_ID ) ;
好,我们现在已经构造了一个xxx表,且由于经常insert和delete,这个表的HWM比较高,其中浪费的block已经比较多。
Table analyzed .
SQL > select TABLE_NAME , HWM , AVG_USED_BLOCKS ,
2 GREATEST ( ROUND ( 100 * ( NVL ( HWM - AVG_USED_BLOCKS , 0 ) / GREATEST ( NVL ( HWM , 1 ) , 1 ) ) , 2 ) , 0 ) WASTE_PER , sysdate analyze_dt
3 from
4 ( SELECT A . BLOCKS - B . EMPTY_BLOCKS - 1 HWM , table_name ,
5 DECODE ( ROUND (( B . AVG_ROW_LEN * NUM_ROWS * ( 1 + ( PCT_FREE / 100 ))) / 8192 , 0 ) ,
6 0 , 1 ,
7 ROUND (( B . AVG_ROW_LEN * NUM_ROWS * ( 1 + ( PCT_FREE / 100 ))) / 8192 , 0 )
8 ) + 2 AVG_USED_BLOCKS
9 FROM USER_SEGMENTS A ,
10 USER_TABLES B
11 WHERE SEGMENT_NAME = TABLE_NAME
12 and TABLE_NAME in ( ' XXX ' )
13 and SEGMENT_TYPE = ' TABLE '
14 ) ;
TABLE_NAME HWM AVG_USED_BLOCKS WASTE_PER ANALYZE_D
---------------------------- -- ---------- --------------- ---------- ---------
XXX 30652 437 98.57 26 - OCT - 07
我们看到这个表的HWM有30652个block,而平均使用的block只有437个block,98%是浪费的。
下面进行这个表的在线重定义,首先建立中间临时表YYY:
2 ( " OWNER " VARCHAR2 ( 30 ) ,
3 " OBJECT_NAME " VARCHAR2 ( 128 ) ,
4 " SUBOBJECT_NAME " VARCHAR2 ( 30 ) ,
5 " OBJECT_ID " NUMBER ,
6 " DATA_OBJECT_ID " NUMBER ,
7 " OBJECT_TYPE " VARCHAR2 ( 18 ) ,
8 " CREATED " DATE ,
9 " LAST_DDL_TIME " DATE ,
10 " TIMESTAMP " VARCHAR2 ( 19 ) ,
11 " STATUS " VARCHAR2 ( 7 ) ,
12 " TEMPORARY " VARCHAR2 ( 1 ) ,
13 " GENERATED " VARCHAR2 ( 1 ) ,
14 " SECONDARY " VARCHAR2 ( 1 ) ,
15 CONSTRAINT " P_YY " PRIMARY KEY ( " OBJECT_ID " )
16 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
17 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
18 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT )
19 TABLESPACE " MSP " ENABLE
20 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
21 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
22 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT )
23 TABLESPACE " MSP " ;
Table created .
SQL > exec dbms_redefinition . CAN_REDEF_TABLE ( ' TEST ' , ' XXX ' ) ;
PL / SQL procedure successfully completed .
SQL > exec dbms_redefinition . START_REDEF_TABLE ( ' TEST ' , ' XXX ' , ' YYY ' ) ;
PL / SQL procedure successfully completed .
由于我们采用的是PK做关联,因此不必使用options_flag=>2。
让我们来对xxx表经常在线的操作,如update,insert等等的操作:
33722 rows updated .
SQL > commit ;
Commit complete .
SQL > insert into xxx select * from dba_objects ;
insert into xxx select * from dba_objects
*
ERROR at line 1 :
ORA - 00001 : unique constraint ( TEST . P_XX ) violated
SQL > update xxx set object_id = rownum + 1000000000 ;
33722 rows updated .
SQL > commit ;
Commit complete .
SQL > insert into xxx select * from dba_objects ;
34699 rows created .
SQL > commit ;
Commit complete .
SQL > delete from xxx where rownum < 10000 ;
9999 rows deleted .
SQL > commit ;
Commit complete .
SQL > select count ( * ) from xxx ;
COUNT ( * )
-------- --
58422
SQL > select count ( * ) from yyy ;
COUNT ( * )
-------- --
33722
SQL >
SQL >
在这里,我们看到,当dbms_redefinition.START_REDEF_TABLE开始时候,xxx表和yyy表的记录数是一样的,但是当xxx表进行更新的时,yyy表还保持着在start时候的状态。(我们可以用dbms_redefinition.SYNC_INTERIM_TABLE进行同步,待会会介绍)
我们来看一下此时的xxx表和yyy表的HWM情况:
Table analyzed .
SQL > analyze table yyy compute statistics ;
Table analyzed .
SQL > select TABLE_NAME , HWM , AVG_USED_BLOCKS ,
2 GREATEST ( ROUND ( 100 * ( NVL ( HWM - AVG_USED_BLOCKS , 0 ) / GREATEST ( NVL ( HWM , 1 ) , 1 ) ) , 2 ) , 0 ) WASTE_PER , sysdate analyze_dt
3 from
4 ( SELECT A . BLOCKS - B . EMPTY_BLOCKS - 1 HWM , table_name ,
5 DECODE ( ROUND (( B . AVG_ROW_LEN * NUM_ROWS * ( 1 + ( PCT_FREE / 100 ))) / 8192 , 0 ) ,
6 0 , 1 ,
7 ROUND (( B . AVG_ROW_LEN * NUM_ROWS * ( 1 + ( PCT_FREE / 100 ))) / 8192 , 0 )
8 ) + 2 AVG_USED_BLOCKS
9 FROM USER_SEGMENTS A ,
10 USER_TABLES B
11 WHERE SEGMENT_NAME = TABLE_NAME
12 and TABLE_NAME in ( ' XXX ' )
13 and SEGMENT_TYPE = ' TABLE '
14 ) ;
TABLE_NAME HWM AVG_USED_BLOCKS WASTE_PER ANALYZE_D
---------------------------- -- ---------- --------------- ---------- ---------
XXX 30652 763 97.51 26 - OCT - 07
SQL > select TABLE_NAME , HWM , AVG_USED_BLOCKS ,
2 GREATEST ( ROUND ( 100 * ( NVL ( HWM - AVG_USED_BLOCKS , 0 ) / GREATEST ( NVL ( HWM , 1 ) , 1 ) ) , 2 ) , 0 ) WASTE_PER , sysdate analyze_dt
3 from
4 ( SELECT A . BLOCKS - B . EMPTY_BLOCKS - 1 HWM , table_name ,
5 DECODE ( ROUND (( B . AVG_ROW_LEN * NUM_ROWS * ( 1 + ( PCT_FREE / 100 ))) / 8192 , 0 ) ,
6 0 , 1 ,
7 ROUND (( B . AVG_ROW_LEN * NUM_ROWS * ( 1 + ( PCT_FREE / 100 ))) / 8192 , 0 )
8 ) + 2 AVG_USED_BLOCKS
9 FROM USER_SEGMENTS A ,
10 USER_TABLES B
11 WHERE SEGMENT_NAME = TABLE_NAME
12 and TABLE_NAME in ( ' YYY ' )
13 and SEGMENT_TYPE = ' TABLE '
14 ) ;
TABLE_NAME HWM AVG_USED_BLOCKS WASTE_PER ANALYZE_D
---------------------------- -- ---------- --------------- ---------- ---------
YYY 475 437 8 26 - OCT - 07
我们看到xxx表达还是有98%左右的浪费,而yyy只有8%左右的浪费。
进行一次数据同步(其实是一次物化视图的刷新,后面会介绍):
PL / SQL procedure successfully completed .
SQL > select count ( * ) from xxx ;
COUNT ( * )
-------- --
58422
SQL > select count ( * ) from yyy ;
COUNT ( * )
-------- --
58422
完成同步后,xxx和yyy保持一致,但是由于是在线系统,xxx表仍然会有变化,我们在这边做同步的目的是为了在做finish_redef_table能同步较少的数据。
最后,我们结束同步,其内部操作是是将xxx表和yyy表的名称互换,我们看看结束同步后HWM的变化:
2 GREATEST ( ROUND ( 100 * ( NVL ( HWM - AVG_USED_BLOCKS , 0 ) / GREATEST ( NVL ( HWM , 1 ) , 1 ) ) , 2 ) , 0 ) WASTE_PER , sysdate analyze_dt
3 from
4 ( SELECT A . BLOCKS - B . EMPTY_BLOCKS - 1 HWM , table_name ,
5 DECODE ( ROUND (( B . AVG_ROW_LEN * NUM_ROWS * ( 1 + ( PCT_FREE / 100 ))) / 8192 , 0 ) ,
6 0 , 1 ,
7 ROUND (( B . AVG_ROW_LEN * NUM_ROWS * ( 1 + ( PCT_FREE / 100 ))) / 8192 , 0 )
8 ) + 2 AVG_USED_BLOCKS
9 FROM USER_SEGMENTS A ,
10 USER_TABLES B
11 WHERE SEGMENT_NAME = TABLE_NAME
12 and TABLE_NAME in ( ' XXX ' )
13 and SEGMENT_TYPE = ' TABLE '
14 ) ;
TABLE_NAME HWM AVG_USED_BLOCKS WASTE_PER ANALYZE_D
---------------------------- -- ---------- --------------- ---------- ---------
XXX 825 763 7.52 26 - OCT - 07
SQL > select TABLE_NAME , HWM , AVG_USED_BLOCKS ,
2 GREATEST ( ROUND ( 100 * ( NVL ( HWM - AVG_USED_BLOCKS , 0 ) / GREATEST ( NVL ( HWM , 1 ) , 1 ) ) , 2 ) , 0 ) WASTE_PER , sysdate analyze_dt
3 from
4 ( SELECT A . BLOCKS - B . EMPTY_BLOCKS - 1 HWM , table_name ,
5 DECODE ( ROUND (( B . AVG_ROW_LEN * NUM_ROWS * ( 1 + ( PCT_FREE / 100 ))) / 8192 , 0 ) ,
6 0 , 1 ,
7 ROUND (( B . AVG_ROW_LEN * NUM_ROWS * ( 1 + ( PCT_FREE / 100 ))) / 8192 , 0 )
8 ) + 2 AVG_USED_BLOCKS
9 FROM USER_SEGMENTS A ,
10 USER_TABLES B
11 WHERE SEGMENT_NAME = TABLE_NAME
12 and TABLE_NAME in ( ' YYY ' )
13 and SEGMENT_TYPE = ' TABLE '
14 ) ;
TABLE_NAME HWM AVG_USED_BLOCKS WASTE_PER ANALYZE_D
---------------------------- -- ---------- --------------- ---------- ---------
YYY 30652 763 97.51 26 - OCT - 07
在这里,我们看到xxx表和yyy的HWM的使用率完全倒过来了,也就是说,将xxx表和yyy表的名字进行了互换。(但是表中的原来的PK还跟着原来的表)。
这样,我们就在线的完成了降低HWM。
同样的,我们也可以用rowid来进行表的在线重定义(不适合IOT表),操作步骤在这边就不一步一步解释了,基本就PK的一样,见下面的实验:
表已创建。
已用时间: 00 : 00 : 01.11
test @ ORALOCAL ( 192.168.0.12 ) > desc xxx ;
名称 是否为空? 类型
--------------------------------------------------- -- -------- ------------------------------------
USERNAME NOT NULL VARCHAR2 ( 30 )
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2 ( 30 )
ACCOUNT_STATUS NOT NULL VARCHAR2 ( 32 )
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2 ( 30 )
TEMPORARY_TABLESPACE NOT NULL VARCHAR2 ( 30 )
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2 ( 30 )
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 ( 30 )
EXTERNAL_NAME VARCHAR2 ( 4000 )
test @ ORALOCAL ( 192.168.0.12 ) > create table yyy as select * from xxx where 1 = 2 ;
表已创建。
已用时间: 00 : 00 : 00.48
test @ ORALOCAL ( 192.168.0.12 ) >
test @ ORALOCAL ( 192.168.0.12 ) >
test @ ORALOCAL ( 192.168.0.12 ) >
test @ ORALOCAL ( 192.168.0.12 ) >
test @ ORALOCAL ( 192.168.0.12 ) > create index idx_yyy on yyy ( USER_ID ) ;
索引已创建。
已用时间: 00 : 00 : 00.30
test @ ORALOCAL ( 192.168.0.12 ) > exec dbms_redefinition . CAN_REDEF_TABLE ( ' TEST ' , ' XXX ' , 2 ) ;
PL / SQL 过程已成功完成。
已用时间: 00 : 00 : 00.40
test @ ORALOCAL ( 192.168.0.12 ) >
test @ ORALOCAL ( 192.168.0.12 ) > exec dbms_redefinition . CAN_REDEF_TABLE ( ' TEST ' , ' XXX ' , 2 ) ;
PL / SQL 过程已成功完成。
已用时间: 00 : 00 : 00.00
test @ ORALOCAL ( 192.168.0.12 ) > exec dbms_redefinition . CAN_REDEF_TABLE ( ' TEST ' , ' XXX ' , 2 ) ;
PL / SQL 过程已成功完成。
已用时间: 00 : 00 : 00.32
test @ ORALOCAL ( 192.168.0.12 ) > exec dbms_redefinition . START_REDEF_TABLE ( ' TEST ' , ' XXX ' , ' YYY ' , null , 2 ) ;
PL / SQL 过程已成功完成。
已用时间: 00 : 01 : 20.92
test @ ORALOCAL ( 192.168.0.12 ) >
test @ ORALOCAL ( 192.168.0.12 ) >
test @ ORALOCAL ( 192.168.0.12 ) > exec dbms_redefinition . SYNC_INTERIM_TABLE ( ' TEST ' , ' XXX ' , ' YYY ' ) ;
PL / SQL 过程已成功完成。
已用时间: 00 : 00 : 02.26
test @ ORALOCAL ( 192.168.0.12 ) >
test @ ORALOCAL ( 192.168.0.12 ) >
test @ ORALOCAL ( 192.168.0.12 ) > exec dbms_redefinition . FINISH_REDEF_TABLE ( ' TEST ' , ' XXX ' , ' YYY ' ) ;
PL / SQL 过程已成功完成。
已用时间: 00 : 00 : 25.47
不过需要注意的时候,用rowid在线重定义,重定义之后的表会生成一个隐藏的M_ROW$$列,我们可以unused后,drop它:
名称 是否为空? 类型
--------------------------------------------------- -- -------- ------------------------------------
USERNAME NOT NULL VARCHAR2 ( 30 )
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2 ( 30 )
ACCOUNT_STATUS NOT NULL VARCHAR2 ( 32 )
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2 ( 30 )
TEMPORARY_TABLESPACE NOT NULL VARCHAR2 ( 30 )
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2 ( 30 )
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 ( 30 )
EXTERNAL_NAME VARCHAR2 ( 4000 )
system @ ORALOCAL ( 192.168.0.12 ) > select table_name , COLUMN_NAME , decode ( NULLABLE , ' N ' , ' NOT NULL ' , ' Y ' , '' ) ,
2 DATA_TYPE , HIDDEN_COLUMN from user_tab_cols where table_name = ' XXX ' ;
TABLE_NAME COLUMN_NAME DECODE ( N DATA_TYPE HID
---------------------------- -- ------------------------------ -------- ---------- ---
XXX USERNAME NOT NULL VARCHAR2 NO
XXX USER_ID NOT NULL NUMBER NO
XXX PASSWORD VARCHAR2 NO
XXX ACCOUNT_STATUS NOT NULL VARCHAR2 NO
XXX LOCK_DATE DATE NO
XXX EXPIRY_DATE DATE NO
XXX DEFAULT_TABLESPACE NOT NULL VARCHAR2 NO
XXX TEMPORARY_TABLESPACE NOT NULL VARCHAR2 NO
XXX CREATED NOT NULL DATE NO
XXX PROFILE NOT NULL VARCHAR2 NO
XXX INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 NO
XXX EXTERNAL_NAME VARCHAR2 NO
XXX M_ROW $$ VARCHAR2 YES
已选择 13 行。
system @ ORALOCAL ( 192.168.0.12 ) > alter table xxx set unused ( M_ROW $$ ) ;
表已更改。
已用时间: 00 : 00 : 07.09
system @ ORALOCAL ( 192.168.0.12 ) > alter table xxx drop unused columns ;
表已更改。
已用时间: 00 : 00 : 02.74
system @ ORALOCAL ( 192.168.0.12 ) > select table_name , COLUMN_NAME , decode ( NULLABLE , ' N ' , ' NOT NULL ' , ' Y ' , '' ) ,
2 DATA_TYPE , HIDDEN_COLUMN from user_tab_cols where table_name = ' XXX ' ;
TABLE_NAME COLUMN_NAME DECODE ( N DATA_TYPE HID
---------------------------- -- ------------------------------ -------- ---------- ---
XXX USERNAME NOT NULL VARCHAR2 NO
XXX USER_ID NOT NULL NUMBER NO
XXX PASSWORD VARCHAR2 NO
XXX ACCOUNT_STATUS NOT NULL VARCHAR2 NO
XXX LOCK_DATE DATE NO
XXX EXPIRY_DATE DATE NO
XXX DEFAULT_TABLESPACE NOT NULL VARCHAR2 NO
XXX TEMPORARY_TABLESPACE NOT NULL VARCHAR2 NO
XXX CREATED NOT NULL DATE NO
XXX PROFILE NOT NULL VARCHAR2 NO
XXX INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 NO
XXX EXTERNAL_NAME VARCHAR2 NO
已选择 12 行。
已用时间: 00 : 00 : 01.05
system @ ORALOCAL ( 192.168.0.12 ) >
已用时间: 00 : 00 : 00.55
system @ ORALOCAL ( 192.168.0.12 ) >
如果深入一点,观察一下这个包的操作过程,通过trace这个包的执行过,我们在start的时候,发现有以下的操作:
create table " TEST " . " MLOG$_XXX " ( M_ROW $$ VARCHAR2 ( 255 ) , snaptime $$ date , dmltype $$ varchar2 ( 1 ) , old_new $$ varchar2 ( 1 ) , change_vector $$ raw ( 255 )) pctfree 60 pctused 30 ;
alter table " TEST " . " YYY " add ( m_row $$ varchar2 ( 255 )) ;
create snapshot " TEST " . " YYY " on prebuilt table with reduced precision refresh fast with rowid as select * from " TEST " . " XXX " ;
CREATE UNIQUE INDEX " TEST " . " I_SNAP$_YYY " ON " TEST " . " YYY " ( M_ROW $$ ) ;
我们发现oracle是通过一个prebuilt 物化视图来实现重定义的,因此,使用物化视图的一些限制在这里同样适用。
其他注意点:(1)索引名称改变。(2)如果数据量比较大,需要较多的undo。(3)如果遇到意外,需要abort_redef_table 将物化视图取消掉。
http://www.oracleblog.org/study-note/online-redefine-table/