Title: Recreating Indexes for Schema in Another Tablespace
Author:Sergey Alentyev, a DBA for Surgut Telecom Network in Surgut, Russia. When you want to recreate all indexes in schema (including indexes that created as Primary Key and Unique constraints) to another tablespace for better performance, try using the following procedure.Source/Text/Comments
/* ====================================================================================
Re-creation Indexes for Schema in other Tablespace
Sergey Alentyev, a Oracle DBA for Surgut Telecom Network, Inc., in Surgut, Russia.
When do You want re-create all indexes in schema (including indexes that created as
Primary Key and Unique constraints) to another tablespace for better performance
this procedure may be careful for You.
Procedure created by SYS and executed by any user.
Execute by SQL*Plus :
SQL> execute sys.re_index('SCHEMA','NEW_TABLESPACE','SCRIPT_DIRECTORY','SCRIPT_FILE')
==================================================================================== */
DROP PROCEDURE sys.re_index
/
CREATE OR REPLACE
PROCEDURE SYS.RE_INDEX
(my_schema VARCHAR2,
my_ts VARCHAR2,
my_dir VARCHAR2,
my_file VARCHAR2)
IS
CURSOR c_ind IS
SELECT owner, index_name, table_name, uniqueness
FROM dba_indexes
WHERE table_owner = my_schema;
CURSOR c_ind_col (i_owner VARCHAR2, i_name VARCHAR2, t_name VARCHAR2) IS
SELECT column_name,column_position
FROM dba_ind_columns
WHERE index_owner = i_owner AND
index_name = i_name AND
table_name = t_name
ORDER BY column_position;
CURSOR c_cons (c_name VARCHAR2) IS
SELECT DECODE(constraint_type,
'P','PRIMARY KEY',
'U','UNIQUE',
'other constraint'),
DECODE(status,'DISABLED',' DISABLE ','')
FROM dba_constraints
WHERE owner = my_schema AND
constraint_name = c_name AND
constraint_type IN ('P','U');
drop_text VARCHAR2(1000);
create_text VARCHAR2(1000);
drop_fk_text VARCHAR2(1000);
create_fk_text VARCHAR2(1000);
columns_list VARCHAR2(100);
col NUMBER(2);
cons_type VARCHAR2(30);
cons_status VARCHAR2(30);
file_handle UTL_FILE.FILE_TYPE;
TYPE TabType IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
MyTab TabType;
i BINARY_INTEGER;
--======== Search Foreign Key on Primary Key or Unique ===
PROCEDURE CONS_FK
(r_own VARCHAR2,
r_cons_name VARCHAR2,
r_table_name VARCHAR2,
r_col_list VARCHAR2)
IS
CURSOR c_cons_fk (my_owner VARCHAR2, my_cons_name VARCHAR2) IS
SELECT owner fk_owner,
constraint_name fk_constraint_name,
table_name fk_table_name,
DECODE(delete_rule,'CASCADE',' ON DELETE CASCADE ',' ') fk_delete_rule,
DECODE(status,'DISABLED',' DISABLE ','') fk_status
FROM dba_constraints
WHERE constraint_type = 'R' AND
r_owner = my_owner AND
r_constraint_name = my_cons_name;
CURSOR c_cons_col (c_owner VARCHAR2, c_name VARCHAR2, t_name VARCHAR2) IS
SELECT column_name,position
FROM dba_cons_columns
WHERE owner = c_owner AND
constraint_name = c_name AND
table_name = t_name
ORDER BY position;
columns_fk_list VARCHAR2(100);
BEGIN
i := 0;
FOR r_cons_fk IN c_cons_fk(r_own,r_cons_name) LOOP
IF c_cons_fk%FOUND THEN
------- Search column list for constraint Foreign Key -------
FOR r_cons_col IN c_cons_col(r_cons_fk.fk_owner,
r_cons_fk.fk_constraint_name,
r_cons_fk.fk_table_name) LOOP
IF r_cons_col.position = 1 THEN
columns_fk_list := r_cons_col.column_name;
ELSE
columns_fk_list := columns_fk_list||','||r_cons_col.column_name;
END IF;
END LOOP;
---------------------------------------------------
drop_fk_text := 'ALTER TABLE '
||r_cons_fk.fk_owner
||'.'
||r_cons_fk.fk_table_name
||' DROP CONSTRAINT '
||r_cons_fk.fk_constraint_name
||';'
;
UTL_FILE.PUT_LINE(file_handle,drop_fk_text);
create_fk_text := 'ALTER TABLE '
||r_cons_fk.fk_owner
||'.'
||r_cons_fk.fk_table_name
||' ADD CONSTRAINT '
||r_cons_fk.fk_constraint_name
||' FOREIGN KEY ('
||columns_fk_list
||') REFERENCES '
||my_schema
||'.'
||r_table_name
||'('
||r_col_list
||') '
||r_cons_fk.fk_delete_rule
||r_cons_fk.fk_status
||';'
;
i := i + 1;
MyTab(i) := create_fk_text;
ELSE
drop_fk_text := '';
create_fk_text := '';
END IF;
END LOOP;
END;
---------------------------------------------------------------------
BEGIN
file_handle := UTL_FILE.FOPEN(my_dir,my_file,'W');
UTL_FILE.PUT_LINE(file_handle,'---------------------------------------------------------------');
UTL_FILE.PUT_LINE(file_handle,'-- Script for re-creation all indexes in schema '||my_schema);
UTL_FILE.PUT_LINE(file_handle,'-- to tablespace '||my_ts);
UTL_FILE.PUT_LINE(file_handle,'---------------------------------------------------------------');
--------- Search schema indexes ----------------
FOR r_ind IN c_ind LOOP
UTL_FILE.NEW_LINE(file_handle);
------- Search index column list -------
FOR r_ind_col IN c_ind_col(r_ind.owner, r_ind.index_name, r_ind.table_name) LOOP
IF r_ind_col.column_position = 1 THEN
columns_list := r_ind_col.column_name;
ELSE
columns_list := columns_list||','||r_ind_col.column_name;
END IF;
END LOOP;
---------------------------------------------------
-- Search any CONSTRAINTs, that correspond to indexes ---
OPEN c_cons (r_ind.index_name);
FETCH c_cons INTO cons_type,cons_status;
IF c_cons%FOUND THEN
drop_text := 'ALTER TABLE '
||my_schema
||'.'
||r_ind.table_name
||' DROP CONSTRAINT '
||r_ind.index_name
||';'
;
create_text := 'ALTER TABLE '
||my_schema
||'.'
||r_ind.table_name
||' ADD CONSTRAINT '
||r_ind.index_name
||' '
||cons_type
||' ('
||columns_list
||') USING INDEX TABLESPACE '
||my_ts
||cons_status
||';'
;
-- Search Foreign Key on Primary Key or Unique
cons_fk(my_schema, r_ind.index_name, r_ind.table_name, columns_list);
ELSE
drop_text := 'DROP INDEX '
||my_schema
||'.'
||r_ind.index_name
||';'
;
create_text := 'CREATE INDEX '
||my_schema
||'.'
||r_ind.index_name
||' ON '
||my_schema
||'.'
||r_ind.table_name
||'('
||columns_list
||') TABLESPACE '
||my_ts
||';'
;
END IF;
CLOSE c_cons;
UTL_FILE.PUT_LINE(file_handle,drop_text);
UTL_FILE.PUT_LINE(file_handle,create_text);
i := MyTab.FIRST;
WHILE i IS NOT NULL LOOP
UTL_FILE.PUT_LINE(file_handle,MyTab(i));
MyTab(i) := NULL;
i := MyTab.NEXT(i);
END LOOP;
END LOOP;
UTL_FILE.PUT_LINE(file_handle,'--- The end ---');
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('--- NO_DATA_FOUND ---!');
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE('--- UTL_FILE.INTERNAL_ERROR ---!');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('--- UTL_FILE.INVALID_FILEHANDLE ---!');
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('--- UTL_FILE.IINVALID_MODE ---!');
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('--- UTL_FILE.INVALID_OPERATION ---!');
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('--- UTL_FILE.INVALID_PATH ---!');
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('--- UTL_FILE.READ_ERROR ---!');
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('--- UTL_FILE.WRITE_ERROR ---!');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('--- VALUE_ERROR ---!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('--- Error ---!');
END;
/
GRANT EXECUTE ON sys.re_index TO public
/