SQL

How to Move Tables by SQL Script

I usually move some tables for data reorganization, and I found the procedure is pretty routine:
  1. Moving the table.
  2. Rebuilding indexes related to this table.
  3. Analyzing all segments related to this table.
So I wrote a SQL script to handle this automatically, one table at a time.
[oracle@test ~]$ cat alter_table_move.sql
set escape on;
accept table_owner char prompt 'Table Owner: ';
accept table_name char prompt 'Table Name: ';
accept tablespace_name char prompt 'Target Tablespace: ';
alter table &table_owner.&table_name move tablespace &tablespace_name;
set heading off echo off feedback off verify off pagesize 0;
spool alter_index_rebuild.sql
select 'alter index ' || owner || '.' || index_name || ' rebuild;' from dba_indexes where table_owner = upper('&table_owner') and table_name = upper('&table_name');
spool off;
set echo on feedback on verify on;
@alter_index_rebuild.sql
exec dbms_stats.gather_table_stats(ownname=> '&table_owner', tabname=> '&table_name', partname=> NULL , estimate_percent=> 33, cascade => TRUE );
exit

And this is how I use it:
[oracle@test ~]$ sqlplus -S "/ as sysdba" @alter_table_move.sql
Table Owner: HR
Table Name: EMPLOYEES
Target Tablespace: USERS
old   1: alter table &table_owner.&table_name move tablespace &tablespace_name
new   1: alter table HR.EMPLOYEES move tablespace USERS

Table altered.

alter index HR.EMP_EMAIL_UK rebuild;
alter index HR.EMP_EMP_ID_PK rebuild;
alter index HR.EMP_DEPARTMENT_IX rebuild;
alter index HR.EMP_JOB_IX rebuild;
alter index HR.EMP_MANAGER_IX rebuild;
alter index HR.EMP_NAME_IX rebuild;

Index altered.


Index altered.


Index altered.


Index altered.


Index altered.


Index altered.


PL/SQL procedure successfully completed.

Hope this script will be helpful to you.

Leave a Reply

Your email address will not be published. Required fields are marked *