Defragmentation Implementation in ODI:
Execute below Code in database to create tables:
create table bi11g_dw.defragment_table as
select owner, table_name,sum(BLOCKS) S,'N' as flag
FROM ALL_TABLES
where owner in ('BI11G_DW', 'BI12C_BIA_ODIREPO','STG') and
(ROUND(((BLOCKS*16/1024)-(NUM_ROWS*AVG_ROW_LEN/1024/1024)),2)/ROUND(((BLOCKS*16/1024)),2))*100 >20 and
--table_name not like '%CFG%' and avg_row_len <> '0' group by owner, table_name order by s ASC;
------------------------------------------------------------------------------------------------------------
create table bi11g_dw.defragment_ind_table as select a.owner, a.table_name, b.index_name, 'N' as FLAG from bi11g_dw.defragment_table a, all_indexes b where a.table_name=b.table_name and a.owner=b.owner;
-----------------------------------------------------------------------------------------
CREATE TABLE "BI11G_DW"."DEFRAGMENT_TMP"
( "TABLE_NAME" VARCHAR2(50 BYTE)
)
------------------------------------------------------------------------------------------
ODI Proc code:
-------------------
step 1:
update bi11g_dw.defragment_table set flag='N';
Step2:
DECLARE
CUR1 SYS_REFCURSOR;
CUR2 SYS_REFCURSOR;
V_TABLENAME VARCHAR2(50);
V_INDEXNAME VARCHAR2(50);
V_OWNER VARCHAR2(50);
BEGIN
OPEN CUR1 FOR
select owner,table_name from bi11g_dw.defragment_table WHERE FLAG='N' order by s ASC;
LOOP
FETCH CUR1 INTO V_OWNER,V_TABLENAME;
EXIT WHEN CUR1%NOTFOUND;
EXECUTE IMMEDIATE'TRUNCATE TABLE bi11g_dw.defragment_tmp';
insert into bi11g_dw.defragment_tmp values (V_TABLENAME);
EXECUTE IMMEDIATE 'ALTER TABLE '||V_OWNER||'.'||V_TABLENAME||' MOVE';
OPEN CUR2 FOR SELECT OWNER,TABLE_NAME, INDEX_NAME FROM bi11g_dw.defragment_ind_table WHERE TABLE_NAME = V_TABLENAME;
LOOP
FETCH CUR2 INTO V_OWNER,V_TABLENAME,V_INDEXNAME;
EXIT WHEN CUR2%NOTFOUND;
EXECUTE IMMEDIATE'TRUNCATE TABLE bi11g_dw.defragment_tmp';
insert into bi11g_dw.defragment_tmp values (V_TABLENAME);
EXECUTE IMMEDIATE 'ALTER INDEX '||V_OWNER||'.'||V_INDEXNAME||' REBUILD ONLINE';
UPDATE bi11g_dw.defragment_ind_table SET FLAG='Y' WHERE TABLE_NAME=V_TABLENAME AND INDEX_NAME=V_INDEXNAME;
commit;
END LOOP;
dbms_stats.gather_table_stats(ownname => V_OWNER, tabname =>V_TABLENAME, estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
UPDATE bi11g_dw.defragment_table SET FLAG='Y' WHERE TABLE_NAME=V_TABLENAME;
COMMIT;
END LOOP;
END;
ODI Proc2:
---------
update Bi11G_DW.defragment_table set flag='E' where table_name in (select * from Bi11G_DW.defragment_tmp)
