garden2.jpg

Relocate tables to a different 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_move_tables_to_ts will achieve just that.
p_move_tables_to_ts generate dynamic PL/SQL which moves all the tables belonging to a given user and move them into a given tablespace.

idbasolutions.com Apr 2008
– This script creates a PL/SQL procedure that
– generates dynamic sql which moves
– all tables for a given OWNER and move them into a
– different tablespace.
– This procedure might take hours to run if you have
– many tables and a very large database (VLDB). 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:
– Usage Ex: From SQL prompt:
set serveroutput on size 100000
exec p_move_tables_to_ts (‘XPA’,'XPAD’)
– where XPA is the owner of the tables to be moved into XPAD tablespace.
– 05-Apr-2007

create or replace procedure
p_move_tables_to_ts (schema IN dba_tables.owner%type,
ts_name IN dba_tablespaces.tablespace_name%type)
as
v_cursorid integer;
status integer;
cursor c_dba_tables is
select owner,table_name
from dba_tables t
where owner=upper(schema)
and TEMPORARY=’N’
and not exists (select 1
from dba_tab_columns c
where c.DATA_TYPE like ‘LONG%’
and c.table_NAME=t.table_NAME
and c.OWNER=t.OWNER);
v_dba_tables c_dba_tables%rowtype;
begin
open c_dba_tables;
v_cursorid:=dbms_sql.open_cursor;
fetch c_dba_tables into v_dba_tables;
if (c_dba_tables%notfound) then
dbms_output.put_line(‘Owner ‘||
upper(schema)||’ : ‘||
‘No tables were to be found for this schema.’);
end if;
while ( c_dba_tables%found ) loop
dbms_sql.parse(v_cursorid,
‘ALTER table ‘||v_dba_tables.owner||’.'||
v_dba_tables.table_name||
‘ move TABLESPACE ‘||ts_name||
‘ storage (initial 1M next 10M) ‘,
dbms_sql.native);
status:=dbms_sql.execute(v_cursorid);
dbms_output.put_line(‘Table moved: ‘||
v_dba_tables.owner||’.'||
v_dba_tables.table_name||’ ‘||ts_name|| ‘ storage (initial 1M next 10M) ‘);
fetch c_dba_tables into v_dba_tables;
end loop;
close c_dba_tables;
dbms_sql.close_cursor(v_cursorid);
exception
when others then
dbms_output.put_line(‘Error…… ‘);
dbms_sql.close_cursor(v_cursorid);
raise;
end p_move_tables_to_ts;

Latest Blog Items

email

Thank you for your interest in idbasolutions.

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