CREATE OR REPLACE PACKAGE Table_Maint AUTHID CURRENT_USER IS /******************************************************************** * Procedures to manage tables and indexes * * Authors: Jaime Gracia * * Created : 10/16/2002 * Last Updated: 11/22/2002 * Updated By: Jaime Gracia * *********************************************************************/ -- Public type declarations SUBTYPE t_column IS all_tab_columns.Column_Name%TYPE; SUBTYPE t_degree IS user_indexes.DEGREE%TYPE; SUBTYPE t_fullTabName IS VARCHAR2(61); SUBTYPE t_id IS NUMBER(9, 0); SUBTYPE t_index IS User_Indexes.Index_Name%TYPE; SUBTYPE t_job_name IS user_jobs.what%TYPE; SUBTYPE t_owner IS all_tables.owner%TYPE; SUBTYPE t_retCode IS PLS_INTEGER; SUBTYPE t_sql IS VARCHAR2(1000); SUBTYPE t_table IS all_tables.table_name%TYPE; SUBTYPE t_tablespace IS all_tables.tablespace_name%TYPE; SUBTYPE t_unique IS user_indexes.uniqueness%TYPE; TYPE tbl_indexes IS TABLE OF t_sql INDEX BY BINARY_INTEGER; TYPE idx_rectype IS RECORD( index_owner t_owner, index_name t_index, uniqueness t_unique, table_owner t_owner, table_name t_table, TABLESPACE t_tablespace, fullTab t_fullTabName, fullIdx t_fullTabName, initExt PLS_INTEGER, nextExt PLS_INTEGER, DEGREE t_degree, INSTANCES t_degree ); -- Public constant declarations c_default_extents CONSTANT PLS_INTEGER := 5; c_table CONSTANT CHAR(1) := 'T'; c_index CONSTANT CHAR(1) := 'I'; -- Public variable declarations CURSOR tab_alloc_cur(p_schema IN t_owner := NULL, p_extents IN PLS_INTEGER := c_default_extents) IS SELECT di.owner, di.table_name, di.tablespace_name, ds.BYTES / 1024 Size_KB, ds.extents Allocated_Ext, di.max_extents Max_Ext, di.initial_extent / 1024 Init_Ext_KB, di.next_extent / 1024 Next_Ext_KB, di.pct_increase Percent_Inc, di.pct_free Percent_Free, di.blocks, di.partitioned FROM SYS.dba_segments ds, SYS.dba_tables di, SYS.dba_tablespaces dt WHERE ds.tablespace_name = di.tablespace_name AND di.tablespace_name = dt.tablespace_name AND dt.extent_management <> 'LOCAL' AND ds.owner = di.owner AND ds.segment_name = di.table_name AND ds.extents > p_extents AND ds.owner NOT IN('SYS', 'SYSTEM') AND di.partitioned = 'NO' AND di.blocks > 0 -- block size = 0 if table not analyzed AND di.owner LIKE DECODE(p_schema, NULL, '%', UPPER(p_schema)) ORDER BY di.owner, di.table_name; CURSOR idx_alloc_cur(p_schema IN t_owner := NULL, p_extents IN PLS_INTEGER := c_default_extents) IS SELECT di.owner index_owner, di.index_name, di.index_type, di.uniqueness, ds.tablespace_name, di.table_owner, di.table_name, ds.BYTES / 1024 Size_KB, ds.extents Allocated_Ext, di.max_extents Max_Ext, di.initial_extent / 1024 Init_Ext_KB, di.next_extent / 1024 Next_Ext_KB, di.pct_increase Percent_Inc, di.pct_free Percent_Free, di.DEGREE, di.INSTANCES FROM SYS.dba_segments ds, SYS.dba_indexes di, SYS.dba_tablespaces dt WHERE ds.tablespace_name = di.tablespace_name AND di.tablespace_name = dt.tablespace_name AND dt.extent_management <> 'LOCAL' AND ds.owner = di.owner AND ds.segment_name = di.index_name AND ds.extents > p_extents AND ds.owner NOT IN('SYS', 'SYSTEM') AND ds.owner LIKE DECODE(p_schema, NULL, '%', UPPER(p_schema)) AND di.index_name NOT LIKE 'SYS\_%' ESCAPE '\' ORDER BY di.owner, di.index_name; -- Public function and procedure declarations -- Analyze table: p_table = owner.table_name PROCEDURE Analyze_Table(p_table IN t_fullTabName); -- determine whether table has excluded columns which prevents table -- from being rebuilt using the move storage method FUNCTION ContainsExcludedColumn(p_owner IN t_owner, p_table IN t_table) RETURN BOOLEAN; -- Rounds up value to nearest base 2 integer -- values in kilobytes FUNCTION GetNearestValue(p_extSize IN PLS_INTEGER := 32) RETURN PLS_INTEGER DETERMINISTIC; -- Retrieve list of indexes on a given table FUNCTION GetTableIndexes(p_owner IN t_owner, p_table IN t_table) RETURN tbl_indexes; -- Determine is failed is locked by another user or session FUNCTION IsTableLocked(p_owner IN t_owner, p_table IN t_table) RETURN BOOLEAN; -- Calculates a new initial extent size for fragemented table FUNCTION CalcInitExtent(p_blocks IN PLS_INTEGER := NULL, p_initExt IN PLS_INTEGER := 32) RETURN PLS_INTEGER; -- Finds and rebuilds any indexes in UNUSABLE state PROCEDURE RebuildUnusableIndexes; -- Update next, max, and pct increase table storage parameters -- parmeters in Kilobytes PROCEDURE UpdateTableStorageParameters( p_owner IN t_owner, p_table IN t_table, p_next IN PLS_INTEGER := 32, p_maxExt IN PLS_INTEGER := 4096, p_pctInc IN PLS_INTEGER := 0); -- Main program to rebuild fragmented tables PROCEDURE RebuildTables( p_schema IN t_owner := NULL, p_extents IN PLS_INTEGER := c_default_extents, p_showOnly IN BOOLEAN := TRUE); -- Main program to rebuild fragmented indexes PROCEDURE RebuildIndexes( p_schema IN t_owner := NULL, p_extents IN PLS_INTEGER := c_default_extents, p_showOnly IN BOOLEAN := TRUE); -- Reclaim used space above the high-water mark for tables or indexes PROCEDURE DeallocateUnusedSpace( p_schema IN t_owner := NULL, p_tablespace IN t_tablespace := NULL, p_type IN VARCHAR2 := c_index, p_showOnly IN BOOLEAN := TRUE); END Table_Maint; / CREATE OR REPLACE PACKAGE BODY Table_Maint IS SUBTYPE t_errMsg IS VARCHAR2(1000); SUBTYPE t_idxType IS VARCHAR2(6); SUBTYPE t_msg IS VARCHAR2(1000); SUBTYPE t_packproc IS VARCHAR2(100); SUBTYPE t_procfunc IS all_objects.object_name%TYPE; SUBTYPE t_segment IS User_Segments.Segment_Name%TYPE; -- Private constant declarations c_colon CONSTANT CHAR(2) := ': '; c_comma CONSTANT CHAR(1) := ','; c_package CONSTANT t_procfunc := 'Table_Maint'; c_period CONSTANT VARCHAR2(1) := '.'; c_space CONSTANT VARCHAR2(1) := CHR(32); c_blksize CONSTANT NUMBER(4) := 8192; -- Private variable declarations g_showOnly BOOLEAN := TRUE; e_general EXCEPTION; cannot_alter_lob_index EXCEPTION; PRAGMA EXCEPTION_INIT(cannot_alter_lob_index, -22864); -- Function and procedure implementations /***************************************************************************/ PROCEDURE Log_Error( p_msg IN VARCHAR2, -- message to be logged p_package IN VARCHAR2 := c_Package, -- application name p_type IN VARCHAR2 := 'E', -- e=error, w=warning, i=info p_proc IN VARCHAR2 := NULL, -- procedure name p_action IN VARCHAR2 := NULL, -- to identify procedure part or sql command inside the module p_searchkey_name IN VARCHAR2 := NULL, -- for example test_id p_searchkey_value IN VARCHAR2 := NULL, -- for example 12345 (this column is indexed) p_severity IN PLS_INTEGER := 4, -- 0=low 9=high , p_dump_env_flag IN VARCHAR2 := 'F' -- If 'T' then env saved, e.g. all sessions saved to log_v$session ) IS v_obj t_procfunc := 'Log_Error'; v_errMsg t_msg := NULL; BEGIN DG_LOG_API.LOG(p_type, p_msg, p_package, p_proc, p_action, p_searchkey_name, p_searchkey_value, p_severity, p_dump_env_flag); EXCEPTION WHEN OTHERS THEN v_errMsg := v_obj || c_colon || SQLERRM; p.l(v_errMsg); END Log_Error; /***************************************************************************/ PROCEDURE ShowElapsedTime(p_proc IN t_procfunc) IS v_obj t_procfunc := 'ShowElapsedTime'; v_errMsg t_msg := NULL; BEGIN PLVtmr.show_elapsed(Prefix_In => c_package || c_period || p_proc || c_colon); EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 4); END ShowElapsedTime; /***************************************************************************/ PROCEDURE Analyze_Table(p_table IN t_fullTabName) IS v_sql t_sql := NULL; v_obj t_procfunc := 'GetNearestValue'; v_errMsg t_msg := NULL; BEGIN v_sql := 'ANALYZE TABLE ' || p_table || ' ESTIMATE STATISTICS SAMPLE 10 PERCENT'; Log_Error(p_msg => v_sql, p_proc => v_obj, p_type => 'I', p_severity => 0); EXECUTE IMMEDIATE v_sql; EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); END Analyze_Table; /***************************************************************************/ PROCEDURE SubmitJob(p_job_name IN t_job_name) IS ------------------------------------------- v_job_name t_job_name := p_job_name; v_msg t_errMsg := NULL; v_obj t_procfunc := 'SubmitJob'; v_errMsg t_errMsg; v_job BINARY_INTEGER := NULL; /* Submit job in the following format: -- 'owner.procedureName;' -- Semicolon must be appended -- owner is optional if synonyms, grants in place */ BEGIN IF INSTR(v_job_name, ';') = 0 THEN v_job_name := v_job_name || ';'; END IF; v_msg := 'Job: ' || v_job_name; p.l(v_msg); Log_Error(p_msg => v_msg, p_proc => v_obj, p_type => 'I', p_severity => 0); DBMS_JOB.SUBMIT(JOB => v_job, WHAT => v_job_name); COMMIT; p.l('Job Number: ' || TO_CHAR(v_job)); EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); END SubmitJob; /***************************************************************************/ FUNCTION GetNearestValue(p_extSize IN PLS_INTEGER := 32) RETURN PLS_INTEGER IS v_val NUMBER(12, 4) := 0; v_retval PLS_INTEGER := 32; v_pow PLS_INTEGER := 0; j PLS_INTEGER := 1; v_obj t_procfunc := 'GetNearestValue'; v_errMsg t_msg := NULL; BEGIN j := LOG(2, p_extSize); FOR i IN 0 .. j LOOP v_pow := POWER(2, i); v_val := p_extSize / v_pow; v_retval := v_pow; IF CEIL(v_val) = 1 THEN EXIT; END IF; END LOOP; RETURN(v_retval); EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); p.l('v_pow: ' || v_pow || ' v_val: ' || v_val); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); RETURN(128); END GetNearestValue; /***************************************************************************/ FUNCTION GetLargestTSFreeExtent(p_tablespace IN t_tablespace) RETURN NUMBER IS /* Returns largest free extent for tablespace. If initial or next extent for an object exceeds this extent size, creation of object will fail with error: ORA-01658: unable to create INITIAL extent for segment in tablespace */ v_retval NUMBER(20) := NULL; v_obj t_procfunc := 'GetLargestTSFreeExtent'; v_errMsg t_msg := NULL; CURSOR ts_cur(p_ts IN t_tablespace) IS SELECT tablespace_name, MAX(BYTES) largest FROM dba_free_space WHERE tablespace_name = UPPER(p_tablespace) GROUP BY tablespace_name; ts_rec ts_cur%ROWTYPE; BEGIN OPEN ts_cur(p_tablespace); FETCH ts_cur INTO ts_rec; IF ts_cur%FOUND THEN v_retval := ts_rec.largest; ELSE v_retval := 0; END IF; CLOSE ts_cur; v_errMsg := 'Largest free extent size in ts ' || p_tablespace || ': ' || TO_CHAR(ROUND(v_retval / 1024, 0)) || 'K'; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_type => 'I', p_proc => v_obj, p_severity => 0); RETURN(v_retval); EXCEPTION WHEN OTHERS THEN IF ts_cur%ISOPEN THEN CLOSE ts_cur; END IF; v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); RETURN(0); END GetLargestTSFreeExtent; /***************************************************************************/ FUNCTION GetTableIndexes(p_owner IN t_owner, p_table IN t_table) RETURN tbl_indexes IS vt_indx tbl_indexes; v_obj t_procfunc := 'GetTableIndexes'; v_errMsg t_msg := NULL; CURSOR indx_cur(p_schema IN t_owner, p_tab IN t_table) IS SELECT 'ALTER INDEX ' || owner || c_period || index_name || ' REBUILD PARALLEL(DEGREE 4 INSTANCES 1) NOLOGGING NOREVERSE' ALTER_SQL FROM SYS.dba_indexes WHERE index_name NOT LIKE 'SYS_%' AND table_owner = p_owner AND table_name = p_table; BEGIN FOR indx_rec IN indx_cur(p_owner, p_table) LOOP vt_indx(indx_cur%ROWCOUNT) := indx_rec.alter_sql; END LOOP; RETURN(vt_indx); EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); RETURN(vt_indx); END GetTableIndexes; /***************************************************************************/ FUNCTION ContainsExcludedColumn(p_owner IN t_owner, p_table IN t_table) RETURN BOOLEAN IS v_retval t_retCode := NULL; v_retBool BOOLEAN := TRUE; v_obj t_procfunc := 'ContainsExcludedColumn'; v_errMsg t_msg := NULL; CURSOR col_cur(p_schema IN t_owner, p_tab IN t_table) IS SELECT 1 FROM SYS.dba_tab_columns c WHERE c.owner = p_schema AND c.table_name = p_tab AND c.data_type IN('LONG', 'LONG RAW', 'RAW', 'MLSLABEL'); BEGIN OPEN col_cur(UPPER(p_owner), UPPER(p_table)); FETCH col_cur INTO v_retval; IF col_cur%NOTFOUND THEN v_retBool := FALSE; ELSE v_retBool := TRUE; END IF; CLOSE col_cur; RETURN(v_retBool); EXCEPTION WHEN OTHERS THEN IF col_cur%ISOPEN THEN CLOSE col_cur; END IF; v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); RETURN(TRUE); END ContainsExcludedColumn; /***************************************************************************/ FUNCTION MoveStorage(p_table IN t_fullTabName, p_initExt IN PLS_INTEGER) RETURN t_retcode IS v_sql t_sql := NULL; v_retval t_retcode := 0; v_obj t_procfunc := 'MoveStorage'; v_errMsg t_msg := NULL; BEGIN v_sql := 'ALTER TABLE ' || p_table || ' MOVE STORAGE(INITIAL ' || TO_CHAR(p_initExt) || 'K)'; Log_Error(p_msg => v_sql, p_proc => v_obj, p_type => 'I', p_severity => 0); EXECUTE IMMEDIATE v_sql; RETURN(v_retval); EXCEPTION WHEN OTHERS THEN v_retval := -1; v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); RETURN(v_retval); END MoveStorage; /***************************************************************************/ FUNCTION IsUniqueConstraint(p_index_owner IN t_owner, p_index IN t_index) RETURN BOOLEAN IS v_cons SYS.dba_constraints.constraint_name%TYPE := NULL; v_retval BOOLEAN := FALSE; v_obj t_procfunc := 'IsUniqueConstraint'; v_errMsg t_msg := NULL; CURSOR cons_cur(p_owner IN t_owner, p_index IN t_index) IS SELECT d.constraint_type FROM SYS.dba_constraints d WHERE d.owner = p_owner AND d.constraint_name = p_index; BEGIN OPEN cons_cur(p_index_owner, p_index); FETCH cons_cur INTO v_cons; CLOSE cons_cur; IF v_cons IN('P', 'U') THEN v_retval := TRUE; ELSE v_retval := FALSE; END IF; RETURN(v_retval); EXCEPTION WHEN OTHERS THEN IF cons_cur%ISOPEN THEN CLOSE cons_cur; END IF; v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); RETURN(FALSE); END IsUniqueConstraint; /***************************************************************************/ FUNCTION IsTablespace(p_tablespace IN t_tablespace) RETURN BOOLEAN IS v_retval BOOLEAN := FALSE; v_ts PLS_INTEGER := NULL; v_obj t_procfunc := 'IsTablespace'; v_errMsg t_msg := NULL; CURSOR ts_cur(p_ts IN t_tablespace) IS SELECT 1 FROM sys.dba_tablespaces t WHERE t.tablespace_name = UPPER(p_ts); BEGIN OPEN ts_cur(p_tablespace); FETCH ts_cur INTO v_ts; v_retval := ts_cur%FOUND; CLOSE ts_cur; RETURN (v_retval); EXCEPTION WHEN OTHERS THEN IF ts_cur%ISOPEN THEN CLOSE ts_cur; END IF; v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 7); RETURN(FALSE); END IsTablespace; /***************************************************************************/ FUNCTION IsUser(p_user IN t_owner) RETURN BOOLEAN IS v_retval BOOLEAN := FALSE; v_user PLS_INTEGER := NULL; v_obj t_procfunc := 'IsUser'; v_errMsg t_msg := NULL; CURSOR user_cur(p_user IN t_owner) IS SELECT 1 FROM sys.dba_users t WHERE t.username = UPPER(p_user); BEGIN OPEN user_cur(p_user); FETCH user_cur INTO v_user; v_retval := user_cur%FOUND; CLOSE user_cur; RETURN (v_retval); EXCEPTION WHEN OTHERS THEN IF user_cur%ISOPEN THEN CLOSE user_cur; END IF; v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 7); RETURN(FALSE); END IsUser; /***************************************************************************/ FUNCTION IsTableLocked(p_owner IN t_owner, p_table IN t_table) RETURN BOOLEAN IS v_retval BOOLEAN := TRUE; v_obj t_procfunc := 'IsTableLocked'; v_errMsg t_msg := NULL; CURSOR lock_cur(p_schema IN t_owner, p_tab IN t_table) IS SELECT o.owner, o.object_name, l.oracle_username, l.os_user_name FROM SYS.v_$locked_object l, SYS.dba_objects o WHERE l.object_id = o.object_id AND o.owner = p_schema AND o.object_name = p_table; lock_rec lock_cur%ROWTYPE; BEGIN OPEN lock_cur(p_owner, p_table); FETCH lock_cur INTO lock_rec; IF lock_cur%NOTFOUND THEN v_retval := FALSE; ELSE v_retval := TRUE; v_errMsg := p_owner || '.' || INITCAP(p_table) || ' locked by ' || lock_rec.oracle_username || ' (' || lock_rec.os_user_name || ')--skipped...'; p.l(v_errMsg); END IF; RETURN(v_retval); EXCEPTION WHEN OTHERS THEN IF lock_cur%ISOPEN THEN CLOSE lock_cur; END IF; v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); RETURN(TRUE); END IsTableLocked; /***************************************************************************/ FUNCTION CalcInitExtent(p_blocks IN PLS_INTEGER := NULL, p_initExt IN PLS_INTEGER := 32) RETURN PLS_INTEGER IS v_tableSize NUMBER(12, 0) := 0; v_tableSizeNew NUMBER(12, 0) := 0; c_PercentIncrease CONSTANT NUMBER(5, 1) := 1.25; v_obj t_procfunc := 'CalcInitExtent'; v_errMsg t_msg := NULL; BEGIN v_tableSize :=(p_blocks * c_blksize); v_tableSizeNew := (v_tableSize * c_PercentIncrease) / 100; v_tableSizeNew := GetNearestValue(p_Extsize => v_tableSizeNew); RETURN(v_tableSizeNew); EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); RETURN(128); END CalcInitExtent; /***************************************************************************/ FUNCTION CalcNextExtent(p_initExt IN PLS_INTEGER := 32) RETURN PLS_INTEGER IS v_nextExt NUMBER(12, 0) := p_initExt; c_Percent CONSTANT NUMBER(5, 1) := .10; BEGIN v_nextExt :=(p_initExt * c_percent); v_nextExt := GetNearestValue(p_Extsize => v_nextExt); RETURN(v_nextExt); END CalcNextExtent; /***************************************************************************/ PROCEDURE RebuildUnusableIndexes IS v_sql t_sql := NULL; v_index t_fullTabName := NULL; v_obj t_procfunc := 'RebuildTableIndexes'; v_errMsg t_msg := NULL; CURSOR rbi_cur IS SELECT i.owner, i.index_name FROM SYS.dba_indexes i WHERE status = 'UNUSABLE' ORDER BY i.owner, i.index_name; BEGIN FOR rbi_rec IN rbi_cur LOOP BEGIN v_index := rbi_rec.owner || c_period || v_index; v_sql := 'ALTER INDEX ' || v_index || ' REBUILD PARALLEL(DEGREE 4 INSTANCES 1) NOLOGGING NOREVERSE'; Log_Error(p_msg => v_sql, p_proc => v_obj, p_type => 'I', p_severity => 0); p.l('Rebuilding Index: ' || v_sql); EXECUTE IMMEDIATE v_sql; EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); END; END LOOP; EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); END RebuildUnusableIndexes; /***************************************************************************/ PROCEDURE RebuildTableIndexes(pt_indexList IN tbl_indexes) IS v_row PLS_INTEGER := 0; v_sql t_sql := NULL; v_obj t_procfunc := 'RebuildTableIndexes'; v_errMsg t_msg := NULL; BEGIN v_row := 0; v_row := pt_indexList.FIRST; LOOP EXIT WHEN v_row IS NULL; p.l(pt_indexList(v_row)); <> BEGIN v_sql := pt_indexList(v_row); Log_Error(p_msg => v_sql, p_proc => v_obj, p_type => 'I', p_severity => 0); EXECUTE IMMEDIATE v_sql; EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || 'Index Rebuild Failure: ' || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 8); END idx_blk; v_row := pt_indexList.NEXT(v_row); END LOOP; EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); END RebuildTableIndexes; /***************************************************************************/ FUNCTION BuildIndexStatement(p_idxrec IN idx_rectype, p_col IN t_sql, p_idxType IN t_idxType) RETURN t_sql IS v_sql t_sql := NULL; v_parallel VARCHAR2(100) := NULL; v_obj t_procfunc := 'BuildIndexStatement'; v_errMsg t_msg := NULL; BEGIN IF p_idxrec.DEGREE > 1 OR p_idxrec.INSTANCES > 1 THEN v_parallel := 'PARALLEL(DEGREE ' || p_idxrec.DEGREE || ' INSTANCES ' || p_idxrec.INSTANCES || ')'; ELSE v_parallel := NULL; END IF; IF p_idxType = 'NORMAL' THEN v_sql := 'CREATE INDEX ' || p_idxrec.fullIdx || CHR(10) || ' ON ' || p_idxrec.fullTab || p_col || CHR(10) || ' PCTFREE 10 ' || CHR(10) || ' INITRANS 2 ' || CHR(10) || ' MAXTRANS 255 ' || CHR(10) || ' TABLESPACE ' || p_idxrec.TABLESPACE || CHR(10) || ' STORAGE(INITIAL ' || TO_CHAR(p_idxrec.initExt) || 'K' || CHR(10) || ' NEXT ' || TO_CHAR(p_idxrec.nextExt) || 'K' || CHR(10) || ' MINEXTENTS 1' || CHR(10) || ' MAXEXTENTS 4096' || CHR(10) || ' PCTINCREASE 0' || CHR(10) || ' FREELISTS 1' || CHR(10) || ' FREELIST GROUPS 1' || CHR(10) || ' BUFFER_POOL DEFAULT)' || CHR(10) || ' LOGGING ' || CHR(10) || ' NOCOMPRESS' || CHR(10) || v_parallel; p.l(v_sql); Log_Error(p_msg => v_sql, p_type => 'I', p_proc => v_obj, p_severity => 0); ELSIF p_idxType = 'UNIQUE' THEN v_sql := 'ALTER INDEX ' || p_idxrec.fullIdx || ' REBUILD ' || v_parallel || CHR(10) || ' LOGGING' || CHR(10) || ' PCTFREE 10 ' || CHR(10) || ' INITRANS 2 ' || CHR(10) || ' MAXTRANS 255 ' || CHR(10) || ' STORAGE(INITIAL ' || TO_CHAR(p_idxrec.initExt) || 'K' || CHR(10) || ' NEXT ' || TO_CHAR(p_idxrec.nextExt) || 'K' || CHR(10) || ' MINEXTENTS 1' || CHR(10) || ' MAXEXTENTS 4096' || CHR(10) || ' PCTINCREASE 0' || CHR(10) || ' FREELISTS 1' || CHR(10) || ' FREELIST GROUPS 1' || CHR(10) || ' BUFFER_POOL DEFAULT)' || CHR(10) || ' TABLESPACE ' || p_idxrec.TABLESPACE; p.l(v_sql); Log_Error(p_msg => v_sql, p_type => 'I', p_proc => v_obj, p_severity => 0); ELSE v_sql := NULL; v_errMsg := 'Could not determine index type. Failed to get index definition.'; -- cannot tell if index is constraint or not Log_Error(p_msg => v_errMsg, p_type => 'E', p_proc => v_obj, p_severity => 8); END IF; RETURN(v_sql); EXCEPTION WHEN OTHERS THEN v_sql := NULL; v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); RETURN(v_sql); END BuildIndexStatement; /***************************************************************************/ FUNCTION DropIndex(p_index IN t_fullTabName) RETURN t_retCode IS v_retval t_retcode := -1; v_drop t_sql := NULL; v_obj t_procfunc := 'DropIndex'; v_errMsg t_msg := NULL; BEGIN v_drop := 'DROP INDEX ' || p_index; p.l(v_drop); Log_Error(p_msg => v_drop, p_type => 'I', p_proc => v_obj, p_severity => 0); IF NOT g_showOnly THEN EXECUTE IMMEDIATE v_drop; END IF; v_retval := 0; RETURN(v_retval); EXCEPTION WHEN OTHERS THEN v_retval := -1; v_errMsg := 'Drop Index Error: ' || c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); RETURN(v_retval); END DropIndex; /***************************************************************************/ PROCEDURE RecreateIndex(p_idxrec IN idx_rectype) IS v_retval t_retcode := NULL; v_create t_sql := NULL; v_idxType t_idxType := 'NORMAL'; -- values: NORMAL, UNIQUE v_col t_sql := '('; v_obj t_procfunc := 'RecreateIndex'; v_errMsg t_msg := NULL; CURSOR col_cur(p_owner IN t_owner, p_index IN t_index) IS SELECT c.index_owner, c.index_name, c.table_owner, c.table_name, c.column_name, c.column_position FROM sys.dba_ind_columns c WHERE c.index_owner = p_owner AND c.index_name = p_index ORDER BY c.column_position ASC; BEGIN -- build list of columns to include in index FOR col_rec IN col_cur(p_idxrec.index_owner, p_idxrec.index_name) LOOP v_col := v_col || col_rec.column_name || c_comma; END LOOP; v_col := RTRIM(v_col, c_comma); v_col := CONCAT(v_col, ')'); p.l(v_col); Log_Error(p_msg => v_col, p_type => 'I', p_proc => v_obj, p_severity => 0); IF NOT IsUniqueConstraint(p_idxrec.index_owner, p_idxrec.index_name) THEN -- drop index and recreate non-unique index <> BEGIN v_idxType := 'NORMAL'; v_create := BuildIndexStatement(p_idxrec, v_col, v_idxType); IF v_create IS NOT NULL THEN v_retval := DropIndex(p_idxrec.fullIdx); IF v_retval = 0 -- Index dropped successfully THEN IF NOT g_showOnly THEN <> BEGIN EXECUTE IMMEDIATE v_create; p.l(CONCAT(p_idxrec.fullIdx, ' created.')); EXCEPTION WHEN OTHERS THEN v_errMsg := 'Index Create:' || c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 8); END recreate; END IF; ELSE -- Failed to drop index v_errMsg := p_idxrec.index_owner || c_period || p_idxrec.index_name || ' not dropped.'; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 8); END IF; ELSE -- string build failed v_errMsg := p_idxrec.index_owner || c_period || p_idxrec.index_name || ' create statement build failed.'; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 8); END IF; EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); END drop_recreate_blk; ELSE -- rebuild pk or uk type indexes <> BEGIN v_idxType := 'UNIQUE'; v_create := BuildIndexStatement(p_idxrec, v_col, v_idxType); IF v_create IS NOT NULL THEN IF NOT g_showOnly THEN <> BEGIN EXECUTE IMMEDIATE v_create; p.l(CONCAT(p_idxrec.fullIdx, ' created.')); EXCEPTION WHEN OTHERS THEN v_errMsg := 'Index Create:' || c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 8); END recreate; END IF; ELSE -- string build failed v_errMsg := p_idxrec.index_owner || c_period || p_idxrec.index_name || ' create statement build failed.'; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 8); END IF; EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); END recreate_blk; END IF; EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); END RecreateIndex; /***************************************************************************/ PROCEDURE UpdateTableStorageParameters( p_owner IN t_owner, p_table IN t_table, p_next IN PLS_INTEGER := 32, p_maxExt IN PLS_INTEGER := 4096, p_pctInc IN PLS_INTEGER := 0) IS v_next VARCHAR2(9) := TO_CHAR(p_next); v_maxExt VARCHAR2(9) := TO_CHAR(p_maxExt); v_pctInc VARCHAR2(9) := TO_CHAR(p_pctInc); v_fullTab t_fullTabName := p_owner || c_period || p_table; v_sql t_sql := NULL; v_obj t_procfunc := 'UpdateTableStorageParameters'; v_errMsg t_msg := NULL; BEGIN v_sql := 'ALTER TABLE ' || v_fullTab || ' STORAGE(NEXT ' || v_next || 'K ' || ' MAXEXTENTS ' || v_maxExt || ' PCTINCREASE ' || v_pctInc || ')'; Log_Error(p_msg => v_sql, p_proc => v_obj, p_type => 'I', p_severity => 0); EXECUTE IMMEDIATE v_sql; EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_type => 'E', p_severity => 0); END UpdateTableStorageParameters; /***************************************************************************/ PROCEDURE RebuildTables(p_schema IN t_owner := NULL, p_extents IN PLS_INTEGER := 5, p_showOnly IN BOOLEAN := TRUE) IS ---------------------------------------- v_exts PLS_INTEGER := p_extents; v_row PLS_INTEGER := 0; v_owner t_owner := NULL; v_table t_table := NULL; v_ts t_tablespace := NULL; v_fullTab t_fullTabName := NULL; v_sql t_sql := NULL; v_initExt PLS_INTEGER := NULL; v_nextExt PLS_INTEGER := NULL; v_largest_free_extent NUMBER(20) := NULL; vt_indx tbl_indexes; v_retval t_retCode := NULL; v_obj t_procfunc := 'RebuildTables'; v_msg t_msg := NULL; v_errMsg t_msg := NULL; -------------------- Main Program ---------------------------------- BEGIN -- Set up Environment start ---------------------------------------- p.turn_on; p.set_linelen(len => 140); p.set_linesep(NULL); PLVtmr.capture(CONCAT('Begin ', v_obj)); g_showOnly := p_showOnly; v_msg := c_package || c_period || v_obj || c_colon || 'Start processing...showOnly set to ' || PLVbool.stg(p_showOnly); Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); p.l(v_msg); p.l('-----------------------------------------------------------------------'); EXECUTE IMMEDIATE('ALTER SESSION SET SORT_AREA_SIZE=20480000 SORT_AREA_RETAINED_SIZE=512000'); EXECUTE IMMEDIATE('ALTER SESSION FORCE PARALLEL QUERY'); -- Set up Environment end ---------------------------------------- IF v_exts < 0 THEN v_exts := 5; END IF; -- Get list of all tables with > v_exts allocated for rebuilding <> FOR tab_alloc_rec IN tab_alloc_cur(UPPER(p_schema), v_exts) LOOP v_owner := tab_alloc_rec.owner; v_table := tab_alloc_rec.table_name; v_ts := tab_alloc_rec.tablespace_name; v_fullTab := v_owner || c_period || v_table; p.l(v_fullTab); Log_Error(p_msg => 'Processing Table ' || v_fullTab, p_proc => v_obj, p_type => 'I', p_severity => 0); BEGIN -- main_blk -- do not rebuild any tables already locked by another session IF NOT IsTableLocked(v_owner, v_table) THEN -- do not rebuild any tables with LONGs or RAWs IF NOT ContainsExcludedColumn(p_Owner => v_owner, p_Table => v_table) THEN -- after rebuilding tables, indexes are in UNUSABLE state -- and must be rebuild. Get indexes to rebuild and store vt_indx := GetTableIndexes(v_owner, v_table); IF vt_indx.COUNT > 0 THEN v_msg := CONCAT(vt_indx.COUNT, ' indexes will be rebuilt.'); p.l(v_msg); Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); ELSE v_msg := CONCAT(vt_indx.COUNT, '...No indexes will be rebuilt.'); p.l(v_msg); Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); END IF; -- update table storage paramenters -- get largest free extent for tablespace v_largest_free_extent := GetLargestTSFreeExtent(p_tablespace => v_ts); IF v_largest_free_extent > 0 THEN v_largest_free_extent := ROUND(v_largest_free_extent / 1024, 0); -- convert TO KB ELSE v_errMsg := 'Largest free extent for ts ' || v_ts || ' not returned.'; Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 8); v_largest_free_extent := 1024000; END IF; v_initExt := CalcInitExtent(tab_alloc_rec.blocks, tab_alloc_rec.Init_Ext_KB); -- if v_initExtent > largest free extent in tablespace, then adjust size WHILE v_initExt > v_largest_free_extent LOOP EXIT WHEN v_initExt < v_largest_free_extent; -- decrement initial extent until smaller than -- largest free extent in tablespace v_initExt := CalcInitExtent(tab_alloc_rec.blocks, v_initExt * .9); END LOOP; v_nextExt := CalcNextExtent(v_initExt); -- if v_nextExtent > largest free extent in tablespace, then adjust size WHILE v_nextExt > v_largest_free_extent LOOP EXIT WHEN v_nextExt < v_largest_free_extent; -- decrement next extent until smaller than -- largest free extent in tablespace v_initExt := CalcNextExtent(v_initExt * .9); END LOOP; IF NOT p_showOnly THEN UpdateTableStorageParameters(p_Owner => v_owner, p_Table => v_table, p_Next => v_nextExt); END IF; v_msg := 'Old initExt: ' || tab_alloc_rec.Init_Ext_KB || ' Old nextExt: ' || tab_alloc_rec.Next_Ext_KB; Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); p.l(v_msg); v_msg := 'New initExt: ' || v_initExt || ' New nextExt: ' || v_nextExt; Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); p.l(v_msg); -- rezize initial extent to new, larger size to eliminate table fragmentation IF NOT p_showOnly THEN v_retval := MoveStorage(v_fullTab, v_initExt); IF v_retval <> 0 THEN p.l('Error rebuilding table ' || v_fullTab); END IF; END IF; -- rebuild indexes after resizing table even if process fails, in case -- any indexes left in UNUSABLE state IF vt_indx.COUNT > 0 THEN IF NOT p_showOnly THEN RebuildTableIndexes(vt_indx); -- analyze table to verify if indexes ok. Will get error is indexes still -- in UNUSABLE state SubmitJob('Table_Maint.Analyze_Table(''' || v_fullTab || ''');'); END IF; END IF; ELSE v_msg := v_fullTab || ' skipped; contains excluded datatype(s).'; Log_Error(p_msg => v_msg, p_type => 'W', p_proc => v_obj, p_severity => 3); p.l(v_msg); END IF; -- containsExcludedColumn END IF; -- isTableLocked END main_blk; END LOOP main_loop; -- Clean up: Check for any indexes still in UNUSABLE state and rebuild, -- then recompile any invalidated objects RebuildUnusableIndexes; SubmitJob('Recompile_Objects;'); ---------------------------- ShowElapsedTime(v_obj); COMMIT; v_msg := c_package || c_period || v_obj || ' completed successfully.'; Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); COMMIT; END RebuildTables; /***************************************************************************/ PROCEDURE RebuildIndexes(p_schema IN t_owner := NULL, p_extents IN PLS_INTEGER := 5, p_showOnly IN BOOLEAN := TRUE) IS ---------------------------------------- v_exts PLS_INTEGER := p_extents; v_row PLS_INTEGER := 0; v_index t_index := NULL; v_idxowner t_owner := NULL; v_tabowner t_owner := NULL; v_table t_table := NULL; vr_idx idx_rectype; v_fullTab t_fullTabName := NULL; v_fullIdx t_fullTabName := NULL; v_sql t_sql := NULL; v_initExt PLS_INTEGER := NULL; v_nextExt PLS_INTEGER := NULL; v_largest_free_extent NUMBER(20) := NULL; vt_indx tbl_indexes; v_retval t_retCode := NULL; v_obj t_procfunc := 'RebuildIndexes'; v_msg t_msg := NULL; v_errMsg t_msg := NULL; -------------------- Main Program ---------------------------------- BEGIN -- Set up Environment start ---------------------------------------- p.turn_on; p.set_linelen(len => 140); p.set_linesep(NULL); PLVtmr.capture(CONCAT('Begin ', v_obj)); g_showOnly := p_showOnly; v_msg := c_package || c_period || v_obj || c_colon || 'Start processing...showOnly set to ' || PLVbool.stg(p_showOnly); Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); p.l(v_msg); p.l('-----------------------------------------------------------------------'); EXECUTE IMMEDIATE('ALTER SESSION SET SORT_AREA_SIZE=20480000 SORT_AREA_RETAINED_SIZE=512000'); EXECUTE IMMEDIATE('ALTER SESSION FORCE PARALLEL QUERY'); -- Set up Environment end ---------------------------------------- -- Get list of all indexes with > v_exts allocated for rebuilding <> FOR idx_alloc_rec IN idx_alloc_cur(UPPER(p_schema), v_exts) LOOP v_idxOwner := idx_alloc_rec.index_owner; v_index := idx_alloc_rec.index_name; v_tabOwner := idx_alloc_rec.table_owner; v_table := idx_alloc_rec.table_name; v_fullIdx := v_idxowner || c_period || v_index; v_fullTab := v_tabOwner || c_period || v_table; p.l(v_fullIdx || ' on ' || v_fullTab); Log_Error(p_msg => 'Processing Index ' || v_fullIdx, p_proc => v_obj, p_type => 'I', p_severity => 0); BEGIN -- main_blk -- do not rebuild any tables already locked by another session IF NOT IsTableLocked(v_tabOwner, v_table) THEN -- get largest free extent for tablespace v_largest_free_extent := GetLargestTSFreeExtent(p_tablespace => idx_alloc_rec.tablespace_name); IF v_largest_free_extent > 0 THEN v_largest_free_extent := ROUND(v_largest_free_extent / 1024, 0); -- convert TO KB ELSE v_errMsg := 'Largest free extent for ts ' || idx_alloc_rec.tablespace_name || ' not returned.'; Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 8); v_largest_free_extent := 1024000; END IF; -- increase current size of index by 20% v_initext := GetNearestValue(p_Extsize =>(idx_alloc_rec.Size_KB * 1.2)); -- if v_initExtent > largest free extent in tablespace, then adjust size WHILE v_initExt > v_largest_free_extent LOOP EXIT WHEN v_initExt < v_largest_free_extent; -- decrement initial extent until smaller than -- largest free extent in tablespace v_initExt := CalcInitExtent((idx_alloc_rec.Size_KB * 1.2)); END LOOP; v_nextExt := CalcNextExtent(v_initExt); -- if v_nextExtent > largest free extent in tablespace, then adjust size WHILE v_nextExt > v_largest_free_extent LOOP EXIT WHEN v_nextExt < v_largest_free_extent; -- decrement next extent until smaller than -- largest free extent in tablespace v_initExt := CalcNextExtent(v_initExt * .9); END LOOP; v_msg := 'Old initExt: ' || idx_alloc_rec.Init_Ext_KB || ' Old nextExt: ' || idx_alloc_rec.Next_Ext_KB; Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); p.l(v_msg); v_msg := 'New initExt: ' || v_initExt || ' New nextExt: ' || v_nextExt; Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); p.l(v_msg); vr_idx.index_owner := v_idxOwner; vr_idx.index_name := v_index; vr_idx.uniqueness := idx_alloc_rec.uniqueness; vr_idx.table_owner := v_tabOwner; vr_idx.table_name := v_table; vr_idx.TABLESPACE := idx_alloc_rec.tablespace_name; vr_idx.fullTab := v_fullTab; vr_idx.fullIdx := v_fullIdx; vr_idx.initExt := v_initext; vr_idx.nextExt := v_nextext; vr_idx.DEGREE := idx_alloc_rec.DEGREE; vr_idx.INSTANCES := idx_alloc_rec.INSTANCES; RecreateIndex(p_idxrec => vr_idx); END IF; -- isTableLocked END main_blk; END LOOP main_loop; -- Clean up: Check for any indexes still in UNUSABLE state and rebuild, -- then recompile any invalidated objects RebuildUnusableIndexes; SubmitJob('Recompile_Objects;'); ---------------------------- ShowElapsedTime(v_obj); COMMIT; v_msg := c_package || c_period || v_obj || ' completed successfully.'; Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); EXCEPTION WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); COMMIT; END RebuildIndexes; /***************************************************************************/ PROCEDURE DeallocateUnusedSpace( p_schema IN t_owner := NULL, p_tablespace IN t_tablespace := NULL, p_type IN VARCHAR2 := c_index, p_showOnly IN BOOLEAN := TRUE) IS c_keep VARCHAR2(2) := '32'; -- kbytes v_total_blocks NUMBER(38); v_total_bytes NUMBER(38); v_unused_blocks NUMBER(38); v_unused_bytes NUMBER(38); v_file_id NUMBER(38); v_block_id NUMBER(38); v_last_block NUMBER(38); v_used_blks NUMBER(38); v_used_bytes NUMBER(38); v_owner t_owner; v_segment t_segment; v_sql t_sql; v_fullobjName t_fullTabName; v_obj t_procfunc := 'DeallocateUnusedSpace'; v_msg t_msg := NULL; v_errMsg t_msg := NULL; CURSOR index_cur is SELECT owner, index_name FROM sys.dba_indexes WHERE tablespace_name LIKE DECODE(UPPER(p_tablespace),NULL,'%',UPPER(p_tablespace)) AND owner LIKE DECODE(UPPER(p_schema),NULL,'%',UPPER(p_schema)) AND tablespace_name <> 'SYSTEM' ORDER BY owner,index_name; CURSOR table_cur is SELECT owner, table_name FROM sys.dba_tables WHERE tablespace_name LIKE DECODE(UPPER(p_tablespace),NULL,'%',UPPER(p_tablespace)) AND owner LIKE DECODE(UPPER(p_schema),NULL,'%',UPPER(p_schema)) AND tablespace_name <> 'SYSTEM' ORDER BY owner,table_name; BEGIN -- Set up Environment start ---------------------------------------- p.turn_on; p.set_linelen(len => 140); p.set_linesep(NULL); PLVtmr.capture(CONCAT('Begin ', v_obj)); g_showOnly := p_showOnly; v_msg := c_package || c_period || v_obj || c_colon || 'Start processing...showOnly set to ' || PLVbool.stg(p_showOnly); Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); p.l(v_msg); p.l('-----------------------------------------------------------------------'); EXECUTE IMMEDIATE('ALTER SESSION SET SORT_AREA_SIZE=20480000 SORT_AREA_RETAINED_SIZE=512000'); EXECUTE IMMEDIATE('ALTER SESSION FORCE PARALLEL QUERY'); -- Check for valid tablespace, if specified IF p_tablespace IS NOT NULL THEN IF NOT IsTablespace(p_tablespace) THEN v_errMsg := 'Tablespace, '||p_tablespace||', is invalid or not found.'; RAISE e_general; END IF; END IF; -- Check for valid schema, if specified IF p_schema IS NOT NULL THEN IF NOT IsUser(p_schema) THEN v_errMsg := 'Schema, '||p_schema||', is invalid or not found.'; RAISE e_general; END IF; END IF; IF UPPER(p_type) = c_index THEN <> BEGIN FOR index_rec IN index_cur LOOP v_owner := index_rec.owner; v_segment := index_rec.index_name; p.l('Processing '||index_rec.owner||'.'||index_rec.index_name); ---- DBMS_SPACE.UNUSED_SPACE(v_owner, v_segment, 'INDEX', v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block); ---- v_used_blks := v_total_blocks - v_unused_blocks; v_used_bytes := v_total_bytes - v_unused_bytes; -- p.l('blocks - total: '||v_total_blocks||' unused: '||v_unused_blocks||' total used: '||v_used_blks); -- p.l('bytes - total: '||v_total_bytes||' unused: '||v_unused_bytes||' total used: '||v_used_bytes); v_fullobjName := v_owner||'.'||v_segment; v_sql := 'ALTER INDEX '||v_fullObjName||' DEALLOCATE UNUSED KEEP '||c_keep||'K'; BEGIN IF NOT p_showOnly THEN EXECUTE IMMEDIATE v_sql; p.l('---Space deallocated from '||v_fullObjName); END IF; EXCEPTION WHEN cannot_alter_lob_index THEN NULL; -- Skip WHEN OTHERS THEN p.l('---Error deallocating space for '||v_fullobjName); v_errMsg := SQLERRM; p.l('---'||v_errMsg); END; p.l(c_space); END LOOP; END idx_loop; ELSIF UPPER(p_type) = c_table THEN <> BEGIN FOR table_rec IN table_cur LOOP v_owner := table_rec.owner; v_segment := table_rec.table_name; p.l('Processing '||table_rec.owner||'.'||table_rec.table_name); ---- DBMS_SPACE.UNUSED_SPACE(v_owner, v_segment, 'TABLE', v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block); ---- v_used_blks := v_total_blocks - v_unused_blocks; v_used_bytes := v_total_bytes - v_unused_bytes; -- p.l('blocks - total: '||v_total_blocks||' unused: '||v_unused_blocks||' total used: '||v_used_blks); -- p.l('bytes - total: '||v_total_bytes||' unused: '||v_unused_bytes||' total used: '||v_used_bytes); v_fullobjName := v_owner||'.'||v_segment; v_sql := 'ALTER TABLE '||v_fullObjName||' DEALLOCATE UNUSED KEEP '||c_keep||'K'; BEGIN IF NOT p_showOnly THEN EXECUTE IMMEDIATE v_sql; p.l('---Space deallocated from '||v_fullObjName); END IF; EXCEPTION WHEN cannot_alter_lob_index THEN NULL; -- Skip WHEN OTHERS THEN p.l('---Error deallocating space for '||v_fullobjName); v_errMsg := SQLERRM; p.l('---'||v_errMsg); END; p.l(c_space); END LOOP; END tab_loop; ELSE v_errMsg := p_type ||' is an invalid indicator. Use I for index or T for table.'; RAISE e_general; END IF; ShowElapsedTime(v_obj); COMMIT; v_msg := c_package || c_period || v_obj || ' completed successfully.'; Log_Error(p_msg => v_msg, p_type => 'I', p_proc => v_obj, p_severity => 0); EXCEPTION WHEN e_general THEN v_errMsg := c_package || c_period || v_obj || c_colon || v_errMsg; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); COMMIT; ShowElapsedTime(v_obj); WHEN OTHERS THEN v_errMsg := c_package || c_period || v_obj || c_colon || SQLERRM; p.l(v_errMsg); Log_Error(p_msg => v_errMsg, p_proc => v_obj, p_severity => 9); COMMIT; ShowElapsedTime(v_obj); END DeallocateUnusedSpace; /******Packete Initialization********************************************/ BEGIN -- Initialization -- purge buffer DBMS_OUTPUT.DISABLE; -- enable output DBMS_OUTPUT.ENABLE(buffer_size => 1000000); END Table_Maint; /