Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle online table reorganization

Oracle Database Tips by Donald Burleson

 

The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates.  See here for details on using the dbms_redefinition package for reorganizing Oracle tables online.  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:

-- 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;
 

Online Redefinition

In a very active database environment, it sometimes becomes necessary to modify a hot table to improve the performance of queries and DML statements. To achieve this, Oracle provides a mechanism called online redefinition.

When a table is redefined online, it is accessible to DML during much of the redefinition process. However, the table is locked in the exclusive mode only during a very small period. This window or period is usually independent of the size of the table and the complexity of the redefinition.

The Online Redefinition process was made available in Oracle9i. However, with Oracle Database 10g, this feature becomes much more easy to use and thus useful for redefining online tables. Oracle Database 10g introduces many useful enhancements, thereby increasing support for 24/7 database availability.

New Additions and Changes to dbms_redefinition

In Oracle, there were many restrictions to performing redefinition. It was even necessary to create dependent objects, such as constraints and indexes, manually. Oracle Database 10g introduces many enhancements to facilitate easier administration and to minimize manual tasks. They are discussed next.

Support for additional data types, such as CLOB and BLOB has been introduced. Tables containing character LOBS and binary LOBS can now be redefined online. However, tables with BFILE columns cannot be redefined online.

Tables that are involved in master-master replication can undergo redefinition.  However, there is a restriction that no horizontal or vertical sub-setting, or column transformations, are allowed.

Three new procedures are added to the package dbms_redefinition . They are as follows:

-  copy_table_dependents   - clones the dependent objects of the table being redefined.
-  register_dependent_object - registers a dependent object on the table being redefined and on the interim table
-  unregister_dependent_object  - un-registers a dependent object on the table being redefined and on the interim table.

The following constants are also introduced in specifying the type of the dependent object.

-  cons_constraint   - specifies the dependent object type CONSTRAINT.
-  cons_index  - specifies the dependent object type is of type Index.
-  cons_trigger  - specifies the dependent object type is of TRIGGER
-  copy_orig_params  - specifies that the indexes on the original table be copied using the storage parameters of the original.

A new optional parameter, orderby_cols , has been introduced for the start_redef_table  procedure. You can use this parameter to specify the columns that should be used to order the rows during the initial instantiation of the interim table. In addition, you can also use the optional key word ASCENDING or DESCENDING.

The remaining restrictions are shown below:

-  The table to be redefined cannot be part of a cluster.

-  Tables in the SYS and SYSTEM schemas cannot be online redefined.

-  Temporary tables cannot be redefined.

-  Tables with BFILE columns cannot be online redefined.

- The overflow table of an index-organized table cannot be online redefined.

-  Tables with user-defined types (objects, REFs, collections, typed tables) cannot be redefined online.

-  Table redefinition cannot be done NOLOGGING.

 

Example of online redefinition of a table

Let us look at the total online redefinition process using these newly introduced features and enhancements. We have a table called dly_price with the structure shown below and it has a primary key on ticker and date_px. It also has two additional indexes.

SQL>desc  dly_price
 Name                     Null?    Type
 ---------------------- -------- --------------
 TICKER                          VARCHAR2(4)
 CLOSE_PX                        NUMBER
 OPEN_PX                         NUMBER
 LOW_PX                          NUMBER
 DATE_PX                         DATE

It also has the following indexes.

Table_Name      Index_Name     Pos Column_Name
-------------- --------------  --- --------------
* DLY_PRICE    DLY_PRICE_IND1    1   TICKER
* DLY_PRICE    DLY_PRICE_IND2    1   DATE_PX
* DLY_PRICE    SYS_C003340       1   TICKER
* DLY_PRICE    SYS_C003340       2   DATE_PX

We intend to redefine the table by dropping one column, modifying another column, and adding an additional column.

Step 1.

First, determine the method of redefinition. Here, we will use the primary key to perform the redefinition. When we set the value of dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys. If you set the value to dbms_redefinition.cons_use_rowid, then the redefinition is done using ROWIDs. This procedure will also determine whether the table is redefinable according to the documented limitations, and if not, issue an error message telling why the table is not redefinable.

SQL>exec DBMS_REDEFINITION.CAN_REDEF_TABLE('test1', -'dly_price', dbms_redefinition.cons_use_pk);
 
PL/SQL procedure successfully completed.

 

%   With Oracle database 10g and beyond, it is possible to use pseudo-primary keys, unique keys with NOT NULL constraints in all component columns.

Step 2.

We need to create the interim table with an appropriate definition. This table structure will reflect addition, modification, and deletion of columns.

 

SQL>create table int_dly_price
  2  ( ticker varchar2(6),
  3    close_px number,
  4    low_px number,
  5    high_px number,
  6    date_px date) ;
 
Table created.

As we see in the create table statement, we have added a high_px column and removed the open_px column. Also notice that we have increased ticker column size to 6 from 4.

Step 3.

Now start the online redefinition process by executing the start_redef_table procedure. Supply the parameters, such as table to be redefined, interim table name, the column mapping, and the method of redefinition. Optionally, we can also specify the columns to be used for ordering rows by using the orderby_cols . This is a new feature in Oracle Database 10g.

 

SQL>BEGIN
  2  DBMS_REDEFINITION.START_REDEF_TABLE('test1', 'dly_price','int_dly_price',
  3  'ticker ticker, close_px close_px, low_px low_px, 0 high_px, date_px date_px',
  4  dbms_redefinition.cons_use_pk);
  5  END ;
/
 
PL/SQL procedure successfully completed.

 

As you will notice, high_px is a new column and will have the value of '0'. And open_px does not appear in the column mapping, since it gets removed after redefinition.

Step 4.

In this step, we will use the copy_table_dependents  procedure to automatically copy all dependent objects, such as triggers, indexes, and privileges to the interim table. This procedure is newly introduced in Oracle Database 10g to ease the redefinition process.

 

SQL>Declare
  2  blk_out pls_integer ;
  3  BEGIN
  4  blk_out := 0 ;
  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('test1', 'dly_price','int_dly_price',1,
  6  TRUE, TRUE, TRUE, FALSE, blk_out);
  7  END;
  8  /
 
PL/SQL procedure successfully completed.

Step 5.

Now we finish the process with the finish_redef_table  procedure.

SYS>exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('test1', 'dly_price','int_dly_price') ;
 
PL/SQL procedure successfully completed.

Now, the redefined table shows the structure below:

 

SYS>desc dly_price ;
 Name                     Null?    Type
 ---------------------- -------- ---------------
 TICKER                          VARCHAR2(6)
 CLOSE_PX                        NUMBER
 LOW_PX                          NUMBER
 HIGH_PX                         NUMBER
 DATE_PX                         DATE

Step 6.

Then we drop the interim table after ensuring the desired results are achieved.

You can also query dba_redefinition_errors to determine whether any errors occurred during the cloning of dependent objects with the copy_table_dependents  procedure. This view shows the errors that occurred while cloning dependent objects involved in ongoing redefinitions.


Oracle consulting and training

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.