tablespace_maintenance.sql
-- ------------------------------------------------------------------------------------- File Name : tablespace_maintenance.sql-- Author : Pierre Montbleau-- Description : Comprehensive Oracle tablespace maintenance and optimization script-- Purpose : Automate tablespace monitoring, maintenance, and space reclamation-- Call Syntax : @F:\DBA\Scripts\tablespace_maintenance.sql (maintenance_type) (tablespace_name)-- Parameters : maintenance_type - Type of maintenance to perform (REPORT, RESIZE, RECLAIM, COALESCE)-- tablespace_name - Specific tablespace or '%' for all tablespaces-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_tablespace_maintenance_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 300SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Tablespace Maintenance Script...PROMPT ==========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Maintenance Type: &1PROMPT Tablespace Name: &2PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_maintenance_type VARCHAR2(20) := UPPER('&1'); v_tablespace_name VARCHAR2(30) := UPPER('&2'); v_tablespace_exists NUMBER;BEGIN IF v_maintenance_type NOT IN ('REPORT', 'RESIZE', 'RECLAIM', 'COALESCE') THEN RAISE_APPLICATION_ERROR(-20001, 'Maintenance type must be REPORT, RESIZE, RECLAIM, or COALESCE'); END IF; IF v_tablespace_name != '%' THEN SELECT COUNT(*) INTO v_tablespace_exists FROM dba_tablespaces WHERE tablespace_name = v_tablespace_name; IF v_tablespace_exists = 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Tablespace ' || v_tablespace_name || ' does not exist'); END IF; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- TABLESPACE USAGE REPORT: Current tablespace utilization-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. TABLESPACE UTILIZATION REPORTPROMPT =================================
COLUMN tablespace_name FORMAT A35COLUMN total_mb FORMAT 999,999,999COLUMN used_mb FORMAT 999,999,999COLUMN free_mb FORMAT 999,999,999COLUMN pct_used FORMAT 999.99COLUMN max_size_mb FORMAT 999,999,999COLUMN autoextensible FORMAT A5
SELECT df.tablespace_name, ROUND(SUM(df.bytes) / 1024 / 1024) AS total_mb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / 1024 / 1024) AS used_mb, ROUND(NVL(SUM(fs.bytes), 0) / 1024 / 1024) AS free_mb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) AS pct_used, ROUND(SUM(CASE WHEN df.autoextensible = 'YES' THEN df.maxbytes ELSE df.bytes END) / 1024 / 1024) AS max_size_mb, MAX(df.autoextensible) as autoextensibleFROM dba_data_files dfLEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_idWHERE df.tablespace_name LIKE '&2'GROUP BY df.tablespace_nameORDER BY pct_used DESC;
-- ------------------------------------------------------------------------------------- DATA FILE ANALYSIS: Individual data file status and sizing-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. DATA FILE ANALYSISPROMPT ======================
COLUMN file_name FORMAT A65COLUMN tablespace_name FORMAT A30COLUMN current_size_mb FORMAT 999,999,999COLUMN max_size_mb FORMAT 999,999,999COLUMN autoextensible FORMAT A5COLUMN increment_by_mb FORMAT 999,999
SELECT file_name, tablespace_name, ROUND(bytes / 1024 / 1024) as current_size_mb, ROUND(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END / 1024 / 1024) as max_size_mb, autoextensible, ROUND(increment_by * 8192 / 1024 / 1024) as increment_by_mbFROM dba_data_filesWHERE tablespace_name LIKE '&2'ORDER BY tablespace_name, file_name;
-- ------------------------------------------------------------------------------------- FRAGMENTATION ANALYSIS: Tablespace fragmentation and free space-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. FRAGMENTATION ANALYSISPROMPT ==========================
COLUMN tablespace_name FORMAT A30COLUMN total_free_mb FORMAT 999,999,999COLUMN largest_free_mb FORMAT 999,999,999COLUMN free_fragments FORMAT 999,999COLUMN fragmentation_pct FORMAT 999.99
SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024) as total_free_mb, ROUND(MAX(bytes) / 1024 / 1024) as largest_free_mb, COUNT(*) as free_fragments, ROUND((1 - (MAX(bytes) / SUM(bytes))) * 100, 2) as fragmentation_pctFROM dba_free_spaceWHERE tablespace_name LIKE '&2'GROUP BY tablespace_nameHAVING SUM(bytes) > 0ORDER BY fragmentation_pct DESC;
-- ------------------------------------------------------------------------------------- AUTOEXTEND CONFIGURATION: Autoextend settings and recommendations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. AUTOEXTEND CONFIGURATION ANALYSISPROMPT =====================================
COLUMN tablespace_name FORMAT A30COLUMN file_name FORMAT A65COLUMN current_size_mb FORMAT 999,999COLUMN autoextend_status FORMAT A10COLUMN recommendation FORMAT A60
SELECT tablespace_name, file_name, ROUND(bytes / 1024 / 1024) as current_size_mb, autoextensible as autoextend_status, CASE WHEN autoextensible = 'NO' AND ROUND(bytes / 1024 / 1024) > 1000 THEN 'Consider enabling autoextend for large datafile' WHEN autoextensible = 'YES' AND ROUND(increment_by * 8192 / 1024 / 1024) < 100 THEN 'Consider increasing autoextend increment size' ELSE 'Configuration appears optimal' END as recommendationFROM dba_data_filesWHERE tablespace_name LIKE '&2'ORDER BY tablespace_name, file_name;
-- ------------------------------------------------------------------------------------- MAINTENANCE OPERATIONS: Perform requested maintenance actions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. MAINTENANCE OPERATIONSPROMPT ==========================
-- Conditional execution based on maintenance typeDECLARE v_maintenance_type VARCHAR2(20) := UPPER('REPORT'); v_tablespace_name VARCHAR2(30) := UPPER('%');
CURSOR c_tablespaces IS SELECT tablespace_name FROM dba_tablespaces WHERE tablespace_name LIKE v_tablespace_name AND contents = 'PERMANENT' AND tablespace_name NOT LIKE 'UNDO%' AND tablespace_name NOT LIKE 'TEMP%';BEGIN IF v_maintenance_type = 'COALESCE' THEN DBMS_OUTPUT.PUT_LINE('Starting tablespace coalesce operation...'); FOR ts_rec IN c_tablespaces LOOP BEGIN EXECUTE IMMEDIATE 'ALTER TABLESPACE ' || ts_rec.tablespace_name || ' COALESCE'; DBMS_OUTPUT.PUT_LINE('Coalesced tablespace: ' || ts_rec.tablespace_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error coalescing ' || ts_rec.tablespace_name || ': ' || SQLERRM); END; END LOOP; DBMS_OUTPUT.PUT_LINE('Coalesce operations completed');
ELSIF v_maintenance_type = 'RECLAIM' THEN DBMS_OUTPUT.PUT_LINE('Starting space reclamation analysis...'); -- Identify segments with high water mark issues FOR reclaim_rec IN ( SELECT owner, segment_name, segment_type, tablespace_name, ROUND(bytes / 1024 / 1024) as size_mb FROM dba_segments WHERE tablespace_name LIKE v_tablespace_name AND segment_type IN ('TABLE', 'TABLE PARTITION') AND ROUND(bytes / 1024 / 1024) > 100 ORDER BY bytes DESC ) LOOP DBMS_OUTPUT.PUT_LINE('Candidate for shrink: ' || reclaim_rec.owner || '.' || reclaim_rec.segment_name || ' (' || reclaim_rec.size_mb || ' MB)'); END LOOP; DBMS_OUTPUT.PUT_LINE('Reclamation analysis completed - review candidates above');
ELSIF v_maintenance_type = 'RESIZE' THEN DBMS_OUTPUT.PUT_LINE('Starting datafile resize analysis...'); -- Identify datafiles that can be resized FOR resize_rec IN ( SELECT df.file_id, df.file_name, df.tablespace_name, ROUND(df.bytes / 1024 / 1024) as current_size_mb, ROUND((df.bytes - NVL(fs.free_bytes, 0)) / 1024 / 1024) as used_mb, ROUND(NVL(fs.free_bytes, 0) / 1024 / 1024) as free_mb FROM dba_data_files df LEFT JOIN ( SELECT file_id, SUM(bytes) as free_bytes FROM dba_free_space GROUP BY file_id ) fs ON df.file_id = fs.file_id WHERE df.tablespace_name LIKE v_tablespace_name AND df.autoextensible = 'NO' AND ROUND(NVL(fs.free_bytes, 0) / 1024 / 1024) > 100 ) LOOP DBMS_OUTPUT.PUT_LINE('Resize candidate: ' || resize_rec.file_name || ' - Free: ' || resize_rec.free_mb || ' MB'); END LOOP; DBMS_OUTPUT.PUT_LINE('Resize analysis completed - review candidates above'); END IF;END;/
-- ------------------------------------------------------------------------------------- GROWTH TREND ANALYSIS: Historical tablespace growth patterns-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. GROWTH TREND ANALYSISPROMPT =========================
COLUMN tablespace_name FORMAT A20COLUMN day FORMAT A10COLUMN daily_growth_mb FORMAT 999,999COLUMN avg_daily_growth_mb FORMAT 999,999
BEGIN IF UPPER('&1') = 'REPORT' THEN DBMS_OUTPUT.PUT_LINE('Growth trend analysis would require AWR data'); DBMS_OUTPUT.PUT_LINE('Consider querying DBA_HIST_TBSPC_SPACE_USAGE for historical trends'); END IF;END;/
-- Placeholder for growth trend analysisSELECT tablespace_name, 'N/A' as day, 0 as daily_growth_mb, 0 as avg_daily_growth_mbFROM dba_tablespacesWHERE tablespace_name LIKE '&2' AND ROWNUM <= 5;
-- ------------------------------------------------------------------------------------- MAINTENANCE RECOMMENDATIONS: Actionable optimization suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MAINTENANCE RECOMMENDATIONSPROMPT ===============================
COLUMN tablespace_name FORMAT A30COLUMN issue_type FORMAT A30COLUMN recommendation FORMAT A80COLUMN priority FORMAT A8
SELECT tablespace_name, 'High Fragmentation' as issue_type, 'Run coalesce operation to consolidate free space' as recommendation, 'HIGH' as priorityFROM ( SELECT tablespace_name, ROUND((1 - (MAX(bytes) / SUM(bytes))) * 100, 2) as frag_pct FROM dba_free_space WHERE tablespace_name LIKE '&2' GROUP BY tablespace_name HAVING ROUND((1 - (MAX(bytes) / SUM(bytes))) * 100, 2) > 50)UNION ALLSELECT tablespace_name, 'Near Capacity' as issue_type, 'Consider adding datafile or resizing existing files' as recommendation, 'HIGH' as priorityFROM ( SELECT df.tablespace_name, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) AS pct_used FROM dba_data_files df LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_id WHERE df.tablespace_name LIKE '&2' GROUP BY df.tablespace_name HAVING ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) > 85)UNION ALLSELECT tablespace_name, 'No Autoextend' as issue_type, 'Enable autoextend for better space management' as recommendation, 'MEDIUM' as priorityFROM ( SELECT DISTINCT tablespace_name FROM dba_data_files WHERE tablespace_name LIKE '&2' AND autoextensible = 'NO' AND ROUND(bytes / 1024 / 1024) > 1000)ORDER BY 4 DESC, tablespace_name;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ==========================================PROMPT TABLESPACE MAINTENANCE COMPLETEDPROMPT ==========================================PROMPT Maintenance Type: &1PROMPT Tablespace Scope: &2PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review HIGH priority recommendations abovePROMPT 2. Schedule regular maintenance operationsPROMPT 3. Monitor tablespace growth trendsPROMPT 4. Consider automated space managementPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_tablespace_maintenance_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 300SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Tablespace Maintenance Script...PROMPT ==========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Maintenance Type: &1PROMPT Tablespace Name: &2PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_maintenance_type VARCHAR2(20) := UPPER('&1'); v_tablespace_name VARCHAR2(30) := UPPER('&2'); v_tablespace_exists NUMBER;BEGIN IF v_maintenance_type NOT IN ('REPORT', 'RESIZE', 'RECLAIM', 'COALESCE') THEN RAISE_APPLICATION_ERROR(-20001, 'Maintenance type must be REPORT, RESIZE, RECLAIM, or COALESCE'); END IF; IF v_tablespace_name != '%' THEN SELECT COUNT(*) INTO v_tablespace_exists FROM dba_tablespaces WHERE tablespace_name = v_tablespace_name; IF v_tablespace_exists = 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Tablespace ' || v_tablespace_name || ' does not exist'); END IF; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- TABLESPACE USAGE REPORT: Current tablespace utilization-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. TABLESPACE UTILIZATION REPORTPROMPT =================================
COLUMN tablespace_name FORMAT A35COLUMN total_mb FORMAT 999,999,999COLUMN used_mb FORMAT 999,999,999COLUMN free_mb FORMAT 999,999,999COLUMN pct_used FORMAT 999.99COLUMN max_size_mb FORMAT 999,999,999COLUMN autoextensible FORMAT A5
SELECT df.tablespace_name, ROUND(SUM(df.bytes) / 1024 / 1024) AS total_mb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / 1024 / 1024) AS used_mb, ROUND(NVL(SUM(fs.bytes), 0) / 1024 / 1024) AS free_mb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) AS pct_used, ROUND(SUM(CASE WHEN df.autoextensible = 'YES' THEN df.maxbytes ELSE df.bytes END) / 1024 / 1024) AS max_size_mb, MAX(df.autoextensible) as autoextensibleFROM dba_data_files dfLEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_idWHERE df.tablespace_name LIKE '&2'GROUP BY df.tablespace_nameORDER BY pct_used DESC;
-- ------------------------------------------------------------------------------------- DATA FILE ANALYSIS: Individual data file status and sizing-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. DATA FILE ANALYSISPROMPT ======================
COLUMN file_name FORMAT A65COLUMN tablespace_name FORMAT A30COLUMN current_size_mb FORMAT 999,999,999COLUMN max_size_mb FORMAT 999,999,999COLUMN autoextensible FORMAT A5COLUMN increment_by_mb FORMAT 999,999
SELECT file_name, tablespace_name, ROUND(bytes / 1024 / 1024) as current_size_mb, ROUND(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END / 1024 / 1024) as max_size_mb, autoextensible, ROUND(increment_by * 8192 / 1024 / 1024) as increment_by_mbFROM dba_data_filesWHERE tablespace_name LIKE '&2'ORDER BY tablespace_name, file_name;
-- ------------------------------------------------------------------------------------- FRAGMENTATION ANALYSIS: Tablespace fragmentation and free space-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. FRAGMENTATION ANALYSISPROMPT ==========================
COLUMN tablespace_name FORMAT A30COLUMN total_free_mb FORMAT 999,999,999COLUMN largest_free_mb FORMAT 999,999,999COLUMN free_fragments FORMAT 999,999COLUMN fragmentation_pct FORMAT 999.99
SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024) as total_free_mb, ROUND(MAX(bytes) / 1024 / 1024) as largest_free_mb, COUNT(*) as free_fragments, ROUND((1 - (MAX(bytes) / SUM(bytes))) * 100, 2) as fragmentation_pctFROM dba_free_spaceWHERE tablespace_name LIKE '&2'GROUP BY tablespace_nameHAVING SUM(bytes) > 0ORDER BY fragmentation_pct DESC;
-- ------------------------------------------------------------------------------------- AUTOEXTEND CONFIGURATION: Autoextend settings and recommendations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. AUTOEXTEND CONFIGURATION ANALYSISPROMPT =====================================
COLUMN tablespace_name FORMAT A30COLUMN file_name FORMAT A65COLUMN current_size_mb FORMAT 999,999COLUMN autoextend_status FORMAT A10COLUMN recommendation FORMAT A60
SELECT tablespace_name, file_name, ROUND(bytes / 1024 / 1024) as current_size_mb, autoextensible as autoextend_status, CASE WHEN autoextensible = 'NO' AND ROUND(bytes / 1024 / 1024) > 1000 THEN 'Consider enabling autoextend for large datafile' WHEN autoextensible = 'YES' AND ROUND(increment_by * 8192 / 1024 / 1024) < 100 THEN 'Consider increasing autoextend increment size' ELSE 'Configuration appears optimal' END as recommendationFROM dba_data_filesWHERE tablespace_name LIKE '&2'ORDER BY tablespace_name, file_name;
-- ------------------------------------------------------------------------------------- MAINTENANCE OPERATIONS: Perform requested maintenance actions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. MAINTENANCE OPERATIONSPROMPT ==========================
-- Conditional execution based on maintenance typeDECLARE v_maintenance_type VARCHAR2(20) := UPPER('REPORT'); v_tablespace_name VARCHAR2(30) := UPPER('%');
CURSOR c_tablespaces IS SELECT tablespace_name FROM dba_tablespaces WHERE tablespace_name LIKE v_tablespace_name AND contents = 'PERMANENT' AND tablespace_name NOT LIKE 'UNDO%' AND tablespace_name NOT LIKE 'TEMP%';BEGIN IF v_maintenance_type = 'COALESCE' THEN DBMS_OUTPUT.PUT_LINE('Starting tablespace coalesce operation...'); FOR ts_rec IN c_tablespaces LOOP BEGIN EXECUTE IMMEDIATE 'ALTER TABLESPACE ' || ts_rec.tablespace_name || ' COALESCE'; DBMS_OUTPUT.PUT_LINE('Coalesced tablespace: ' || ts_rec.tablespace_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error coalescing ' || ts_rec.tablespace_name || ': ' || SQLERRM); END; END LOOP; DBMS_OUTPUT.PUT_LINE('Coalesce operations completed');
ELSIF v_maintenance_type = 'RECLAIM' THEN DBMS_OUTPUT.PUT_LINE('Starting space reclamation analysis...'); -- Identify segments with high water mark issues FOR reclaim_rec IN ( SELECT owner, segment_name, segment_type, tablespace_name, ROUND(bytes / 1024 / 1024) as size_mb FROM dba_segments WHERE tablespace_name LIKE v_tablespace_name AND segment_type IN ('TABLE', 'TABLE PARTITION') AND ROUND(bytes / 1024 / 1024) > 100 ORDER BY bytes DESC ) LOOP DBMS_OUTPUT.PUT_LINE('Candidate for shrink: ' || reclaim_rec.owner || '.' || reclaim_rec.segment_name || ' (' || reclaim_rec.size_mb || ' MB)'); END LOOP; DBMS_OUTPUT.PUT_LINE('Reclamation analysis completed - review candidates above');
ELSIF v_maintenance_type = 'RESIZE' THEN DBMS_OUTPUT.PUT_LINE('Starting datafile resize analysis...'); -- Identify datafiles that can be resized FOR resize_rec IN ( SELECT df.file_id, df.file_name, df.tablespace_name, ROUND(df.bytes / 1024 / 1024) as current_size_mb, ROUND((df.bytes - NVL(fs.free_bytes, 0)) / 1024 / 1024) as used_mb, ROUND(NVL(fs.free_bytes, 0) / 1024 / 1024) as free_mb FROM dba_data_files df LEFT JOIN ( SELECT file_id, SUM(bytes) as free_bytes FROM dba_free_space GROUP BY file_id ) fs ON df.file_id = fs.file_id WHERE df.tablespace_name LIKE v_tablespace_name AND df.autoextensible = 'NO' AND ROUND(NVL(fs.free_bytes, 0) / 1024 / 1024) > 100 ) LOOP DBMS_OUTPUT.PUT_LINE('Resize candidate: ' || resize_rec.file_name || ' - Free: ' || resize_rec.free_mb || ' MB'); END LOOP; DBMS_OUTPUT.PUT_LINE('Resize analysis completed - review candidates above'); END IF;END;/
-- ------------------------------------------------------------------------------------- GROWTH TREND ANALYSIS: Historical tablespace growth patterns-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. GROWTH TREND ANALYSISPROMPT =========================
COLUMN tablespace_name FORMAT A20COLUMN day FORMAT A10COLUMN daily_growth_mb FORMAT 999,999COLUMN avg_daily_growth_mb FORMAT 999,999
BEGIN IF UPPER('&1') = 'REPORT' THEN DBMS_OUTPUT.PUT_LINE('Growth trend analysis would require AWR data'); DBMS_OUTPUT.PUT_LINE('Consider querying DBA_HIST_TBSPC_SPACE_USAGE for historical trends'); END IF;END;/
-- Placeholder for growth trend analysisSELECT tablespace_name, 'N/A' as day, 0 as daily_growth_mb, 0 as avg_daily_growth_mbFROM dba_tablespacesWHERE tablespace_name LIKE '&2' AND ROWNUM <= 5;
-- ------------------------------------------------------------------------------------- MAINTENANCE RECOMMENDATIONS: Actionable optimization suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MAINTENANCE RECOMMENDATIONSPROMPT ===============================
COLUMN tablespace_name FORMAT A30COLUMN issue_type FORMAT A30COLUMN recommendation FORMAT A80COLUMN priority FORMAT A8
SELECT tablespace_name, 'High Fragmentation' as issue_type, 'Run coalesce operation to consolidate free space' as recommendation, 'HIGH' as priorityFROM ( SELECT tablespace_name, ROUND((1 - (MAX(bytes) / SUM(bytes))) * 100, 2) as frag_pct FROM dba_free_space WHERE tablespace_name LIKE '&2' GROUP BY tablespace_name HAVING ROUND((1 - (MAX(bytes) / SUM(bytes))) * 100, 2) > 50)UNION ALLSELECT tablespace_name, 'Near Capacity' as issue_type, 'Consider adding datafile or resizing existing files' as recommendation, 'HIGH' as priorityFROM ( SELECT df.tablespace_name, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) AS pct_used FROM dba_data_files df LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_id WHERE df.tablespace_name LIKE '&2' GROUP BY df.tablespace_name HAVING ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) > 85)UNION ALLSELECT tablespace_name, 'No Autoextend' as issue_type, 'Enable autoextend for better space management' as recommendation, 'MEDIUM' as priorityFROM ( SELECT DISTINCT tablespace_name FROM dba_data_files WHERE tablespace_name LIKE '&2' AND autoextensible = 'NO' AND ROUND(bytes / 1024 / 1024) > 1000)ORDER BY 4 DESC, tablespace_name;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ==========================================PROMPT TABLESPACE MAINTENANCE COMPLETEDPROMPT ==========================================PROMPT Maintenance Type: &1PROMPT Tablespace Scope: &2PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review HIGH priority recommendations abovePROMPT 2. Schedule regular maintenance operationsPROMPT 3. Monitor tablespace growth trendsPROMPT 4. Consider automated space managementPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------