Defragmentation Implementation in ODI

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)