1,資料庫版本

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

2，連線測試使用者

SQL> conn tbs_11204/system

Connected.

3,建立測試表並插入資料

SQL> create table t_non_partition(card_id int,prov_code int,card_name int);

Table created.

SQL> insert into t_non_partition select level,mod(level,12),level from dual connect by level<=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

4，新增PK主鍵約束

SQL> alter table t_non_partition add constraint pk_non primary key(card_id);

Table altered.

5,建立索引

SQL> create index idx_t_non_partition_code on t_non_partition(prov_code);

Index created.

6,新增表列的註解

SQL> comment on column t_non_partition.card_name is 'card_name comment';

Comment created.

7,以sys使用者驗證是否可以線上重定義為分割槽表

SQL> conn /as sysdba

Connected.

SQL> set serveroutput on

SQL> begin

2 dbms_redefinition.can_redef_table('tbs_11204','t_non_partition');

3 end;

4 /

PL/SQL procedure successfully completed.

8,以測試使用者tbs_11204 建立和和上述表t_non_partition結構相同的臨時表

SQL> create table t_non_partition_temp(card_id int primary key,prov_code int,card_name int)

2 partition by list(prov_code)

3 (partition p1 values(1,2,3,4),

4 partition p2 values(5,6,7,8),

5 partition p3 values(9,10,11,0));

Table created.

9，以sys使用者重新開始線上重定義

SQL> conn /as sysdba

Connected.

SQL> set serveroutput on

SQL> begin

dbms_redefinition.start_redef_table('tbs_11204','t_non_partition','t_non_partition_temp','card_id card_id,prov_code prov_code,card_name card_name',dbms_redefinition.cons_use_pk);

3 end;

4 /

PL/SQL procedure successfully completed.

10,把源表上面的依賴物件即主鍵約束，索引和註解及其它全面同步複製到中間表

官方註解

COPY_TABLE_DEPENDENTS Procedure

This procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. This procedure does not clone the already registered dependent objects.

This subprogram is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table (which represents the post-redefinition table).

Syntax

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
   uname                    IN  VARCHAR2,
   orig_table               IN  VARCHAR2,
   int_table                IN  VARCHAR2,
   copy_indexes             IN  PLS_INTEGER := 1,
   copy_triggers            IN  BOOLEAN     := TRUE,
   copy_constraints         IN  BOOLEAN     := TRUE,
   copy_privileges          IN  BOOLEAN     := TRUE,
   ignore_errors            IN  BOOLEAN     := FALSE,
   num_errors               OUT PLS_INTEGER,
   copy_statistics          IN  BOOLEAN     := FALSE);

Parameters

Table 77-5 COPY_TABLE_DEPENDENTS Procedure Parameters

Parameter	Description
uname	The schema name of the tables.
orig_table	The name of the table being redefined.
int_table	The name of the interim table.
copy_indexes	A flag indicating whether to copy the indexes 0 - do not copy any index dbms_redefinition.cons_orig_params – copy the indexes using the physical parameters of the source indexes
copy_triggers	TRUE = clone triggers, FALSE = do nothing
copy_constraints	TRUE = clone constraints, FALSE = do nothing. If compatibility setting is 10.2 or higher, then clone CHECK and NOT NULL constraints
copy_privileges	TRUE = clone privileges, FALSE = do nothing
ignore_errors	TRUE = if an error occurs while cloning a particular dependent object, then skip that object and continue cloning other dependent objects. FALSE = that the cloning process should stop upon encountering an error.
num_errors	The number of errors that occurred while cloning dependent objects
copy_statistics	TRUE = copy statistics, FALSE = do nothing

Usage Notes

The user must check the column num_errors before proceeding to ensure that no errors occurred during the cloning of the objects.
In case of an error, the user should fix the cause of the error and call the again to clone the dependent object. Alternatively the user can manually clone the dependent object and then register the manually cloned dependent object using the .
All cloned referential constraints involving the interim tables will be created disabled (they will be automatically enabled after the redefinition) and all triggers on interim tables will not fire till the redefinition is completed. After the redefinition is complete, the cloned objects will be renamed to the corresponding pre-redefinition names of the objects (from which they were cloned from).
It is the user's responsibility that the cloned dependent objects are unaffected by the redefinition. All the triggers will be cloned and it is the user's responsibility that the cloned triggers are unaffected by the redefinition.

SQL> declare

2 num_errors pls_integer;

3 begin

4 dbms_redefinition.copy_table_dependents('tbs_11204','t_non_partition','t_non_partition_temp',dbms_redefinition.cons_orig_params,TRUE, TRUE, TRUE, TRUE, num_errors);

5 end;

6 /

PL/SQL procedure successfully completed.

print :num_erros

0

11,檢視線上重定義有無錯誤，這個報錯是因為中間表已存在PK主鍵約束，此錯可以忽略

SQL> set linesize 300

SQL> select object_name,base_table_name,ddl_txt from dba_redefinition_errors;

OBJECT_NAME BASE_TABLE_NAME DDL_TXT

------------------------------ ------------------------------ --------------------------------------------------------------------------------

PK_NON T_NON_PARTITION CREATE UNIQUE INDEX "TBS_11204"."TMP$$_PK_NON0" ON "TBS_11204"."T_NON_PARTITION_

PK_NON T_NON_PARTITION ALTER TABLE "TBS_11204"."T_NON_PARTITION_TEMP" ADD CONSTRAINT "TMP$$_PK_NON0" PR

SQL>

12，可選步，用中間臨時表同步源表

SQL> begin

2 dbms_redefinition.sync_interim_table('tbs_11204','t_non_partition','t_non_partition_temp');

3 end;

4 /

PL/SQL procedure successfully completed.

13,完成線上重定義

SQL> begin

2 dbms_redefinition.finish_redef_table('tbs_11204','t_non_partition','t_non_partition_temp');

3 end;

4 /

PL/SQL procedure successfully completed.

14，確認源表已由非分割槽表變成分割槽表

SQL> select table_name,partition_name from user_tab_partitions where lower(table_name)='t_non_partition';

TABLE_NAME PARTITION_NAME

------------------------------ ------------------------------

T_NON_PARTITION P3

T_NON_PARTITION P1

T_NON_PARTITION P2

15，檢視源表的約束，索引，列註解也也複製過來了

SQL> set linesize 300

SQL> col column_name for a50

SQL> select table_name,index_name,column_name from user_ind_columns where lower(table_name)='t_non_partition';

TABLE_NAME INDEX_NAME COLUMN_NAME

------------------------------ ------------------------------ --------------------------------------------------

T_NON_PARTITION SYS_C004785 CARD_ID

T_NON_PARTITION IDX_T_NON_PARTITION_CODE PROV_CODE

16,可知源表的列註解沒有複製過來

SQL> col comments for a50

SQL> set linesize 300

SQL> select table_name,column_name,comments from user_col_comments where lower(table_name)='t_non_partition';

TABLE_NAME COLUMN_NAME COMMENTS

------------------------------ -------------------------------------------------- --------------------------------------------------

T_NON_PARTITION CARD_ID

T_NON_PARTITION PROV_CODE

T_NON_PARTITION CARD_NAME

17,刪除中間臨時表（可以先不急刪除此表，等所有驗證完成後再刪除此臨時中間表）

SQL> conn tbs_11204/system

Connected.

SQL> drop table t_non_partition_temp;

Table dropped.

oracle 10.2.0.5,如何快速把普通表變更為分割槽表，並且同步複製普通表上面依賴物件呢？

COPY_TABLE_DEPENDENTS Procedure

相關文章