oracle----在线重定义

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博客 ” ，链接：http://blog.itpub.net/26078027/viewspace-1052780/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26078027/viewspace-1052780/