|
The Oracle online table
reorganization package, (dbms_redefinition)
is used to reorganize tables while they are
accepting updates.
-
See here for a full example of
dbms_redefinition
The online reorganization packages
does this by creating a snapshot on the
target table and applying all table changes
after the table has been reorganized with
the "Create table as select" command:
Here is a simple execution of an online
table reorganization:
Here is a simple execution of an online
table reorganization:
-- Check table can be
redefined
EXEC
Dbms_Redefinition.Can_Redef_Table('SCOTT',
'EMPLOYEES');
-- Create new table
with CTAS
CREATE TABLE
scott.employees2
TABLESPACE tools AS
SELECT empno,
first_name, salary as sal
FROM employees WHERE
1=2;
-- Start Redefinition
EXEC
Dbms_Redefinition.Start_Redef_Table(
-
'SCOTT', -
'EMPLOYEES', -
'EMPLOYEES2', -
'EMPNO EMPNO,
FIRST_NAME FIRST_NAME, SALARY*1.10
SAL);
-- Optionally
synchronize new table with interim
data
EXEC
dbms_redefinition.sync_interim_table(
-
'SCOTT', 'EMPLOYEES',
'EMPLOYEES2');
-- Add new keys, FKs
and triggers
ALTER TABLE
employees2 ADD
(CONSTRAINT emp_pk2
PRIMARY KEY (empno)
USING INDEX
TABLESPACE indx);
-- Complete
redefinition
EXEC
Dbms_Redefinition.Finish_Redef_Table(
-
'SCOTT', 'EMPLOYEES',
'EMPLOYEES2');
-- Remove original
table which now has the name of the
new table
DROP TABLE
employees2;
Using Oracle
dbms_redefinition
To solve the problem of doing table
reorgs while the database accepts updates, Oracle9i has
introduced Online Table Redefinitions using
the
DBMS_REDEFINITION package.
The dbms_redefinition package allows you to
copy a table (using CTAS), create a snapshot
on the
table, enqueue changes during the
redefinition, and then re-synchronize the
restructured table
with the changes that have accumulated
during reorganization.
exec
dbms_redefinition.abort_redef_table('PUBS','TITLES','TITLES2');
alter table titles add constraint pk_titles
primary key (title_id);
exec
dbms_redefinition.can_redef_table('PUBS','TITLES');
create table titles2
as
select * from titles;
exec
dbms_redefinition.start_redef_table('PUBS','TITLES','TITLES2','title_id
title_id,title
title,type type,pub_id pub_id,price
price,advance advance,royalty*1.1
royalty,ytd_sales
ytd_sales,notes notes,pubdate pubdate');
exec
dbms_redefinition.sync_interim_table('PUBS','TITLES','TITLES2');
exec
dbms_redefinition.finish_redef_table('PUBS','TITLES','TITLES2');
If your reorganization fails, you must take
special steps to make it re-start. Because
the
redefinition requires creating a snapshot,
you must call
dbms_redefinition.abort_redef_table to
release the snapshot to re-start you
procedure.
The ‘dbms_redefinition.abort_redef_table’
procedure which accepts 3 parameters
(schema, original table name, holding table
name), and which “pops the stack” and allows
you to start over.
|
