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.
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);
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);
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);
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);
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
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.
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);
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);
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);
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);
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);
-- 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);
-- 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;
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;
/
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;