garden2.jpg

How to Rebuild Oracle Indexes Move


This script is especially useful after you have performed an import with FROMUSER TOUSER option, where the original tablespace name is different from the targeted TOUSER tablespace, causing all tables and indexes to be stored within their original owner’s default tablespace.

Lets say you do an export for owner=PA where all tables are in PAD tablespace and all indexes are in PAX tablespace, now you do an import into a different schema called XPA; FROMUSER=PA TOUSER=XPA, after the import into XPA all tables are going to be stored in PAD and indexes in PAX. But you rather want to have all tables owned by XPA in XPAD tablepsace and all indexes owned by XPA in XPAX tablespace.

Stored procedure p_rebuild_indexes_to_ts will achieve just that. p_rebuild_indexes_to_ts generates dynamic PL/SQL which rebuilds all the indexes belonging to a given user and moves them into a given tablespace.

idbasolutions.com Apr 2008

– This script creates a PL/SQL procedure that

– generates dynamic sql which rebuilds

– all indexes for a given OWNER and moves them into a

– different tablespace.

– Do not run this procedure at pick time.

– Disclaimer: Use on database – especially production databases

– is at DBAs own risk.

– This procedure was tested on Oracle 9.2.0.4 running Oracle Finacial 11.5.9

– Use this procedure as follow:

– Ex: From SQL prompt: exec p_rebuild_indexes_to_ts (‘XPA’,’XPAX’)

– where XPA is the owner of indexes to be moved into XPAX tablespace..

– 05-Apr-2007

create or replace procedure

p_rebuild_indexes_to_ts (schema IN dba_indexes.owner%type,

ts_name IN dba_tablespaces.tablespace_name%type)

as

v_cursorid integer;

status integer;

cursor c_dba_indexes is

select owner,index_name

from dba_indexes i

where owner=upper(schema)

and STATUS=’VALID’

and index_NAME not like ‘SYS_IOT_OVER_%’

and not exists (select 1

from dba_constraints c

where CONSTRAINT_TYPE =’P’

and c.INDEX_NAME=i.index_NAME

and c.INDEX_OWNER=i.OWNER)

and not exists (select 1

from dba_lobs l

where l.INDEX_NAME=i.index_NAME

and l.OWNER=i.OWNER);

v_dba_indexes c_dba_indexes%rowtype;

begin

open c_dba_indexes;

v_cursorid:=dbms_sql.open_cursor;

fetch c_dba_indexes into v_dba_indexes;

if (c_dba_indexes%notfound) then

dbms_output.put_line(‘Owner ‘||

upper(schema)||’ : ‘||

‘No indexes were to be found for this schema.’);

end if;

while ( c_dba_indexes%found ) loop

dbms_sql.parse(v_cursorid,

‘ALTER INDEX ‘||v_dba_indexes.owner||’.'||

v_dba_indexes.index_name||

‘ REBUILD TABLESPACE ‘||ts_name,

dbms_sql.native);

status:=dbms_sql.execute(v_cursorid);

dbms_output.put_line(‘Index Rebuild: ‘||

v_dba_indexes.owner||’.'||

v_dba_indexes.index_name||’ ‘||ts_name);

fetch c_dba_indexes into v_dba_indexes;

end loop;

close c_dba_indexes;

dbms_sql.close_cursor(v_cursorid);

exception

when others then

dbms_output.put_line(‘Error…… ‘);

dbms_sql.close_cursor(v_cursorid);

raise;

end p_rebuild_indexes_to_ts;

Latest Blog Items

email

Thank you for your interest in idbasolutions.

Contact us using this email: contact.us@idbasolutions.com