首页 > Linux操作系统 > Linux操作系统 > Online Index Rebuild and Table Redefinition

Online Index Rebuild and Table Redefinition

原创 Linux操作系统 作者:yanggq 时间:2019-07-21 16:21:01 0 删除 编辑
From Other Site
A number of new features in Oracle9i have increased database availablity including:

Online Index Rebuilds

Online index rebuilds allow DML operations on the base table during index creation. Oracle9i extends the online index rebuild feature to include Reverse Key, Function Based and Key Compressed indexes. Key compressed indexes on Index Oraganized Tables (IOT) can also be rebuilt online. The syntax for an online rebuild is:
When the ONLINE keyword is used as part of the CREATE or ALTER syntax the current index is left intact while a new copy of the index is built, allowing DML to access the old index. Any alterations to the old index are recorded in a Index Organized Table known as a "journal table". Once the rebuild is complete the alterations from the journal table are merged into the new index. This may take several passes depending on the frequency of alterations to the index. The process will skip any locked rows and commit every 20 rows. Once the merge operation is complete the data dictionary is updated and the old index is dropped. DML access is only blocked during the data dictionary updates, which complete very quickly.

The availability of Index Organized Tables (IOTs) has been improved by:
  • Online creation and rebuild of secondary indexes using:
  • Online COALESCE of IOTs primary key index using:
  • Online update of logical rowids for secondary indexes using:
  • Online move of overflow segments using:

Online Table Redefinition

Prior to Oracle9i table redefinition was only possible using export/import which meant the table was offline during the process, or the move syntax which locked DML during the operation. Neither of these methods is suitable for large OLTP tables as the downtime can be considerable. To solve this problem Oracle9i has introduced Online Table Redefinitions using the DBMS_REDEFINITION package.

The process is simlar to online rebuilds of indexes in that the original table is left online while a new copy of the table is built. DML operations on the original table are stored in an temporary table for interim updates. Once the new table is complete the interim updates are merged into it and the names of the original and the new table are swapped in the data dictionary. This step requires a DML lock but it is only held for a short time. At this point all DML is processed against the new table. The interim updates are automatically discarded, but the original table, with it's new name, has to be discarded manually. An example of the process would be:

-- Check table can be redefined
EXEC Dbms_Redefinition.Can_Redef_Table('SCOTT', 'EMPLOYEES');

-- Create new table
CREATE TABLE scott.employees2
SELECT empno, first_name, salary as sal 
FROM employees WHERE 1=2;

-- Start Redefinition
EXEC Dbms_Redefinition.Start_Redef_Table( - 
  'SCOTT', -

-- Optionally synchronize new table with interim data
-- before index creation
EXEC dbms_redefinition.sync_interim_table( -

-- Add new keys, FKs and triggers
ALTER TABLE employees2 ADD
(CONSTRAINT emp_pk2 PRIMARY KEY (empno) 

-- Complete redefinition
EXEC Dbms_Redefinition.Finish_Redef_Table( -
-- Remove original table which now 
-- has the name of the new table
DROP TABLE employees2;
If the column mappings are ommitted it is assumed that all column names in the new table match those of the old table. Functions can be performed on the data during the redefinition if they are specified in the column mapping. Any indexes, keys and triggers created against the new table must have unique names. All FKs should be created disabled as the redefinition completion will enable them.

The redefinition process can be aborted using:
EXEC Dbms_Redefinition.Abort_Redef_Table('SCOTT', 'EMPLOYEES', 'EMPLOYEES2');
This process allows the following operations to be performed with no impact on DML operations:
  • Converting a non-partitioned table to a partitioned table and vice versa.
  • Switching a heap organized table to an index organized table and vice versa.
  • Dropping non-primary key columns.
  • Adding new columns.
  • Adding or removing parallel support.
  • Modifying storage parameters.
Online table redefinition has a number of restrictions including:
  • There must be enough space to hold two copies of the table.
  • Primary key columns cannot be modified.
  • Tables must have primary keys.
  • Redefinition must be done within the same schema.
  • New columns added cannot be made NOT NULL until after the redefinition operation.
  • Tables cannot contain LONGs, BFILEs or User Defined Types.
  • Clustered tables cannot be redefined.
  • Tables in the SYS or SYSTEM schema cannot be redefined.
  • Tables with materialized view logs or materialized views defined on them cannot be redefined.
  • Horizontal subsetting of data cannot be performed during the redefinition.

Online Analyze Validate

The ANALYZE VALIDATE command is now performed without a DML lock to improve availability. This means the validate structure option can be used with no impact on users.

Quiesce Database

Some operations on database objects will fail if non-DBA queries, transactions or PL/SQL statements reference the object during the operation, such as moving a table to a new schema. In addition certain operations may result in the invalidation of other database objects and code. To prevent users being affected by these operations DBAs often shutdown the database and open it in restricted mode. This has an obvious affect on availability as users are locked out of the system until the restriction is lifted.

In Oracle9i the Quiesce Database mode limits downtime by placing the database in a partially available state while specfic operations are performed using:
All active non-DBA sessions will proceed until they become inactive. Once all non-DBA sessions are inactive the system is placed in Quiesce mode. As soon as the statement is issued, all attempts to activate an inactive non-DBA session are blocked. Once finished the database can be returned to a fully available state using:
The main advantage of this method is that users do not loose their sessions during the process. Also, the shared pool does not have to "warm up" after a shutdown so performance should return to normal instantly. This method is advantageous when performing ALTER TABLE, CREATE OR REPLACE PACKAGE and EXP/IMP operations.

Using database quiesce mode has several restrictions including:
  • Resource Manager must be switched on as this is the process that restricts the non-DBA sessions.
  • Quiesce mode is only available for server editions where Resource Manager is available.
  • When the database is being quiesced, opening a new instance causes a Real Application Clusters environment error.
  • Database recovery returns the system to a normal state if it was in quiesce mode prior to a crash.
  • Backups taken while in quiesce mode are considered as normal online backups.

Dynamic Initialization Parameters

The following parameters can now be altered using the ALTER SYSTEM syntax rather than needing the server to be bounced:

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量