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 handledINSERT 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';

Oracle DBMS_REDEFINITION Version 10.2

相關文章