Oracle Scripts - dbapool.com

REM: Script rebuilds the schema with minimal downtime 
REM:
REM: Author: Arulselvaraj  A
REM: Date Submitted: 02-Jan-2006
REM: Date Posted: 03-Jan-2006
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

CREATE OR REPLACE PROCEDURE schema_rebuild authid current_user IS
  CURSOR obj_list IS
  SELECT decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
  decode( segment_type, 'TABLE', 1, 2 ) order_col2,
  decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) m_or_r,segment_type,segment_name
  FROM user_segments, (SELECT table_name, index_name FROM user_indexes )
  WHERE segment_type IN ( 'TABLE', 'INDEX' )
  AND segment_name = index_name (+)  ORDER BY 1, 2 ;

reb_str VARCHAR2(200) ;
  username varchar2(20);

BEGIN
username:=user;
  FOR i IN OBJ_LIST LOOP
     IF i.segment_type = 'INDEX' THEN
                reb_str := 'alter ' || i.segment_type || ' ' || i.segment_name ||' '|| i.m_or_r ||' online';
     ELSE
            reb_str := 'alter ' || i.segment_type || ' ' || i.segment_name ||' '|| i.m_or_r;
     END IF;
     EXECUTE IMMEDIATE reb_str;
     reb_str := '';
  END LOOP ;
                dbms_stats.gather_schema_stats (ownname=>username, method_opt=>'FOR ALL INDEXED COLUMNS', cascade=>TRUE);
END ;
/

Home Discussion Forum FAQs Articles Jobs Newsletters Directory Downloads

Our Premium Sponsor