Oracle SQL Commands

1. Table and tablespace location

Finding out who owns a table and what tablespace it is in is a pretty common need of the DBA. In this query, we use the dba_tables view to find the owner and tablespace name of the EMP table.

SELECT table_name, tablespace_name, STATUS
  FROM user_tables
 WHERE table_name LIKE :p_table_pattern;

SELECT table_name, tablespace_name, STATUS
  FROM all_tables
 WHERE table_name LIKE :p_table_pattern;

SQL>  select owner, table_name, tablespace_name

  2   from dba_tables

  3  where table_name='EMP';

 

OWNER                TABLE_NAME           TABLESPACE_NAME

-------------------- -------------------- -------------

SCOTT                EMP                  USERS

POLL                 EMP                  USERS

 

2. 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;