Here is the index
rebuilding part of a ksh script I use. Prior to this, I some variable
setting, check the existence and permissions on some files, check the existence
of tablespaces, check that the script is not already running, etc. In this
script, all the index extents are set to the same size which is fine
for this particular database.
--------- snip
------------
echo "RAT INDEX
REBUILD SCRIPT" >> "$MAILFILE"
############## First index build to alternate tablespace ###########
echo "--------------- START TIME: `/usr/bin/date +'%T %D'` --------------" >> "$MAILFILE"
{
sqlplus -s <<-XXXX
system/$SYSPASS@$ORACLE_SID
-- crap
-- crap
-- crap
############## First index build to alternate tablespace ###########
echo "--------------- START TIME: `/usr/bin/date +'%T %D'` --------------" >> "$MAILFILE"
{
sqlplus -s <<-XXXX
system/$SYSPASS@$ORACLE_SID
-- crap
-- crap
-- crap
set
serveroutput on
whenever sqlerror exit failure
whenever sqlerror exit failure
ALTER
TABLESPACE ALT_RAT_DATA_IDX COALESCE;
declare
cursor c1 is select owner,index_name from dba_indexes where tablespace_name = 'RAT_DATA_IDX';
a integer;
b integer;
begin
dbms_output.enable(500000);
cursor c1 is select owner,index_name from dba_indexes where tablespace_name = 'RAT_DATA_IDX';
a integer;
b integer;
begin
dbms_output.enable(500000);
for x
in c1 loop
execute immediate 'ALTER INDEX '||x.owner||'.'||x.index_name||' REBUILD INITRANS 20 STORAGE(INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0) TABLESPACE ALT_RAT_DATA_IDX ONLINE';
dbms_output.put_line('ALT_RAT_DATA_IDX '||x.owner||'.'||x.index_name);
end loop;
execute immediate 'ALTER INDEX '||x.owner||'.'||x.index_name||' REBUILD INITRANS 20 STORAGE(INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0) TABLESPACE ALT_RAT_DATA_IDX ONLINE';
dbms_output.put_line('ALT_RAT_DATA_IDX '||x.owner||'.'||x.index_name);
end loop;
select
count(*) into a from dba_indexes where tablespace_name =
'RAT_DATA_IDX';
select count(*) into b from dba_indexes where tablespace_name = 'ALT_RAT_DATA_IDX';
dbms_output.put_line('RAT_DATA_IDX = '||a||' ALT_RAT_DATA_IDX = '||b);
select count(*) into b from dba_indexes where tablespace_name = 'ALT_RAT_DATA_IDX';
dbms_output.put_line('RAT_DATA_IDX = '||a||' ALT_RAT_DATA_IDX = '||b);
exception
when others then
dbms_output.put_line('ERROR: '||SQLCODE||' '||SQLERRM);
end;
/
XXXX
} | /usr/bin/sed '/^$/d; s/^ *//g; s/^ *$//g' | while read LINE; do
if [ -n "`echo $LINE | /usr/bin/sed -n '/^ERROR/p'`" ]; then
echo "PROBLEM encountered on first index rebuild." >> "$MAILFILE"
echo "$LINE" >> "$MAILFILE"
while read LINE; do
echo "$LINE" >> "$MAILFILE"
done
echo "$0 $* did not finished" >> "$MAILFILE"
mailx -s "BROKE: rat index rebuild" $SUPPORT < "$MAILFILE"
exit 1
fi
echo "$LINE" >> "$MAILFILE"
done
when others then
dbms_output.put_line('ERROR: '||SQLCODE||' '||SQLERRM);
end;
/
XXXX
} | /usr/bin/sed '/^$/d; s/^ *//g; s/^ *$//g' | while read LINE; do
if [ -n "`echo $LINE | /usr/bin/sed -n '/^ERROR/p'`" ]; then
echo "PROBLEM encountered on first index rebuild." >> "$MAILFILE"
echo "$LINE" >> "$MAILFILE"
while read LINE; do
echo "$LINE" >> "$MAILFILE"
done
echo "$0 $* did not finished" >> "$MAILFILE"
mailx -s "BROKE: rat index rebuild" $SUPPORT < "$MAILFILE"
exit 1
fi
echo "$LINE" >> "$MAILFILE"
done
echo
"------------- END FIRST REBUILD: `/usr/bin/date +'%T %D'` --------------"
>> "$MAILFILE"
##############
Second index build back to original tablespace ###########
{
sqlplus -s <<-XXXX
system/$SYSPASS@$ORACLE_SID
-- crap
-- crap
-- crap
{
sqlplus -s <<-XXXX
system/$SYSPASS@$ORACLE_SID
-- crap
-- crap
-- crap
set
serveroutput on
whenever sqlerror exit failure
whenever sqlerror exit failure
ALTER
TABLESPACE RAT_DATA_IDX COALESCE;
declare
cursor c1 is select owner,index_name from dba_indexes where tablespace_name = 'ALT_RAT_DATA_IDX';
cursor c3 is select owner,object_name from dba_objects where status = 'INVALID';
a integer;
b integer;
begin
dbms_output.enable(100000);
cursor c1 is select owner,index_name from dba_indexes where tablespace_name = 'ALT_RAT_DATA_IDX';
cursor c3 is select owner,object_name from dba_objects where status = 'INVALID';
a integer;
b integer;
begin
dbms_output.enable(100000);
for x
in c1 loop
execute immediate 'ALTER INDEX '||x.owner||'.'||x.index_name||' REBUILD INITRANS 20 STORAGE(INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0) TABLESPACE RAT_DATA_IDX ONLINE';
dbms_output.put_line('RAT_DATA_IDX '||x.owner||'.'||x.index_name);
end loop;
execute immediate 'ALTER INDEX '||x.owner||'.'||x.index_name||' REBUILD INITRANS 20 STORAGE(INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0) TABLESPACE RAT_DATA_IDX ONLINE';
dbms_output.put_line('RAT_DATA_IDX '||x.owner||'.'||x.index_name);
end loop;
select
count(*) into a from dba_indexes where tablespace_name =
'RAT_DATA_IDX';
select count(*) into b from dba_indexes where tablespace_name = 'ALT_RAT_DATA_IDX';
dbms_output.put_line('RAT_DATA_IDX = '||a||' ALT_RAT_DATA_IDX = '||b);
select count(*) into b from dba_indexes where tablespace_name = 'ALT_RAT_DATA_IDX';
dbms_output.put_line('RAT_DATA_IDX = '||a||' ALT_RAT_DATA_IDX = '||b);
dbms_output.put_line('----------------------- INVALID OBJECTS
-------------------------');
for x
in c3
loop
dbms_output.put_line(x.owner||'.'||x.object_name);
end loop;
dbms_output.put_line(x.owner||'.'||x.object_name);
end loop;
exception
when others then
dbms_output.put_line('ERROR: '||SQLCODE||' '||SQLERRM);
end;
/
when others then
dbms_output.put_line('ERROR: '||SQLCODE||' '||SQLERRM);
end;
/
ALTER
TABLESPACE RAT_DATA_IDX COALESCE;
ALTER TABLESPACE ALT_RAT_DATA_IDX COALESCE;
ALTER TABLESPACE ALT_RAT_DATA_IDX COALESCE;
XXXX
} | /usr/bin/sed '/^$/d; s/^ *//g; s/^ *$//g' | while read LINE; do
if [ -n "`echo $LINE | /usr/bin/sed -n '/^ERROR/p'`" ]; then
echo "PROBLEM encountered on second index rebuild."
echo "$LINE"
while read LINE; do
echo "$LINE"
done
exit 1
fi
echo "$LINE" >> "$MAILFILE"
done
} | /usr/bin/sed '/^$/d; s/^ *//g; s/^ *$//g' | while read LINE; do
if [ -n "`echo $LINE | /usr/bin/sed -n '/^ERROR/p'`" ]; then
echo "PROBLEM encountered on second index rebuild."
echo "$LINE"
while read LINE; do
echo "$LINE"
done
exit 1
fi
echo "$LINE" >> "$MAILFILE"
done
echo
"------------- END SECOND REBUILD: `/usr/bin/date +'%T %D'`
--------------" >> "$MAILFILE"
mailx -s "SUCCESS:
rat index rebuild" $SUPPORT < "$MAILFILE"
exit
0