| General |
Note: DBMS_REDEFINITION is used to redefine table columns and column
names. Tables that can not be redefined are:
- Tables that have materialized views and materialized view logs defined on them
cannot be redefined online
- Tables that are materialized view container tables and AQ tables cannot be
redefined online.
- The overflow table of an IOT table cannot be redefined online.
|
| Source |
{ORACLE_HOME}/rdbms/admin/dbmshord.sql |
| First Available |
9.0.1 |
Constants |
| Name |
Data Type |
Value |
| REGISTER_DEPENDENT_OBJECTS
Constants |
| cons_constraint |
PLS_INTEGER |
3 |
| cons_index |
PLS_INTEGER |
2 |
| cons_mvlog |
PLS_INTEGER |
10 |
| cons_trigger |
PLS_INTEGER |
4 |
| INDEX COPYING
Constants |
| cons_orig_parms |
PLS_INTEGER |
1 |
| START_REDEF_TABLE
Constants |
| cons_use_pk |
PLS_INTEGER |
1 |
| cons_use_rowid |
PLS_INTEGER |
2 |
|
Dependent Objects |
| CCOL$ |
DBA_TAB_PARTITIONS |
IND$ |
| CDEF$ |
DBA_USERS |
KU$_DDL |
| COL$ |
DBMS_IJOB |
KU$_DDLS |
| CON$ |
DBMS_METADATA |
OBJ$ |
| DBA_CONSTRAINTS |
DBMS_SNAPSHOT_LIB |
PLITBLM |
| DBA_CONS_COLUMNS |
DBMS_SNAP_INTERNAL |
REDEF_DEP_ERROR$ |
| DBA_INDEXES |
DBMS_STATS |
REDEF_OBJECT$ |
| DBA_IND_COLUMNS |
DBMS_SYS_ERROR |
TRIGGER$ |
| DBA_LOG_GROUPS |
EMD_LOADER |
TRIGGERDEP$ |
| DBA_MVIEW_LOGS |
ICOL$ |
USER$ |
| DBA_REDEFINITION_OBJECTS |
|
|
|
Exceptions |
| Error Code |
Reason |
| ORA-01408 |
Such column list already indexed |
| ORA-12087 |
An attempt was made to online redefine a table owned by SYS or SYSTEM |
| ORA-12088 |
An attempt was made to online redefine a table containing a LONG column, an ADT column, or a FILE column. |
| ORA-12089 |
An attempt was made to online redefine a table that does not have a primary key defined on it. |
| ORA-12090 |
An attempt was made to online redefine a table that is either a clustered table, AQ table, temporary table, IOT overflow table or table with FGA/RLS enabled. |
| ORA-12091 |
An attempt was made to online redefine a table that had materialized views defined on it or had a materialized view log defined on it or is a master. |
| ORA-12092 |
An attempt was made to online redefine a table that is either a materialized view or a replicated table. |
| ORA-12093 |
The table is not the interim table of the corresponding table to be online redefined. |
| ORA-12094 |
Error during online redefinition. |
| ORA-32330 |
Invalid operation on online redefinition interim table
"string"."string". |
|
Security Model |
Execute is granted to execute_catalog_role
The following privileges must be granted to the user:
ALTER ANY TABLE
CREATE ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE
The following privileges may be required too:
CREATE ANY INDEX
CREATE ANY TRIGGER |
| |
| ABORT_REDEF_TABLE |
| Cleans Up Errors From The Redefinition
Process |
dbms_redefinition.abort_redef_table(
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
part_name IN VARCHAR2 := NULL); |
| See demo: below |
| |
| CAN_REDEF_TABLE |
| Determines if a given table can be redefined online |
dbms_redefinition.can_redef_table(
uname IN VARCHAR2,
tname IN VARCHAR2,
options_flag IN BINARY_INTEGER := 1,
part_name IN VARCHAR2 := NULL);
-- see constants above |
| See demo: below |
| |
| COPY_TABLE_DEPENDENTS
(copy_mvlog added in 11g) |
Copies the dependant objects
of the original table to the interim table |
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
copy_mvlog IN BOOLEAN := FALSE); |
| See demo: below |
| |
| FINISH_REDEF_TABLE |
| Registers a dependent object (index, trigger or constraint) |
dbms_redefinition.finish_redef_table(
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
part_name IN VARCHAR2 := NULL); |
| See demo: below |
| |
| REGISTER_DEPENDENT_OBJECT |
Completes The Redefinition Process |
dbms_redefinition.register_dependent_object(
uname IN VARCHAR2, -- schema name
orig_table IN VARCHAR2, -- table to redefine
int_table IN VARCHAR2, -- interim table
dep_type IN PLS_INTEGER, -- type of dependent object
dep_owner IN VARCHAR2, -- owner of dependent object
dep_orig_name IN VARCHAR2, -- name of orig dependent object
dep_int_name IN VARCHAR2); -- name of interim dependent obj. |
| See demo: below |
| |
| START_REDEF_TABLE |
| Starts The Redefinition Process |
dbms_redefinition.start_redef_table(
uname IN VARCHAR2, -- schema name
orig_table IN VARCHAR2, -- table to redefine
int_table IN VARCHAR2, -- interim table
col_mapping IN VARCHAR2 := NULL, -- column mapping
options_flag IN BINARY_INTEGER := 1, -- redefinition type
orderby_cols IN VARCHAR2 := NULL, -- col list & ASC/DESC
part_name IN VARCHAR2 := NULL); |
| See demo: below |
| |
| SYNC_INTERIM_TABLE |
| Maintains Synchronization Between The Original And Interim Table |
dbms_redefinition.sync_interim_table(
uname IN VARCHAR2, -- schema name
orig_table IN VARCHAR2, -- original table
int_table IN VARCHAR2, -- interim table
part_name IN VARCHAR2 := NULL); |
| See demo: below |
| |
| UNREGISTER_DEPENDENT_OBJECT |
| Unregisters a dependent
object |
dbms_redefinition.unregister_dependent_object(
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
dep_type IN PLS_INTEGER,
dep_owner IN VARCHAR2,
dep_orig_name IN VARCHAR2,
dep_int_name IN VARCHAR2); |
| exec
dbms_redefinition.unregister_dependent_object('UWCLASS', 'EMP', 'INT_EMP',
dbms_redefinition.cons_trigger, 'UWCLASS', 'bu1_hiredate', 'bu2_hiredate'); |
| |
| DBMS_REDEFINITION Demo
1 |
| System Privileges Required To Run Demo |
GRANT create session TO uwclass;
GRANT create materialized view TO uwclass;
GRANT create table TO uwclass;
GRANT create trigger TO uwclass;
GRANT create view TO uwclass;
GRANT execute ON dbms_redefinition TO uwclass;
GRANT alter any table TO uwclass;
GRANT create any table TO uwclass;
GRANT drop any table TO uwclass;
GRANT lock any table TO uwclass;
GRANT select any table TO uwclass; |
Demo Tables |
CREATE TABLE dept (
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
ALTER TABLE dept
ADD CONSTRAINT pk_dept
PRIMARY KEY (deptno);
CREATE TABLE emp (
empno NUMBER(15),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2));
ALTER TABLE emp
ADD CONSTRAINT pk_emp
PRIMARY KEY (empno);
ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept_no
FOREIGN KEY (deptno)
REFERENCES dept(deptno);
ALTER TABLE emp
ADD CONSTRAINT cc_emp_sal_range
CHECK (deptno BETWEEN 1 AND 100);
CREATE TABLE int_emp (
empno NUMBER(15),
name VARCHAR(100),
salary NUMBER,
hiredate DATE,
deptno NUMBER DEFAULT 10); |
Demo Data |
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO emp VALUES
(7369,'SMITH','CLERK',7902, TO_DATE('17-DEC-04'),800,NULL,20);
INSERT INTO emp VALUES
(7499,'ALLEN','SALES',7698, TO_DATE('20-FEB-05'),1600,300,30);
INSERT INTO emp VALUES
(7521,'WARD','SALES',7698, TO_DATE('22-FEB-03'),1250,500,30);
INSERT INTO emp VALUES
(7566,'JONES','MANAGER',7839, TO_DATE('02-APR-04'),2975,NULL,20);
INSERT INTO emp VALUES
(7654,'MARTIN','SALES',7698, TO_DATE('28-SEP-00'),1250,1400,30);
INSERT INTO emp VALUES
(7698,'BLAKE','MANAGER',7839, TO_DATE('01-MAY-01'),2850,NULL,30);
INSERT INTO emp VALUES
(7782,'CLARK','MANAGER',7839, TO_DATE('09-JUN-02'),2450,NULL,10);
INSERT INTO emp VALUES
(7788,'HESS','ANALYST',7566, TO_DATE('13-JUL-03')-85,3000,NULL,20);
INSERT INTO emp VALUES
(7839,'KING','PRESIDENT',NULL, TO_DATE('17-NOV-99'),5000,0,10);
INSERT INTO emp VALUES
(7844,'TURNER','SALES',7698, TO_DATE('08-SEP-01'),1500,0,30);
INSERT INTO emp VALUES
(7876,'ADAMS','CLERK',7788, TO_DATE('13-JUL-02')-51,1100,NULL,20);
INSERT INTO emp VALUES
(7900,'JAMES','CLERK',7698, TO_DATE('03-DEC-01'),950,NULL,30);
INSERT INTO emp VALUES
(7902,'FORD','ANALYST',7566, TO_DATE('03-DEC-03'),3000,NULL,20);
INSERT INTO emp VALUES
(7934,'MILLER','CLERK',7782, TO_DATE('23-JAN-05'),1300,NULL,10);
COMMIT; |
Demo Trigger |
CREATE OR REPLACE TRIGGER
bu1_hiredate
BEFORE UPDATE OF hiredate ON emp
FOR EACH ROW
BEGIN
:NEW.hiredate := SYSDATE;
END bu1_hiredate;
/
CREATE OR REPLACE TRIGGER bu2_hiredate
BEFORE UPDATE OF hiredate ON int_emp
FOR EACH ROW
BEGIN
:NEW.hiredate := SYSDATE+10;
END bu2_hiredate;
/ |
Redefine Emp |
SELECT COUNT(*)
FROM emp;
SELECT COUNT(*)
FROM int_emp;
SELECT table_name
FROM user_tables;
exec dbms_redefinition.start_redef_table('UWCLASS',
'EMP', 'INT_EMP', 'EMPNO EMPNO, ENAME
NAME, SAL*1.10 SALARY, HIREDATE HIREDATE');
SELECT table_name
FROM user_tables;
SELECT name
FROM user_snapshots;
desc mlog$_emp
desc rupd$_emp
exec dbms_redefinition.abort_redef_table('UWCLASS',
'EMP','INT_EMP');
SELECT table_name
FROM user_tables;
/*
If the table is not a candidate for online redefinition, an error
message is raised
*/
exec dbms_redefinition.can_redef_table('UWCLASS','EMP',
dbms_redefinition.cons_use_pk);
exec dbms_redefinition.start_redef_table('UWCLASS',
'EMP', 'INT_EMP', 'EMPNO EMPNO, ENAME
NAME, SAL*1.10 SALARY, HIREDATE HIREDATE, DEPTNO DEPTNO');
exec dbms_redefinition.register_dependent_object('UWCLASS',
'EMP', 'INT_EMP', dbms_redefinition.cons_trigger,
'UWCLASS', 'bu1_hiredate', 'bu2_hiredate');
-- ALTER TABLE int_emp DISABLE CONSTRAINT fk_emp_dept;
DECLARE
retval NUMBER(5);
BEGIN
dbms_redefinition.copy_table_dependents('UWCLASS', 'EMP', 'INT_EMP', 0,
copy_constraints=>TRUE, num_errors=>retval);
dbms_output.put_line(retval);
END;
/
exec dbms_redefinition.sync_interim_table('UWCLASS', 'EMP', 'INT_EMP');
-- the insert to show how DML issued while
-- redefinition is in progress is handled
INSERT INTO emp VALUES
(9999,'MORGAN','IT',7839,TO_DATE('01-JAN-2005'),2950,NULL,10);
COMMIT;
exec dbms_redefinition.finish_redef_table('UWCLASS', 'EMP', 'INT_EMP');
desc emp
desc int_emp
SELECT COUNT(*) FROM emp;
SELECT COUNT(*) FROM int_emp;
SELECT new.salary, old.sal
FROM emp new, int_emp old
WHERE new.empno = old.empno;
SELECT table_name, trigger_name
FROM user_triggers;
SELECT trigger_body from user_triggers
WHERE table_name = 'EMP'; |
| |
| DBMS_REDEFINITION Demo |
This demo was provide by:
Kevin Kennedy, Oracle DBA
[email protected] |
whenever sqlerror exit 1
BEGIN
dbms_redefinition.can_redef_table('GDS',
'BL_STATUS',
dbms_redefinition.cons_use_pk);
END;
/
whenever sqlerror continue
DROP TABLE gds.bl_status_int PURGE;
whenever sqlerror exit 1
CREATE TABLE gds.bl_status_int (
BL_ID NUMBER(12) NOT NULL,
STAT_CODE VARCHAR2(2 BYTE) NOT NULL,
BL_STAT_SEQ_NUM NUMBER(12) NOT NULL,
UPD_DATE DATE NOT NULL,
LST_UPD_DATE DATE DEFAULT SYSDATE NOT NULL,
VERSION_NO NUMBER(12) NOT NULL)
TABLESPACE data01
PARTITION BY RANGE (BL_ID) (
PARTITION P01 VALUES LESS THAN (1750001) TABLESPACE data01,
PARTITION P10 VALUES LESS THAN (2500001) TABLESPACE data02,
PARTITION P11 VALUES LESS THAN (2750001) TABLESPACE data03,
PARTITION P12 VALUES LESS THAN (3000001) TABLESPACE data04,
PARTITION P15 VALUES LESS THAN (3250001) TABLESPACE data05,
PARTITION P16 VALUES LESS THAN (3500001) TABLESPACE data06,
PARTITION P17 VALUES LESS THAN (3750001) TABLESPACE data07,
PARTITION P18 VALUES LESS THAN (4000001) TABLESPACE data08,
PARTITION P19 VALUES LESS THAN (4250001) TABLESPACE data01,
PARTITION P20 VALUES LESS THAN (4500001) TABLESPACE data02,
PARTITION P21 VALUES LESS THAN (4750001) TABLESPACE data03,
PARTITION P22 VALUES LESS THAN (5000001) TABLESPACE data04,
PARTITION P23 VALUES LESS THAN (5250001) TABLESPACE data05,
PARTITION P24 VALUES LESS THAN (5500001) TABLESPACE data06,
PARTITION P25 VALUES LESS THAN (5750001) TABLESPACE data07,
PARTITION PMAX VALUES LESS THAN (MAXVALUE) TABLESPACE data08);
CREATE UNIQUE INDEX gds.xpkbl_status_int
ON gds.bl_status_int(bl_stat_seq_num)
TABLESPACE data09;
ALTER TABLE gds.bl_status_int
ADD CONSTRAINT xpkbl_status_int
PRIMARY KEY (bl_stat_seq_num);
BEGIN
dbms_redefinition.start_redef_table('GDS',
'BL_STATUS',
'BL_STATUS_INT');
END;
/
CREATE UNIQUE INDEX gds.xak1bl_status_int
ON gds.BL_STATUS_INT(bl_id, stat_code, bl_stat_seq_num)
TABLESPACE data11
LOCAL (
PARTITION P01 TABLESPACE data08,
PARTITION P10 TABLESPACE data07,
PARTITION P11 TABLESPACE data06,
PARTITION P12 TABLESPACE data05,
PARTITION P15 TABLESPACE data04,
PARTITION P16 TABLESPACE data03,
PARTITION P17 TABLESPACE data02,
PARTITION P18 TABLESPACE data01,
PARTITION P19 TABLESPACE data08,
PARTITION P20 TABLESPACE data07,
PARTITION P21 TABLESPACE data06,
PARTITION P22 TABLESPACE data05,
PARTITION P23 TABLESPACE data04,
PARTITION P24 TABLESPACE data03,
PARTITION P25 TABLESPACE data02,
PARTITION PMAX TABLESPACE data01);
CREATE INDEX gds.xak2bl_status_int
ON gds.bl_status_int(lst_upd_date)
TABLESPACE data09;
ALTER TABLE gds.bl_status_int
ADD CONSTRAINT status_blstatus_ri_int
FOREIGN KEY (stat_code)
REFERENCES gds.status (stat_code)
DISABLE;
REM ALTER TABLE gds.bl_status_int
REM MODIFY CONSTRAINT status_blstatus_ri_int
REM DISABLE KEEP INDEX
REM /
GRANT SELECT ON gds.bl_status_int TO statrpt;
GRANT DELETE, INSERT, SELECT, UPDATE ON gds.bl_status_int TO system;
ALTER TABLE gds.bi_print_status
ADD CONSTRAINT bl_status_bl_prnt_stat_ri_int
FOREIGN KEY (bl_stat_seq_num)
REFERENCES gds.bl_status_int (bl_stat_seq_num)
DISABLE;
BEGIN
dbms_redefinition.sync_interim_table('GDS',
'BL_STATUS', 'BL_STATUS_INT');
END;
/
whenever sqlerror exit 1
BEGIN
dbms_redefinition.finish_redef_table('GDS',
'BL_STATUS', 'BL_STATUS_INT');
END;
/
ALTER TRIGGER gds.bl_LIST_CRPRT_CNTRL_PRFL_TRG COMPILE;
ALTER PACKAGE gds.bl_pkg COMPILE BODY;
ALTER PROCEDURE statrpt.get_bl_stat_sp COMPILE;
ALTER PROCEDURE statrpt.get_user_stat_sp COMPILE;
ALTER TABLE gds.bl_print_status
DROP CONSTRAINT bl_status_bl_prnt_stat_ri;
DROP TABLE gds.bl_status_int PURGE;
ALTER TABLE gds.bl_status
RENAME CONSTRAINT xpkbl_status_int TO xpkbl_status;
ALTER TABLE gds.bl_status
RENAME CONSTRAINT status_blstatus_ri_int TO status_blstatus_ri;
ALTER TABLE gds.bl_print_status
RENAME CONSTRAINT bl_status_bl_prnt_stat_ri_int
TO bl_status_bl_prnt_stat_ri;
ALTER INDEX gds.xpkbl_status_int RENAME TO XPKBL_STATUS;
ALTER INDEX gds.xak1bl_status_int RENAME TO XAK1BL_STATUS;
ALTER INDEX gds.xak2bl_status_int RENAME TO XAK2BL_STATUS; |
Kevin's Abort Redef script |
BEGIN
dbms_redefinition.abort_redef_table('GDS',
'BL_STATUS', 'BL_STATUS_INT');
END;
/
ALTER TABLE gds.BL_PRINT_STATUS
DROP CONSTRAINT bl_status_bl_prnt_stat_r1_int;
DROP TABLE gds.bl_status_int; |
|
