Rebuild index TABLESPACE
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
– Author Bobby Akbari.
–
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;
/