ORACLE:PL/SQL script to rebuild Table(s) and it’s corresponding indexes.
Note :
Please implement in lower environments before proceeding to Production.
Pre requisites :
Please download and check below file for pre-requisites.
The below script will be used for
1.Rebuild table(s)
2.Rebuild Indexes for corresponding table(s).
3.Pre & Post validation steps.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 |
cat table_rebuild_obj.sql conn vinod/abcd delete TABLE_INDEXNAME; delete dba_constraints_before ; delete dba_constraints_after; delete dba_objects_before; delete dba_objects_after; commit; insert into dba_constraints_before select CONSTRAINT_TYPE,STATUS,count(1) from dba_constraints where owner='VINOD' group by CONSTRAINT_TYPE,STATUS; commit; insert into dba_objects_before select owner,object_type , count(*),status from dba_objects where owner='VINOD' group by object_type,status,owner order by owner,object_type; commit; set serveroutput on set ver off set trimspool on set lines 199 col owner for a15 col segment_name for a30 col segment_type for a25 col tablespace_name for a22 set pagesize 150 col part_name for a30 compu sum of size_mb on report break on report skip 2 select owner, segment_name,partition_name part_name, buffer_pool, segment_type, tablespace_name, extents, round(bytes/1024/1024) size_mb from dba_segments where segment_name in (select TABLENAME from table_and_tablespace_details) ; set linesize 300 col index_owner for a30 col table_name for a30 column index_name format a30 column columns format a50 column index_type format a25 heading type column owner format a10 column tablespace_name format a15 select b.table_name,b.uniqueness,b.index_type,b.status,b.TABLESPACE_NAME,TO_CHAR(b.LAST_ANALYZED,'DD-MON-YYYYHH24:MI:SS'),a.* from (select index_name,index_owner, max(decode(column_position,1,column_name,null))|| max(decode(column_position,2,', '||column_name,null))|| max(decode(column_position,3,', '||column_name,null))|| max(decode(column_position,4,', '||column_name,null))|| max(decode(column_position,5,', '||column_name,null))|| max(decode(column_position,6,', '||column_name,null)) columns from dba_ind_columns where table_name in (select TABLENAME from table_and_tablespace_details) group by index_name,index_owner) a, dba_indexes b where a.index_name = b.index_name and a.index_owner=b.owner order by index_owner; DECLARE V_INDEXNAME VARCHAR2 (100); v_cnt NUMBER := 0; BEGIN FOR i IN (SELECT x.TABLENAME, x.TABLESPACENAME FROM TABLE_AND_TABLESPACE_DETAILS x) LOOP IF i.TABLENAME IS NOT NULL THEN -- DBMS_OUTPUT.PUT_LINE ('i.TABLENAME : ' || i.TABLENAME); -- DBMS_OUTPUT.PUT_LINE ('i.TABLESPACENAME : ' || i.TABLESPACENAME); BEGIN -- DBMS_OUTPUT.PUT_LINE ('i.TABLENAME Before : ' || i.TABLENAME); EXECUTE IMMEDIATE 'alter table ' || i.TABLENAME || ' move tablespace ' || i.TABLESPACENAME; COMMIT; DBMS_OUTPUT.PUT_LINE ('Rebuild completed for TABLENAME : ' || i.TABLENAME); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ( 'Error while rebuild index..' || i.TABLENAME); END; BEGIN FOR o_indexinsert IN (SELECT distinct index_name FROM dba_ind_columns WHERE table_name = UPPER (i.TABLENAME)) LOOP -- DBMS_OUTPUT.PUT_LINE ( -- 'o_indexinsert.index_name : ' -- || o_indexinsert.index_name); INSERT INTO TABLE_INDEXNAME (INDEXNAME) VALUES (o_indexinsert.index_name); END LOOP; END; END IF; END LOOP; COMMIT; BEGIN SELECT COUNT (*) INTO v_cnt FROM TABLE_INDEXNAME; IF v_cnt > 0 THEN FOR o_indexbuild IN (SELECT * FROM TABLE_INDEXNAME) LOOP --DBMS_OUTPUT.PUT_LINE ('Inside loop..' || o_indexbuild.INDEXNAME); DBMS_OUTPUT.PUT_LINE ('Index To Be Rebuild: ' || o_indexbuild.INDEXNAME); BEGIN -- DBMS_OUTPUT.PUT_LINE (o_indexbuild.INDEXNAME); EXECUTE IMMEDIATE 'alter index ' || o_indexbuild.INDEXNAME || ' rebuild online'; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ( 'Error while rebuild index..' || o_indexbuild.INDEXNAME); END; END LOOP; END IF; END; END; / set ver off set trimspool on set lines 199 col owner for a15 col segment_name for a30 col segment_type for a25 col tablespace_name for a22 set pagesize 150 col part_name for a30 compu sum of size_mb on report break on report skip 2 select owner, segment_name,partition_name part_name, buffer_pool, segment_type, tablespace_name, extents, round(bytes/1024/1024) size_mb from dba_segments where segment_name in (select TABLENAME from table_and_tablespace_details) ; set linesize 300 col index_owner for a30 col table_name for a30 column index_name format a30 column columns format a50 column index_type format a25 heading type column owner format a10 column tablespace_name format a15 select b.table_name,b.uniqueness,b.index_type,b.status,b.TABLESPACE_NAME,TO_CHAR(b.LAST_ANALYZED,'DD-MON-YYYYHH24:MI:SS'),a.* from (select index_name,index_owner, max(decode(column_position,1,column_name,null))|| max(decode(column_position,2,', '||column_name,null))|| max(decode(column_position,3,', '||column_name,null))|| max(decode(column_position,4,', '||column_name,null))|| max(decode(column_position,5,', '||column_name,null))|| max(decode(column_position,6,', '||column_name,null)) columns from dba_ind_columns where table_name in (select TABLENAME from table_and_tablespace_details) group by index_name,index_owner) a, dba_indexes b where a.index_name = b.index_name and a.index_owner=b.owner order by index_owner; insert into dba_constraints_after select CONSTRAINT_TYPE,STATUS,count(1) from dba_constraints where owner='VINOD' group by CONSTRAINT_TYPE,STATUS; commit; insert into dba_objects_after select owner,object_type , count(*),status from dba_objects where owner='VINOD' group by object_type,status,owner order by owner,object_type; commit; Prompt Constraints Informaiton (select * from dba_constraints_before minus select * from dba_constraints_after) union all ( select * from dba_constraints_after minus select * from dba_constraints_before ); Prompt Objects Informaiton (select * from dba_objects_before minus select * from dba_objects_after) union all ( select * from dba_objects_after minus select * from dba_objects_before ); |
Output :
Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin
Note: Please test scripts in Non Prod before trying in Production.
Loading...
