move_indexes.sql

Language: English
Programming Language: SQL
Published by: buzzwang [not registered]
Last Update: 5/15/2006
Views: 1013

Description

Relocates indexes in an Oracle tablespaceto any other tablespace. When run inSQL*Plus the script displays all tablespacesthat contain indexes. The script thenprompts for the source tablespace and thedestination tablespace. When finished thescript displays how many indexes whererelocated.

Code

Display line numbers

1 -- Name: move_indexes.sql 2 -- Author: David F. Newman <buzzwang@ourvillage.com> 3 -- Version: 1.0 4 -- Description: Moves indexes from one tablespace to another in Oracle. 5 6 set heading off 7 set feedback off 8 set pagesize 0 9 set verify off 10 set serveroutput on 11 12 select 'Tablespaces with Indexes...' from dual; 13 select distinct tablespace_name from all_indexes 14 where tablespace_name not in ('SYSTEM'); 15 ACCEPT source_ts CHAR PROMPT 'What is the source Tablespace? ' 16 ACCEPT dest_ts CHAR PROMPT 'What is the destination Tablespace? ' 17 18 DECLARE 19 -- 12 + 30 + 20 + 30 = 92 20 rebuild_command VARCHAR2(93); 21 ddl_cursor_id INTEGER; 22 ignore_for_ddl INTEGER; 23 index_count INTEGER; 24 BEGIN 25 ddl_cursor_id := DBMS_SQL.OPEN_CURSOR; 26 index_count := 0; 27 FOR my_index in ( 28 SELECT index_name, owner FROM all_indexes 29 WHERE tablespace_name = upper('&source_ts')) LOOP 30 31 rebuild_command := 'ALTER INDEX ' 32 ||my_index.owner 33 ||'.' 34 ||my_index.index_name 35 ||' REBUILD TABLESPACE ' 36 ||'&dest_ts'; 37 38 DBMS_SQL.PARSE (ddl_cursor_id, rebuild_command, DBMS_SQL.NATIVE); 39 ignore_for_ddl := DBMS_SQL.EXECUTE (ddl_cursor_id); 40 41 42 index_count := index_count + 1; 43 44 END LOOP; 45 46 DBMS_OUTPUT.PUT_LINE('Moved '||index_count||' indexes'); 47 48 --Deallocate the cursor. 49 DBMS_SQL.CLOSE_CURSOR(ddl_cursor_id); 50 END; 51 / 52 53

move_indexes.sql

Description

Code

No comments avaiable

Add a comment