Oracle DBMS_REDEFINITION
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose 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.
AUTHID CURRENT_USER
Constants
Name Data Type Value
COPY_VPD_OPT Parameter of start_redef_table
cons_vpd_none PLS_INTEGER 1
cons_vpd_auto PLS_INTEGER 2
cons_vpd_manual PLS_INTEGER 4
DML Lock
cons_no_dmllock PLS_INTEGER -1
index Copying Method
cons_orig_params PLS_INTEGER 1
Object types in the register_dependent_object
cons_index PLS_INTEGER 2
cons_constraint PLS_INTEGER 3
cons_trigger PLS_INTEGER 4
cons_mvlog PLS_INTEGER 10
OPTIONS_FLAG parameter of start_redef_table
cons_use_pk PLS_INTEGER 1
cons_use_rowid PLS_INTEGER 2
Rollback Constants
cons_no_rollback PLS_INTEGER 1
cons_online_rollback PLS_INTEGER 2
Dependencies
DBMS_ASSERT DBMS_STANDARD DBMS_SODA_ADMIN
DBMS_DBFS_SFS DBMS_STATS DBMS_SQL
DBMS_ISCHED DBMS_SYSTEM KU$_MULTI_DDL
DBMS_LOB DBMS_SYS_ERROR KU$_MULTI_DDLS
DBMS_LOGREP_UTIL DBMS_SYS_SQL KU$_PARSED_ITEM
DBMS_METADATA DBMS_UTILITY KU$_PARSED_ITEMS
DBMS_REDEFINITION_INTERNAL DUAL KU$_SUBMITRESULT
DBMS_SCHEDULER KU$_DDL KU$_SUBMITRESULTS
DBMS_SNAPSHOT_LIB KU$_DDLS PLITBLM
DBMS_SNAP_INTERNAL KU$_ERRORLINE REDEF_DEP_ERROR$
DBMS_SODA KU$_ERRORLINES REDEF_OBJECT$
DBMS_SODA_ADMIN DBMS_SQL UTL_CALL_STACK
DBMS_SQLDIAG DBMS_SODA  
Documented Yes: Packages and Types Reference
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 redefine a clustered, AQ, temporary,or IOT overflow table or table with FGA/RLS enabled
ORA-12091 An attempt to redefine a table that had materialized views or 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"
First Available 9.0.1
Security Model Owned by SYS with EXECUTE granted to XDB and the EXECUTE_CATALOG_ROLE role
System Privileges The following privileges must be granted to the user:
ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE
CREATE ANY TABLE LOCK ANY TABLE  

The following privileges may be required:
CREATE ANY INDEX CREATE ANY TRIGGER  
Source {ORACLE_HOME}/rdbms/admin/dbmshord.sql
Subprograms
ABORT_REDEF_TABLE FINISH_REDEF_TABLE START_REDEF_TABLE
ABORT_ROLLBACK REDEF_TABLE SYNC_INTERIM_TABLE
ABORT_UPDATE REGISTER_DEPENDENT_OBJECT UNREGISTER_DEPENDENT_OBJECT
CAN_REDEF_TABLE ROLLBACK Demo 1
COPY_TABLE_DEPENDENTS SET_PARAM Demo 2
EXECUTE_UPDATE    
 
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 Demos Below
 
ABORT_ROLLBACK
Aborts the intention to ROLLBACK the changes made during online re-organization dbms_redefinition.abort_rollback(
rollback(uname        IN VARCHAR2,
orig_table            IN VARCHAR2,
int_table             IN VARCHAR2    := NULL,
part_name             IN VARCHAR2    := NULL,
dml_lock_timeout      IN PLS_INTEGER := NULL,
continue_after_errors IN BOOLEAN     := FALSE);
TBD
 
ABORT_UPDATE
Cleans up any residual objects after an online re-organization with an UPDATE statement dbms_redefinition.abort_update(abort_update(update_stmt IN CLOB);
PRAGMA SUPPLEMENTAL_LOG_DATA(abort_update, UNSUPPORTED);
DECLARE
 upStmnt CLOB := 'UPDATE t SET change_date = NULL';
BEGIN
  dbms_redefinition.abort_update(upStmnt);
END;
/
 
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(can_redef_table, NONE);

-- see constants above
See Demos 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,
copy_mvlog       IN  BOOLEAN     := FALSE);
See Demos Below
 
EXECUTE_UPDATE
Executes an UPDATE statement dbms_redefinition.execute_update(update_stmt IN CLOB);
PRAGMA SUPPLEMENTAL_LOG_DATA(execute_update, UNSUPPORTED);
DECLARE
 upStmnt CLOB := 'UPDATE t SET change_date = SYSDATE';
BEGIN
  dbms_redefinition.execute_update(upStmnt);
END;
/
 
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
dml_lock_timeout      IN PLS_INTEGER := NULL,
continue_after_errors IN BOOLEAN     := FALSE,
disable_rollback      IN BOOLEAN     := FALSE);
See Demos Below
 
REDEF_TABLE
Provides a single push-button interface that integrates several redefinition steps dbms_redefinition.redef_table(
uname                      IN VARCHAR2,
tname                      IN VARCHAR2,
table_compression_type     IN VARCHAR2    := NULL,
table_part_tablespace      IN VARCHAR2    := NULL,
index_key_compression_type IN VARCHAR2    := NULL,
index_tablespace           IN VARCHAR2    := NULL,
lob_compression_type       IN VARCHAR2    := NULL,
lob_tablespace             IN VARCHAR2    := NULL,
lob_store_as               IN VARCHAR2    := NULL,
refresh_dep_mviews         IN VARCHAR2    := 'N',
dml_lock_timeout           IN PLS_INTEGER := NULL);
PRAGMA SUPPLEMENTAL_LOG_DATA(redef_table, NONE);
conn sys@pdbdev as sysdba

SELECT tablespace_name, compression, compress_for
FROM dba_all_tables
WHERE table_name = 'SERVERS';

exec dbms_redefinition.redef_table('UWCLASS', 'SERVERS', 'COMPRESS FOR OLTP', 'SYSAUX');

SELECT tablespace_name, compression, compress_for
FROM dba_all_tables
WHERE table_name = 'SERVERS';
-- example published by Oracle Corp. copied here to keep it from disappearing

BEGIN
  dbms_redefinition.redef_table(
    uname => 'PM',
    tname => 'PRINT_ADS',
    table_compression_type => 'ROW STORE COMPRESS ADVANCED',
    table_part_tablespace => 'NEWTBS',
    index_key_compression_type => 'COMPRESS 1',
    index_tablespace => 'NEWIDXTBS',
    lob_compression_type => 'COMPRESS HIGH',
    lob_tablespace => 'NEWLOBTBS',
    lob_store_as => 'SECUREFILE');
END;
/
 
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 Demos Below
 
ROLLBACK
Rolls back changes made during an online re-organization dbms_redefinition.rollback(
uname                 IN VARCHAR2,
orig_table            IN VARCHAR2,
int_table             IN VARCHAR2    := NULL,
part_name             IN VARCHAR2    := NULL,
dml_lock_timeout      IN PLS_INTEGER := NULL,
continue_after_errors IN BOOLEAN     := FALSE);
TBD
 
SET_PARAM
Sets a parameter with a value for use in a redefinition dbms_redefinition.set_param(
REDEFINITION_ID IN VARCHAR2,
PARAM_NAME      IN VARCHAR2,
PARAM_VALUE     IN VARCHAR2);
TBD
 
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,    -- order by col list ASC/DESC
part_name             IN VARCHAR2       := NULL,    -- partition name
copy_vpd_opt          IN BINARY_INTEGER := 1,       -- by default do not copy
continue_after_errors IN BOOLEAN        := FALSE,
copy_vpd_opt          IN BINARY_INTEGER := 1,
refresh_dep_mviews    IN VARCHAR2       := 'N',
enable_rollback       IN BOOLEAN        := FALSE);
See Demos 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,   -- partition name
continue_after_errors IN BOOLEAN  := FALSE);
See Demos 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');
 
Demo 1
System Privileges Required To Run Demo conn sys@pdbdev as sysdba

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;
Create Demo Tables conn uwclass/uwclass@pdbdev

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     VARCHAR2(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 Triggers 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_all_tables;

exec dbms_redefinition.start_redef_table('UWCLASS', 'EMP', 'INT_EMP', 'EMPNO EMPNO, ENAME NAME, SAL*1.10 SALARY, HIREDATE HIREDATE, DEPTNO DEPTNO', orderby_cols=>'EMPNO');

SELECT table_name
FROM user_all_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_all_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', orderby_cols=>'EMPNO');

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';
 
Demo 2
This demo was provide by:
Kevin Kennedy, Oracle DBA
kevin.kennedy@nsriusa.com		 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;

Related Topics
Built-in Functions
Built-in Packages
DBMS_REDEFINITION_INTERNAL
DBMS_SODA_ADMIN
Materialized View
Tables
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx