We often need to perform reorganization of the tables. We have to do offline by taking the system down and it consumes a lot of time. we can perform an Online table redefinition using DBMS_REDEFINITION. This feature is available from 9i.
How Oracle internally achieve this Online table redefinition
- To achieve this online reorganization, incrementally maintainable local materialized views are used.
- Snapshot logs need to be defined on the master tables to support incrementally maintainable materialized views.
- These logs keep track of the changes to the master tables and are used by the materialized views during refresh synchronization.
- To keep table indexes and privileges you must use the copy_table_dependents procedure
There are some Restrictions
Tables with the following characteristics cannot be redefined online:
– [9.0.1]Tables with no primary keys
– Tables that have materialized view logs defined on them
– [9i] Tables that are materialized view container tables and AQ tables
– [10g] Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (a subset of rows in the table), vertical subsetting (a subset of columns in the table), and column transformations are not allowed.
– The overflow table of an IOT table
– Tables with fine-grained access control (row-level security)
– Tables with BFILE columns
– Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.
– Tables in the SYS and SYSTEM schema
– Temporary tables
Other restrictions:
– A subset of rows in the table
– Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table.
For example, subqueries are not allowed.
– If new columns are being added with no column mappings, then they must not be declared NOT NULL until the redefinition is complete.
– There cannot be any referential constraints between the table being redefined and the interim table.
– Table redefinition cannot be done NOLOGGING.
– [10g] For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties.
– You cannot convert a nested table to a VARRAY.
Summary of DBMS_REDEFINITION Subprograms:
CAN_REDEF_TABLE Procedure:
This procedure determines if a given table can be reorganized online. This is the first step of the online reorganization process. If the table is not a candidate for online redefinition, an error message is raised.
SYNTAX DBMS_REDEFINITION.can_redef_table ( uname IN VARCHAR2, tname IN VARCHAR2);
START_REDEF_TABLE Procedure:
This procedure initiates the reorganization process. After verifying that the table can be reorganized online, you create an empty interim table (in the same schema as the table to be reorganized) with the desired attributes of the post-reorganization table.
SYNTAX DBMS_REDEFINITION.start_redef_table ( uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2, col_mapping IN VARCHAR2 := NULL); uname The schema name of the tables. orig_table The name of the table to be reorganized. int_table The name of the interim table. col_mapping The mapping information from the columns in the interim table to the columns in the original table. (This is similar to the column list on the SELECT clause of a query.) If NULL, all the columns in the original table are selected and have the same name after reorganization.
FINISH_REDEF_TABLE Procedure:
This procedure completes the reorganization process. Before this step, you can create new indexes, triggers, grants, and constraints on the interim table. The
referential constraints involving the interim table must be disabled. After completing this step, the original table is locked briefly during this procedure.
SYNTAX DBMS_REDEFINITION.finish_redef_table ( uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2); uname The schema name of the tables. orig_table The name of the table to be reorganized. int_table The name of the interim table.
SYNC_INTERIM_TABLE Procedure:
This procedure keeps the interim table synchronized with the original table. This step is useful in minimizing the amount of synchronization needed to be
done by finish_reorg_table before completing the online reorganization. This procedure can be called between long-running operations (such as creating an index)
on the interim table to sync it up with the data in the original table and speed up subsequent operations.
SYNTAX DBMS_REDEFINITION.sync_interim_table ( uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2); Parameter Description uname The schema name of the tables. orig_table The name of the table to be reorganized. int_table The name of the interim table.
COPY_TABLE_DEPENDENTS Procedure
This procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. This procedure does not clone the already registered dependent objects.
This subprogram is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table (which represents the post-redefinition 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);
Parameter definition
uname:Schema name of the tables
orig_table:Name of the table being redefined
int_table:Name of the interim table
copy_indexes: Flag indicating whether to copy the indexes 0 - do not copy any index
dbms_redefinition.cons_orig_params – copy the indexes using the physical parameters of the source indexes
copy_triggers: TRUE = clone triggers, FALSE = do nothing
copy_constraintsP TRUE = clone constraints, FALSE = do nothing. If compatibility setting is 10.2 or higher, then clone CHECK and NOT NULL constraints
copy_privileges:TRUE = clone privileges, FALSE = do nothing
ignore_errors :TRUE = if an error occurs while cloning a particular dependent object, then skip that object and continue cloning other dependent objects. FALSE = that the cloning process should stop upon encountering an error.
num_errors:Number of errors that occurred while cloning dependent objects
copy_statistics:TRUE = copy statistics, FALSE = do nothing
copy_mvlog:TRUE = copy materialized view log, FALSE = do nothing
Let us take an example of Online table redefinition using Dbms_redefination
create table SCOTT.EXAMPLE_TEST (name varchar2(20),
description varchar2(200),
value varchar2(20)) tablespace USERS;
alter table SCOTT.EXAMPLE_TEST add constraint test_pk primary key (name);
create index SCOTT.EXAMPLE_TEST_idx on test (value);
insert into SCOTT.EXAMPLE_TEST values('rocket','rocket works on momentum principle','mv');
insert into SCOTT.EXAMPLE_TEST values('laser','it is a focus beam','laser');
insert into SCOTT.EXAMPLE_TEST values('comet','this is comet','tail');
insert into SCOTT.EXAMPLE_TEST values('galaxy','Milky way is a name of galaxy ','solar');
commit;
Suppose we want to reorganize the table SCOTT.EXAMPLE_TEST from tablespace TOOLS to USERS
Step 01:
Check Whether Table SCOTT.EXAMPLE_TEST can be redefined online.
Set serveroutput on
SQL> EXECUTE DBMS_REDEFINITION.CAN_REDEF_TABLE('scott','EXAMPLE_TEST', dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
Step 02:
Create an interim table SCOTT.EXAMPLE_TEST_1 which holds the same structure as the original table except for constraints, indexes, and triggers but add the new storage attributes
create table SCOTT.EXAMPLE_TEST_1 (name varchar2(20),
description varchar2(200),
value varchar2(20)) tablespace TOOLS;
Later this table can be dropped.
Step 03:
Initiates the redefinition process by calling dbms_redefinition.start_redef_table procedure.
SQL> exec dbms_redefinition.start_redef_table('SCOTT', 'EXAMPLE_TEST', 'EXAMPLE_TEST_1');
PL/SQL procedure successfully completed.
If any error is encountered while running this process then abort the redefinition process first using the below statements:
If we receive issues during this process, then we need to first do the abort option
SQL> exec dbms_redefinition.abort_redef_table('SCOTT', 'EXAMPLE_TEST', 'EXAMPLE_TEST_1');
Resolve the error and restart the redefinition process.
Step 04:
Copies the dependent objects of the original table onto the interim table. The COPY_TABLE_DEPENDENTS Procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. However, this procedure does not clone the already registered dependent objects.
COPY_TABLE_DEPENDENTS Procedure is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table which represents the post-redefinition table.
DECLARE
error_count PLS_INTEGER := 0;
BEGIN
dbms_redefinition.copy_table_dependents('SCOTT', 'EXAMPLE_TEST', 'EXAMPLE_TEST_',1, TRUE, TRUE, TRUE, FALSE,error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
COPY_TABLE_DEPENDENTS procedure to the DBMS_REDEFINITION package. This new procedure optionally copies all indexes, grants, triggers, constraints and privileges from the source table to the interim table.
All referential integrity constraints cloned by the procedure are created in a disabled state, and then automatically enabled after the complete redefinition. The triggers cloned to the interim table are disabled until the redefinition is completed. Once the redefinition is complete, all cloned objects are renamed to the original names used by the objects they were cloned from.
Step 5:
Optionally, synchronize the interim table “EXAMPLE_TEST_1”.
SQL> EXECUTE DBMS_REDEFINITION.SYNC_INTERIM_TABLE('scott', 'EXAMPLE_TEST','EXAMPLE_TEST_1');
Step 06:
Completes the redefinition process by calling FINISH_REDEF_TABLE Procedure.
SQL> exec dbms_redefinition.finish_redef_table('SCOTT', 'EXAMPLE_TEST', 'EXAMPLE_TEST_1');
Step 07:
Check index status by
SQL> select index_name , status from user_indexes where table_name='EXAMPLE_TEST';
Step 08:
Check the constraints
SELECT owner,constraint_name,constraint_type,serach_condition,status FROM dba_constraints WHERE table_name='EXAMPLE_TEST'; Check for the NOT NULL constraints. Any constraint defined at table level on original table is now enforced by system generated constraints.
Step 09:
Drop the interim table EXAMPLE_TEST_1.
SQL> drop table SCOTT.EXAMPLE_TEST_1;
Common Issues with DBMS_redifination
ORA-904 Error During Online Redefinition – DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
Symptoms
ORA-00904: “<column name>”: invalid identifier
This will occur if a table that is being redefined has an index, constraint or trigger on a column that is not included in the redefinition (ie the interim table) and DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS is executed
Cause
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS attempts to create the index on the non-included column
Solution
At present there is no way to force the exclusion of indexes, constraints or triggers ( for copy of dependencies) on columns not included in an interim table
Several solutions can be tried
(a) Set the IGNORE_ERRORS parameter to TRUE before executing the procedure
(b) Do not copy the index, constraint or trigger when doing COPY_TABLE_DEPENDENTS using false for these COPY_TABLE_DEPENDENTS.Once the redefinition is finished, recreate the Oracle indexes that should have been copied
(c) Drop the index, constraint or trigger on the columns that are not included in the interim table before running COPY_TABLE_DEPENDENTS
(d) Manually create the index, constraint or trigger on the interim table then use REGISTER_DEPENDENT_OBJECT to register the mapping of the master to the interim table
Hope you like this post on Online Table redefinition and how we can modify table structure without significantly affecting the availability of the table
Related Articles
Oracle Create table
move the LOB segment to another tablespace
alter table move
create tablespace statement in Oracle
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#ADMIN11667