Scheduled Database Maintenance Activity for Oracle

For Oracle 9i

Schedule frequent index rebuilding for Oracle 9i to ensure that the eG database does not suffer performance degradations. It is recommended that you stop the eG manager and then execute the procedures detailed below to rebuild indexes.

Execute the following commands on Windows:

SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 1000
SET TERMOUT OFF
SPOOL C:\rebuildindex.sql
SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD; ' FROM USER_INDEXES WHERE INDEX_NAME LIKE 'IDX_%';
SPOOL OFF
SET TERMOUT ON
SET HEADING ON
SET FEEDBACK ON
SET PAGESIZE 40
EXIT

On Unix, the commands will be as follows:

SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 1000
SET TERMOUT OFF
SPOOL /opt/rebuildindex.sql
SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD ; ' FROM USER_INDEXES WHERE INDEX_NAME LIKE 'IDX_%';
SPOOL OFF
SET TERMOUT ON
SET HEADING ON
SET FEEDBACK ON
SET PAGESIZE 40
EXIT
Copy the queries from the C:\rebuildindex.sql file (or the /opt/rebuildindex.sql file, as the case may be) that is created in the previous step, paste them on to the SQL prompt, and execute the queries. This will rebuild secondary indexes and reduce fragmentation.

For Oracle 10g

Oracle 10g recommends a two-pronged approach to database maintenance:

Index rebuilding, and;
Reclamation of the space that is released by eG’s daily database cleanup activity

Both these procedures have to be performed at recommended intervals to ensure peak performance of the eG database.

Rebuilding Indexes for Oracle 10g

Index rebuilding in Oracle 10g can be performed in the online or offline mode. We recommend that you perform index rebuilding once in a while - say, once every 6 months - to ensure peak performance of the eG database.

Both the online and offline procedures are discussed hereunder.

Offline index rebuilding

Create a procedure in eG database by executing the commands below:

create or replace PROCEDURE IndexQueries
as
tableName varchar2(50);
tableIndex varchar2(50);
cursor cur_table is select table_name from user_tables;
begin
open cur_table;
loop
fetch cur_table into tableName;
EXIT WHEN cur_table%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(CHR(10)||'Alter table ' || tableName || ' move;');
for curindex_name in (select index_name from user_indexes where table_name=tableName)
loop
DBMS_OUTPUT.PUT_LINE('Alter index ' || curindex_name.index_name || ' rebuild;');
end loop;
end loop;
close cur_table;

Exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('');
close cur_table;
end;
Execute the following script on Windows to generate the index queries:

SET SERVEROUTPUT ON
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 1000
SET TERMOUT OFF
SPOOL C:\rebuildindex.sql
Exec IndexQueries
SPOOL OFF
SET TERMOUT ON
SET HEADING ON
SET FEEDBACK ON
SET PAGESIZE 40

On Unix, the commands will be as follows:

SET SERVEROUTPUT ON
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 1000
SET TERMOUT OFF
SPOOL /opt/rebuildindex.sql
Exec IndexQueries
SPOOL OFF
SET TERMOUT ON
SET HEADING ON
SET FEEDBACK ON
SET PAGESIZE 40
Copy the queries from the C:\rebuildindex.sql file (or the /opt/rebuildindex.sql file, as the case may be) that is generated in the previous step, paste them onto the SQL prompt, and execute the queries. This will rebuild the primary and secondary indexes and reduce fragmentation.

Online index rebuilding

The first step is to generate the index queries. For that, on Windows, execute the following script:

SET HEADING OFF

SET FEEDBACK OFF

SET PAGESIZE 1000

SET TERMOUT OFF

SPOOL C:\IndexRebuildOnline.sql

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD ONLINE; ' FROM USER_INDEXES where index_name like 'PK%' or index_name like 'IDX%' order by table_name;

SPOOL OFF

SET TERMOUT ON

SET HEADING ON

SET FEEDBACK ON

SET PAGESIZE 40 EXIT

On Unix, execute the following commands:

SET HEADING OFF

SET FEEDBACK OFF

SET PAGESIZE 1000

SET TERMOUT OFF

SPOOL /opt/IndexRebuildOnline.sql

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD ONLINE; ' FROM USER_INDEXES where index_name like 'PK%' or index_name like 'IDX%' order by table_name;

SPOOL OFF

SET TERMOUT ON

SET HEADING ON

SET FEEDBACK ON

SET PAGESIZE 40 EXIT
Copy the queries from the C:\IndexRebuildOnline.sql file (on Windows; on Unix, this will be the /opt/IndexRebuildOnline.sql file) that is generated in the previous step, paste them onto the SQL prompt, and execute the queries. This will rebuild the primary and secondary indexes and reduce fragmentation.

Reclamation of Database Space

The eG manager automatically runs a cleanup procedure on the eG database every day to remove obsolete/stale data from the database and to make space for recent data. In the process, free space is created in the eG database, which will have to be reclaimed time and again, so as to avoid the performance degradation that may creep in due to fragmentation. Using the procedure discussed below, this can be achieved. Since this procedure can even be run in the 'Online’ mode, it is recommended that you perform it once every 15 days.

Create a file named SHRINK_SPACE.SQL in any location on the eG database host - say C:\ on Windows or /opt/usr on Unix - and save the following script to that file. Given below is a sample script on Windows:

SPOOL E:\ROW_ENABLE.SQL
SELECT 'ALTER TABLE '||TABLE_NAME||' ENABLE ROW MOVEMENT;' FROM USER_TABLES;
SPOOL OFF

SPOOL E:\ROW_ENABLE_OUT.TXT
@E:\ROW_ENABLE.SQL
SPOOL OFF

SPOOL E:\OBJECT_SHRINK.SQL
SELECT 'ALTER TABLE '||TABLE_NAME||' SHRINK SPACE CASCADE;' FROM USER_TABLES;
SPOOL OFF

SPOOL E:\OBJECT_SHRINK_OUT.TXT
@E:\OBJECT_SHRINK.SQL
SPOOL OFF

Given below is a sample script on Unix:

SPOOL OPT/USR/ROW_ENABLE.SQL

SELECT 'ALTER TABLE '||TABLE_NAME||' ENABLE ROW MOVEMENT;' FROM USER_TABLES;

SPOOL OFF

SPOOL OPT/USR/ROW_ENABLE_OUT.TXT
@OPT/USR/ROW_ENABLE.SQL
SPOOL OFF

SPOOL OPT/USR/OBJECT_SHRINK.SQL
SELECT 'ALTER TABLE '||TABLE_NAME||' SHRINK SPACE CASCADE;' FROM USER_TABLES;
SPOOL OFF

SPOOL OPT/USR/OBJECT_SHRINK_OUT.TXT
@OPT/USR/OBJECT_SHRINK.SQL
SPOOL OFF
Next, to run the script, login to the eG database as the <eGDBUser> and issue the following command from the SQL prompt.

On Windows, the syntax of the command is:

SQL > @E:\shRINK_SPACE.SQL

On Unix, the command syntax is as follows:

SQL > @/opt/SHRINK_SPACE.SQL