| 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 |
| cons_constraint |
PLS_INTEGER |
3 |
| cons_index |
PLS_INTEGER |
2 |
| cons_orig_parms |
PLS_INTEGER |
1 |
| cons_trigger |
PLS_INTEGER |
4 |
| cons_use_pk |
PLS_INTEGER |
1 |
| cons_use_rowid |
PLS_INTEGER |
2 |
|
| Dependent Objects |
| CCOL$ |
DBA_TRIGGERS |
ICOL$ |
| CDEF$ |
DBA_USERS |
IND$ |
| COL$ |
DBMS_IJOB |
KU$_DDL |
| CON$ |
DBMS_METADATA |
KU$_DDLS |
| DBA_CONSTRAINTS |
DBMS_SNAPSHOT_LIB |
OBJ$ |
| DBA_CONS_COLUMNS |
DBMS_SNAP_INTERNAL |
PLITBLM |
| DBA_INDEXES |
DBMS_STANDARD |
REDEF_DEP_ERROR$ |
| DBA_IND_COLUMNS |
DBMS_SYS_ERROR |
REDEF_OBJECT$ |
| DBA_LOG_GROUPS |
EMD_LOADER |
SYS |
| DBA_REDEFINITION_OBJECTS |
|
USER$ |
|
| 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 |
| 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); |
| 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 |
| 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'; |
|