oracle 10.2.0.5,如何快速把普通表变更为分区表，并且同步复制普通表上面依赖对象呢？...

最新推荐文章于 2024-08-09 11:36:05 发布

clg10051

最新推荐文章于 2024-08-09 11:36:05 发布

阅读量120

点赞数

文章标签：数据库

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 COPY_TABLE_DEPENDENTS Procedure 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 REGISTER_DEPENDENT_OBJECT Procedure.
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

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.

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

转载于:http://blog.itpub.net/9240380/viewspace-1244662/

clg10051

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 10.2.0.5,如何快速把普通表变更为分区表，并且同步复制普通表上面依赖对象呢？...

1,数据库版本 SQL> select * from v$version; BANNER ------------------------------------------...
复制链接

扫一扫