|
Simple script to to move all tables in a schema
The following SQL script generates DDL required for moving all tables in a schema and rebuilding the indexes
select 'alter table '||table_name||' MOVE TABLESPACE ;' from user_tables;
select 'alter index '||index_name||' rebuild online tablespace
The following PL/SQL code generates script for moving selected tables and its indexes to different tablespace and analyze the objects. The list of tables can either be placed in IN list or can be populated in a seperate table move_tables_list
set serveroutput ON size 320000
set linesize 10000
set pagesize 0
set trimspool ON
spool move_tables.sql
DECLARE
n1 NUMBER;
v_target_ts VARCHAR2 (30) := 'DATA';
str VARCHAR2 (1000);
BEGIN
FOR c2 IN (SELECT table_name, v_target_ts tablespace_name
FROM user_tables
WHERE table_name IN ('EMP'))
LOOP
str :=
'alter table '
|| c2.table_name
|| ' move tablespace '
|| c2.tablespace_name;
DBMS_OUTPUT.put_line (str || ';');
FOR c1 IN (SELECT index_name, index_type
FROM user_indexes
WHERE table_name = c2.table_name
AND index_type IN ('NORMAL', 'BITMAP'))
LOOP
str :=
'alter index '
|| c1.index_name
|| ' rebuild online tablespace '
|| c2.tablespace_name;
DBMS_OUTPUT.put_line (str || ';');
END LOOP;
str := 'analyze table ' || c2.table_name || ' compute statistics';
DBMS_OUTPUT.put_line (str || ';');
FOR c1 IN (SELECT index_name
FROM user_indexes
WHERE table_name = c2.table_name
AND index_type IN ('NORMAL', 'BITMAP'))
LOOP
str := 'analyze index ' || c1.index_name || ' compute statistics';
DBMS_OUTPUT.put_line (str || ';');
END LOOP;
END LOOP;
END;
/
spool off
|
Moving Table, Indexes and LOB segments
The following PL/SQL code can be used to generate SQL scripts for moving tables, indexes and LOB segments to a different tablespace
set serveroutput ON size 320000
set linesize 10000
set pagesize 0
set trimspool ON
spool move_tables_lobs.sql
DECLARE
n1 NUMBER;
v_target_ts VARCHAR2 (30) := 'DATA';
str VARCHAR2 (1000);
str_lobs VARCHAR2 (1000);
BEGIN
FOR c2 IN (SELECT table_name, v_target_ts tablespace_name
FROM user_tables
WHERE table_name IN ('EMP'))
LOOP
str :=
'alter table '
|| c2.table_name
|| ' move tablespace '
|| c2.tablespace_name;
DBMS_OUTPUT.put_line (str);
FOR c1lob IN (SELECT table_name, column_name
FROM user_lobs
WHERE table_name = c2.table_name)
LOOP
str_lobs :=
str_lobs
|| ' LOB('
|| c1lob.column_name
|| ') store as(tablespace '
|| c2.tablespace_name
|| ') ';
DBMS_OUTPUT.put_line (str_lobs);
END LOOP;
DBMS_OUTPUT.put_line ('/');
DBMS_OUTPUT.put_line (' ');
FOR c1 IN (SELECT index_name, index_type
FROM user_indexes
WHERE table_name = c2.table_name
AND index_type IN ('NORMAL', 'BITMAP'))
LOOP
str :=
'alter index '
|| c1.index_name
|| ' rebuild online tablespace '
|| c2.tablespace_name;
DBMS_OUTPUT.put_line (str || ';');
END LOOP;
str := 'analyze table ' || c2.table_name || ' compute statistics';
DBMS_OUTPUT.put_line (str || ';');
FOR c1 IN (SELECT index_name
FROM user_indexes
WHERE table_name = c2.table_name
AND index_type IN ('NORMAL', 'BITMAP'))
LOOP
str := 'analyze index ' || c1.index_name || ' compute statistics';
DBMS_OUTPUT.put_line (str || ';');
END LOOP;
END LOOP;
END;
/
spool off
|
Moving Partitioned Tables and Indexes and LOB segments
If the tables or Indexes are partitioned then the following script can be used
set serveroutput ON size 320000
set linesize 10000
set pagesize 0
set trimspool ON
spool move_tables_lobs.sql
/* Formatted on 2009/03/06 17:19 (Formatter Plus v4.8.8) */
DECLARE
n1 NUMBER;
v_target_ts VARCHAR2 (30) := 'DATA';
str VARCHAR2 (1000);
str_lobs VARCHAR2 (1000);
tname VARCHAR2 (30);
partname VARCHAR2 (30);
tsname VARCHAR2 (30);
PROCEDURE genlob
IS
BEGIN
FOR c1lob IN (SELECT table_name, column_name
FROM user_lobs
WHERE table_name = tname)
LOOP
DBMS_OUTPUT.put_line ( ' LOB('
|| c1lob.column_name
|| ') store as(tablespace '
|| tsname
|| ')'
);
END LOOP;
END;
PROCEDURE genindx
IS
n1 NUMBER;
BEGIN
FOR c1 IN (SELECT a.index_name, b.locality
FROM user_indexes a, user_part_indexes b
WHERE a.table_name = tname
AND a.index_name = b.index_name
AND a.index_type IN ('NORMAL', 'BITMAP'))
LOOP
n1 := 0;
FOR c2 IN (SELECT partition_name
FROM user_ind_partitions
WHERE index_name = c1.index_name)
LOOP
n1 := n1 + 1;
IF c1.locality = 'GLOBAL' OR partname = c2.partition_name
THEN
DBMS_OUTPUT.put_line ( 'alter index '
|| c1.index_name
|| ' rebuild partition '
|| c2.partition_name
|| ' online;'
);
DBMS_OUTPUT.put_line (' ');
END IF;
END LOOP;
IF n1 = 0
THEN
DBMS_OUTPUT.put_line ( 'alter index '
|| c1.index_name
|| ' rebuild online tablespace '
|| tsname
);
END IF;
END LOOP;
END;
BEGIN
FOR c2 IN (SELECT table_name, v_target_ts tablespace_name
FROM user_tables
WHERE table_name IN ('EMP'))
LOOP
tname := c2.table_name;
tsname := c2.tablespace_name;
n1 := 0;
FOR c2p IN (SELECT partition_name
FROM user_tab_partitions
WHERE table_name = c2.table_name)
LOOP
partname := c2p.partition_name;
n1 := n1 + 1;
DBMS_OUTPUT.put_line ('alter table ' || c2.table_name || ' move ');
DBMS_OUTPUT.put_line ( 'partition '
|| c2p.partition_name
|| ' tablespace '
|| c2.tablespace_name
);
genlob;
DBMS_OUTPUT.put_line ('/');
DBMS_OUTPUT.put_line (' ');
genindx;
END LOOP;
IF n1 = 0
THEN
DBMS_OUTPUT.put_line ( 'alter table '
|| c2.table_name
|| ' move tablespace '
|| c2.tablespace_name
);
genlob;
DBMS_OUTPUT.put_line ('/');
DBMS_OUTPUT.put_line (' ');
partname := ' ';
genindx;
END IF;
str := 'analyze table ' || c2.table_name || ' compute statistics';
DBMS_OUTPUT.put_line (str || ';');
FOR c1 IN (SELECT index_name
FROM user_indexes
WHERE table_name = c2.table_name
AND index_type IN ('NORMAL', 'BITMAP'))
LOOP
str := 'analyze index ' || c1.index_name || ' compute statistics';
DBMS_OUTPUT.put_line (str || ';');
END LOOP;
END LOOP;
END;
/
spool off
|
|