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" ( "TA...
