8i | 9i | 10g | 11g | 12c | Misc | PL/SQL | SQL | RAC | Linux

Home » Articles » 9i » Here

High Availability Enhancements In Oracle9i

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.


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

-- Check table can be redefined

-- 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 as follows.


This process allows the following operations to be performed with no impact on DML operations:

Online table redefinition has a number of restrictions including:

Later releases have improved the online table redefinition functionality.

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.


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.


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:

Dynamic Initialization Parameters

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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.