ITPub博客

首页 > 数据库 > Oracle > oracle----在线重定义

oracle----在线重定义

原创 Oracle 作者:hrora 时间:2011-07-20 18:36:27 0 删除 编辑

Privileges Required
====================
Following privileges are needed to run this package:
- Execute privilege to DBMS_REDEFINITION
- Create any table
- Alter any table
- Drop any table
- Lock any table
- Select any table

CREATE TABLE unpar_table
(
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
);


ALTER TABLE unpar_table ADD
(
CONSTRAINT unpar_table_pk PRIMARY KEY (id)
);

CREATE INDEX create_date_ind ON unpar_table(create_date);

EXEC DBMS_STATS.gather_table_stats('huang', 'unpar_table', cascade => TRUE);


CREATE TABLE par_table
(
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
)
PARTITION BY RANGE (create_date)
(
PARTITION unpar_table_2005 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION unpar_table_2006 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
PARTITION unpar_table_2007 VALUES LESS THAN (MAXVALUE)
);

EXEC Dbms_Redefinition.can_redef_table('huang', 'unpar_table');

BEGIN
DBMS_REDEFINITION.start_redef_table
(
uname => 'huang',
orig_table => 'unpar_table',
int_table => 'par_table'
);
END;


BEGIN
dbms_redefinition.sync_interim_table(
uname => 'huang',
orig_table => 'unpar_table',
int_table => 'par_table');
END;


ALTER TABLE par_table ADD
(
CONSTRAINT unpar_table_pk2 PRIMARY KEY (id)
);


BEGIN
dbms_redefinition.finish_redef_table
(
uname => 'huang',
orig_table => 'unpar_table',
int_table => 'par_table');
END;


select table_name,constraint_name,constraint_type,owner from dba_constraints where table_name in
('UNPAR_TABLE','PAR_TABLE');

TABLE_NAME CONSTRAINT_NAME C OWNER
--------------- ------------------------------ - ------------------------------
PAR_TABLE UNPAR_TABLE_PK P HUANG
UNPAR_TABLE UNPAR_TABLE_PK2 P HUANG

select table_name,index_name,index_type,owner from dba_indexes where table_name in
('UNPAR_TABLE','PAR_TABLE');

TABLE_NAME INDEX_NAME INDEX_TYPE OWNER
--------------- -------------------- ---------- ----------
PAR_TABLE UNPAR_TABLE_PK NORMAL HUANG
PAR_TABLE CREATE_DATE_IND NORMAL HUANG
UNPAR_TABLE UNPAR_TABLE_PK2 NORMAL HUANG
UNPAR_TABLE CREATE_DATE_IND2 NORMAL HUANG


alter table huang.par_table rename constraint UNPAR_TABLE_PK to UNPAR_TABLE_PK_BAK;
alter index huang.UNPAR_TABLE_PK rename to UNPAR_TABLE_PK_BAK;
alter index huang.CREATE_DATE_IND rename to CREATE_DATE_IND_BAK;

alter table huang.UNPAR_TABLE rename constraint UNPAR_TABLE_PK2 to UNPAR_TABLE_PK;
alter index huang.UNPAR_TABLE_PK2 rename to UNPAR_TABLE_PK;
alter index huang.CREATE_DATE_IND2 rename to CREATE_DATE_IND;


SQL> select table_name,constraint_name,constraint_type,owner from dba_constraints where table_name in
2 ('UNPAR_TABLE','PAR_TABLE');

TABLE_NAME CONSTRAINT_NAME C OWNER
--------------- ------------------------------ - ----------
UNPAR_TABLE UNPAR_TABLE_PK P HUANG
PAR_TABLE UNPAR_TABLE_PK_BAK P HUANG

SQL> select table_name,index_name,index_type,owner from dba_indexes where table_name in
2 ('UNPAR_TABLE','PAR_TABLE');

TABLE_NAME INDEX_NAME INDEX_TYPE OWNER
--------------- -------------------- ---------- ----------
PAR_TABLE UNPAR_TABLE_PK_BAK NORMAL HUANG
PAR_TABLE CREATE_DATE_IND_BAK NORMAL HUANG
UNPAR_TABLE UNPAR_TABLE_PK NORMAL HUANG
UNPAR_TABLE CREATE_DATE_IND NORMAL HUANG

Refer to :

How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]

DBMS_REDEFINITION ONLINE REORGANIZATION OF TABLES [ID 149564.1]

[@more@]

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

请登录后发表评论 登录
全部评论
  • 博文量
    31
  • 访问量
    66066