Redefining Tables Online
DBMS_REDEFINITION
Sometime it's necessary in oracle database to modify the logical or
physical structure of a table to improve the poor performance or managed space
issue:
- Improve the performance of queries or DML
- Accommodate application changes
- Manage storage
Oracle Database provides a mechanism to make table structure
modifications without significantly affecting the availability of the table.
The mechanism is called online table redefinition.
Redefining tables online provides a substantial increase in availability
compared to traditional methods of redefining tables.
When a table is redefined online, it is accessible
to both queries and DML during much of the redefinition process. The table is
locked in the exclusive mode only during a very small window that is
independent of the size of the table and complexity of the redefinition, and
that is completely transparent to users.
Online table redefinition requires an amount of
free space that is approximately equivalent to the space used by the table
being redefined.
There are n
number of ways to reorganization of table. When downtime is a challenge to us then
the best option we can use DBMS_REDEFINITION package to execute the task.
Redefine a
table online:
1. Choose the redefinition method: by key or by rowids:
By key Select a primary key or pseudo-primary key to use for the redefinition.
Pseudo-primary keys are unique keys with all component columns having
NOT NULL constraints. For this method, the versions of the
tables before and after redefinition should have the same primary key columns.
This is the preferred and default method of redefinition.
By rowid Use this method if no key is
available. In this method, a hidden column named
M_ROW$$ is added to the post-redefined version of the
table. It is recommended that this column be dropped or marked as unused after
the redefinition is complete. If COMPATIBLE is set to 10.2.0 or higher, the final phase of
redefinition automatically sets this column unused. You can then use the ALTER TABLE ... DROP UNUSED COLUMNS statement to drop it.
2. Verify that the
table can be redefined online by invoking the CAN_REDEF_TABLE procedure. If
the table is not a candidate for online
redefinition, then this procedure raises an error indicating why the table
cannot be redefined online.
3. Create an empty interim table (in the same schema as
the table to be redefined) with all of the desired logical and physical
attributes.
4. It is not necessary to create the interim table with
all the indexes, constraints, grants, and triggers of the table being
redefined. This can be done by COPY_TABLE_DEPENDENTS procedure to automatically.
5. For large table and want to
improve the performance we can set it in parallel :
alter session force parallel dml
parallel degree-of-parallelism;
alter session force parallel query
parallel degree-of-parallelism;
6. FINISH_REDEF_TABLE procedure to complete the
redefinition of the table. During this procedure, the original table is locked
in exclusive mode for a very short time, independent of the amount of data in
the original table. However, FINISH_REDEF_TABLE will wait for all pending DML to commit before
completing the redefinition.
7. If you used rowids for the redefinition and your COMPATIBLE initialization
parameter is set to 10.1.0 or lower, drop or set UNUSED the hidden column M_ROW$$ that
is now in the redefined table.
ALTER TABLE <table_name>
SET UNUSED (M_ROW$$);
If COMPATIBLE is 10.2.0 or higher, this hidden column is automatically set UNUSED when redefinition completes. You can then drop the column with the ALTER TABLE ... DROP UNUSED COLUMNS statement.
Wait for any long-running queries against the interim table to complete and then drop the interim table.
Here is the brief demonstration for redefining table.
[oracle@vm215 ~]$ sqlplus amit/amit
SQL*Plus: Release 11.2.0.3.0
Production on Sat Oct 29 05:44:44 2016
Copyright (c) 1982, 2011,
Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise
Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data
Mining and Real Application Testing options
Creating a demo table name test1 under AMIT schema.
SQL> CREATE TABLE TEST1 ( ID NUMBER(10)
,
ENAME
VARCHAR2(10),
SAL NUMBER(10)
) ;
Inserting bulk rows.
Set the pga_aggregate_target to max value.
SQL> INSERT INTO AMIT.TEST1
SELECT ROWNUM, 'T'|| ROWNUM,DBMS_RANDOM.VALUE(100000, 999999) FROM DUAL CONNECT
BY LEVEL < 1000000;
999999 ROWS CREATED.
SQL> COMMIT ;
COMMIT COMPLETE.
Creating dependent objects related to table
TEST1 rows, so that you can see what happen when we do online redefinition.
View Creation
SQL> CREATE VIEW TEST1_VW AS
SELECT * FROM TEST1 ;
VIEW CREATED.
Sequence Creation
SQL> CREATE SEQUENCE TEST_SEQ ;
SEQUENCE CREATED.
Procedure Creation
CREATE OR REPLACE PROCEDURE PROC1
(P_ID IN NUMBER)
AS
V_ID NUMBER ;
BEGIN
SELECT SAL
INTO V_ID
FROM TEST1
WHERE ID = P_ID;
END;
/
PROCEDURE CREATED.
DML Trigger Creation
SQL> CREATE OR REPLACE TRIGGER
AMIT_TRIG
BEFORE
INSERT OR UPDATE ON TEST1
FOR EACH ROW
DECLARE
X NUMBER;
BEGIN
SELECT COUNT(*) INTO X
FROM TEST1
WHERE ID = :NEW.ID;
IF X > 0 THEN
RAISE_APPLICATION_ERROR(-20501, 'ID'
|| :NEW.ID || ' ALREADY EXISTS');
END IF;
END;
/
TRIGGER CREATED.
Primary Key Creation
SQL> ALTER TABLE TEST1 ADD
CONSTRAINT TEST1_ID_PK PRIMARY KEY (ID) ;
TABLE ALTERED.
Get the status before redefinition
SQL> SQL>
SQL> COLUMN OBJECT_NAME FORMAT
A20
SELECT OBJECT_NAME, OBJECT_TYPE,
STATUS FROM USER_OBJECTS ORDER BY OBJECT_NAME;SQL>
OBJECT_NAME OBJECT_TYPE STATUS
--------------------
------------------- -------
AMIT_TRIG TRIGGER VALID
PROC1 PROCEDURE VALID
TEST1 TABLE VALID
TEST1_ID_PK INDEX VALID
TEST1_VW VIEW VALID
TEST_SEQ SEQUENCE VALID
6 ROWS SELECTED.
Check table can be redefined online using
either rowids or primary key
Using Primary Key
SQL> EXEC
DBMS_REDEFINITION.CAN_REDEF_TABLE
('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL PROCEDURE SUCCESSFULLY
COMPLETED.
Using rowid
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE
('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL PROCEDURE SUCCESSFULLY
COMPLETED.
Create a replica of new interim table without
any dependent objects
SQL> CREATE TABLE TEST1_REORG AS
SELECT * FROM TEST1 WHERE ROWNUM=5 ;
TABLE CREATED.
SQL> SELECT COUNT(*) FROM
TEST1_REORG ;
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM TEST1;
COUNT(*)
----------
999999
Now connect using privilege user to execute the
task
[oracle@vm215 ~]$ sqlplus / as
sysdba
Sql*plus: release 11.2.0.3.0
production on sat oct 29 05:16:48 2016
Copyright (c) 1982, 2011,
oracle. All rights reserved.
Connected to:
Oracle database 11g enterprise
edition release 11.2.0.3.0 - 64bit production
With the partitioning, olap, data
mining and real application testing options
Start redefinition using
Primary Key
SQL> EXEC
DBMS_REDEFINITION.START_REDEF_TABLE('AMIT','TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY
COMPLETED.
Copy dependent objects (it will automatically
copy dependent objects) like mview, primary key, view, sequence, triggers.
Note : IGNORE_ERROR is set to true here to avoid primary key violation (COPY_TABLE_DEPENDENTS)
SQL> DECLARE
N
PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('AMIT',
'TEST1','TEST1_REORG', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE,
TRUE, TRUE, TRUE, N);
END;
/
PL/SQL PROCEDURE SUCCESSFULLY
COMPLETED.
To check for errors in dba_redefinition_errors view
SQL> COL OBJECT_NAME FOR A25
SET LIN200 PAGES 200
COL DDL_TEXT FOR A60
SELECT OBJECT_NAME, BASE_TABLE_NAME,
DDL_TXT
FROM DBA_REDEFINITION_ERRORS;
NO ROWS SELECTED
Validate the row count on both tables and sync
with interim table
SQL> SELECT COUNT(*) FROM
AMIT.TEST1_REORG ;
COUNT(*)
----------
999999
SQL> SELECT COUNT(*) FROM
AMIT.TEST1 ;
COUNT(*)
----------
999999
SQL> EXEC
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('AMIT', 'TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY
COMPLETED.
Finally finish the redefinition
SQL> EXEC
DBMS_REDEFINITION.FINISH_REDEF_TABLE ('AMIT', 'TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY
COMPLETED.
SQL> COLUMN OBJECT_NAME FORMAT
A40
SELECT OBJECT_NAME, OBJECT_TYPE,
STATUS
FROM DBA_OBJECTS
WHERE OWNER='AMIT';
OBJECT_NAME OBJECT_TYPE STATUS
---------------------
------------------- -------
TEST1_VW VIEW INVALID
TEST_SEQ SEQUENCE VALID
PROC1 PROCEDURE VALID
TEST1 TABLE
VALID
TEST1_REORG TABLE VALID
TEST1_ID_PK INDEX VALID
TMP$$_TEST1_ID_PK0 INDEX VALID
TMP$$_AMIT_TRIG0 TRIGGER INVALID
AMIT_TRIG TRIGGER INVALID
9 ROWS SELECTED.
You can see the status of view and trigger is
invalid. Recompile the schema with
complete dependency.
SQL> EXEC
UTL_RECOMP.RECOMP_SERIAL('AMIT') ;
PL/SQL PROCEDURE SUCCESSFULLY
COMPLETED.
SQL> SELECT OBJECT_NAME,
OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='AMIT';
OBJECT_NAME OBJECT_TYPE STATUS
----------------------------------------
------------------- -------
TEST1_VW VIEW VALID
TEST_SEQ SEQUENCE VALID
PROC1
PROCEDURE VALID
TEST1 TABLE VALID
TEST1_REORG TABLE VALID
TEST1_ID_PK INDEX VALID
TMP$$_TEST1_ID_PK0 INDEX VALID
TMP$$_AMIT_TRIG0 TRIGGER VALID
AMIT_TRIG TRIGGER VALID
9 ROWS SELECTED.
At the end drop the interim table.
SQL> DROP TABLE AMIT.TEST1_REORG;
TABLE DROPPED.
Hope this help :)
