Which type of oracle indexes can be rebuilt online?

My colleague was trying rebuild index and he added script assuming that all indexes can be rebuilt online. But ended up with errors as his database contains all sorts of indexes – Normal , function based, Domain, LOB and IOT index.

Here is the list of index types and which can be performed online.

Normal and Function Based index

These can be rebuilt online without any problem

alter <index_name> rebuild online;

Domain Index

These can be rebuilt online however can’t specify “no logging”

LOB indexes

These needs to be Moved.

alter table <your_table> move lob (<lob_column>) store as (tablespace <tablespace>);

Index-Organized Tables (IOT)

Can’t be rebuilt Online, instead they needs to be moved online.

Per documentation…Because index-organized tables are primarily stored in a B-tree index, can encounter fragmentation as a consequence of incremental updates. MOVE statement reduces the fragmentation.

ALTER TABLE <IOT_name> MOVE ONLINE;

Other Restrictions Using ONLINE clause:

• Parallel DML is not supported during online index building. If you specify ONLINE and subsequently issue parallel DML statements, then Oracle Database returns an error.
• You cannot specify ONLINE for a bitmap join index or a cluster index.
• For a nonunique secondary index on an index-organized table, the number of index key columns plus the number of primary key columns that are included in the logical rowid in the index-organized table cannot exceed 32. The logical rowid excludes columns that are part of the index key.

Read Full Post »

Export and Import AWR data

Why do I need to Export?
you want to save AWR data for comparing with another database or after upgrading database and so on reasons.

 

How do I save AWR report?

You can simply run awrrpt.sql located in <Oracle_home>\rdbms\admin directory and save the reports.

Another way is to export the AWR report itself similar to database export.

Below script let you export AWR data to dump file.

 

BEGIN
DBMS_SWRF_INTERNAL.AWR_EXTRACT (DMPFILE   => 'awr_data.dmp', -- Dump file name
DMP_DIR   => 'DB_DIR', -- Database directory
BID       => 4283, -- Beginning snapshot
EID       => 4315 –- Ending snapshot
);
END;

 

Prerequisites: (1) you need to have Execute privs on the DBMS_SWRF_INTERNAL package, (2) Set-up a Database directory. You can use existing directory [ query from DBA_DIRECTORIES]. In above case, I have directory called DB_DIR

 

BID and EID represents the beginning and ending snapshot ids. You can obtain available snapshots from DBA_HIST_SNAPSHOT view

  

Once you complete the exporting, you can import back into different/same database using below statements. This is two step process

 

BEGIN      DBMS_SWRF_INTERNAL.AWR_LOAD (SCHEMA => "DEV",      
  DMPFILE   => 'awr_data.dmp', -- Dump file name exported earlier.      
  DMP_DIR   => 'DB_DIR', -- Database directory ); END;
 

Above statement loads AWR data into DEV schema, later that needs to be moved to SYS.

Same pre-requisites applies. Note that if database is located on different server, you need to move dump file where the Database directory is configured on destination database.

 

Second part of the import is move imported data to SYS using below statement

 

BEGIN
     DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHEMA => “DEV
);
END;

 

 

Read Full Post »