database_capacity_planner.sql
-- ------------------------------------------------------------------------------------- File Name : database_capacity_planner.sql-- Author : Pierre Montbleau-- Description : Oracle database capacity planning and growth forecasting utility-- Purpose : Analyze current capacity, forecast growth, and provide scaling recommendations-- Call Syntax : @F:\DBA\Scripts\database_capacity_planner.sql (forecast_days) (analysis_level)-- Parameters : forecast_days - Number of days to forecast growth (default: 365)-- analysis_level - Analysis depth (BASIC, STANDARD, COMPREHENSIVE)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINTCOLUMN REPORT_FILE NEW_VALUE REPORT_FILE 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 TIME1, 'F:\DBA\Scripts\AI\Reports\DB_capacity_report_' || USER || '_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.txt' AS REPORT_FILEFROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1DEFINE REPORT_FILE = &&REPORT_FILE
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Start output redirectionSPOOL &REPORT_FILE
PROMPT ================================================================================PROMPT DATABASE CAPACITY PLANNER REPORTPROMPT ================================================================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Forecast Period: &1 daysPROMPT Analysis Level: &2PROMPT Report Timestamp: &&DATE1 &&TIME1PROMPT Report File: &&REPORT_FILEPROMPT ================================================================================PROMPT
-- Validate parametersDECLARE v_forecast_days NUMBER := NVL(TO_NUMBER('&1'), 365); v_analysis_level VARCHAR2(20) := UPPER(NVL('&2', 'STANDARD'));BEGIN IF v_forecast_days < 30 OR v_forecast_days > 1825 THEN RAISE_APPLICATION_ERROR(-20001, 'Forecast days must be between 30 and 1825 (5 years)'); END IF; IF v_analysis_level NOT IN ('BASIC', 'STANDARD', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20002, 'Analysis level must be BASIC, STANDARD, or COMPREHENSIVE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful - Forecast: ' || v_forecast_days || ' days, Level: ' || v_analysis_level);EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for forecast days'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- CURRENT CAPACITY SUMMARY: Database size and utilization overview-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 1. CURRENT CAPACITY SUMMARYPROMPT ================================================================================
COLUMN component FORMAT A30COLUMN current_size_gb FORMAT 999,999,999.99COLUMN allocated_gb FORMAT 999,999,999.99COLUMN used_gb FORMAT 999,999,999.99COLUMN utilization_pct FORMAT 999.99
SELECT 'Database Data' as component, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as current_size_gb, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as allocated_gb, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as used_gb, 100.00 as utilization_pctFROM dba_segmentsUNION ALLSELECT 'Tablespace Datafiles', ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2), ROUND(SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END) / 1024 / 1024 / 1024, 2), ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2), ROUND((SUM(bytes) / SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END)) * 100, 2)FROM dba_data_filesUNION ALLSELECT 'Archive Logs (Daily)', ROUND((SELECT NVL(SUM(blocks * block_size), 0) / 1024 / 1024 / 1024 FROM v$archived_log WHERE first_time >= TRUNC(SYSDATE)) , 2), 0, ROUND((SELECT NVL(SUM(blocks * block_size), 0) / 1024 / 1024 / 1024 FROM v$archived_log WHERE first_time >= TRUNC(SYSDATE)) , 2), 100.00FROM dualORDER BY 2 DESC;
-- ------------------------------------------------------------------------------------- TABLESPACE GROWTH ANALYSIS: Current usage and growth trends-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 2. TABLESPACE GROWTH ANALYSISPROMPT ================================================================================
COLUMN tablespace_name FORMAT A35COLUMN current_used_gb FORMAT 999,999.99COLUMN allocated_gb FORMAT 999,999.99COLUMN max_size_gb FORMAT 999,999.99COLUMN utilization_pct FORMAT 999.99COLUMN growth_status FORMAT A15
SELECT ts.tablespace_name, ROUND(ts.used_space * 8 / 1024 / 1024, 2) as current_used_gb, ROUND(ts.tablespace_size * 8 / 1024 / 1024, 2) as allocated_gb, ROUND(ts.used_percent / 100 * ts.tablespace_size * 8 / 1024 / 1024, 2) as max_size_used_equiv_gb, ROUND(ts.used_percent, 2) as utilization_pct, CASE WHEN ts.used_percent > 90 THEN 'CRITICAL' WHEN ts.used_percent > 80 THEN 'WARNING' WHEN ts.used_percent > 70 THEN 'MONITOR' ELSE 'HEALTHY' END as growth_statusFROM dba_tablespace_usage_metrics tsWHERE UPPER('&2') IN ('STANDARD', 'COMPREHENSIVE')ORDER BY utilization_pct DESC;
-- ------------------------------------------------------------------------------------- SEGMENT GROWTH FORECAST: Largest segments and growth projection-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 3. LARGEST SEGMENTS AND GROWTH PROJECTIONPROMPT ================================================================================
COLUMN owner FORMAT A15COLUMN segment_name FORMAT A30COLUMN segment_type FORMAT A15COLUMN current_size_gb FORMAT 999,999.99COLUMN growth_rate_pct FORMAT 999.99COLUMN projected_size_gb FORMAT 999,999.99COLUMN growth_impact FORMAT A15
SELECT owner, segment_name, segment_type, ROUND(bytes / 1024 / 1024 / 1024, 2) as current_size_gb, 5.0 as growth_rate_pct, ROUND(bytes / 1024 / 1024 / 1024 * POWER(1.05, &1/30), 2) as projected_size_gb, CASE WHEN bytes / 1024 / 1024 / 1024 > 10 THEN 'HIGH' WHEN bytes / 1024 / 1024 / 1024 > 5 THEN 'MEDIUM' ELSE 'LOW' END as growth_impactFROM ( SELECT owner, segment_name, segment_type, bytes, ROW_NUMBER() OVER (ORDER BY bytes DESC) as rnk FROM dba_segments WHERE segment_type IN ('TABLE', 'INDEX'))WHERE rnk <= 10ORDER BY bytes DESC;
-- ------------------------------------------------------------------------------------- ARCHIVELOG GROWTH ANALYSIS: Archive log generation rates-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 4. ARCHIVELOG GENERATION ANALYSISPROMPT ================================================================================
COLUMN time_period FORMAT A15COLUMN archive_count FORMAT 999,999COLUMN total_size_gb FORMAT 999,999.99COLUMN avg_daily_gb FORMAT 999,999.99COLUMN growth_trend FORMAT A15
SELECT 'Last 7 Days' as time_period, COUNT(*) as archive_count, ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2) as total_size_gb, ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024 / 7, 2) as avg_daily_gb, 'STABLE' as growth_trendFROM v$archived_logWHERE first_time >= SYSDATE - 7 AND UPPER('&2') IN ('STANDARD', 'COMPREHENSIVE')UNION ALLSELECT 'Last 30 Days', COUNT(*), ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2), ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024 / 30, 2), 'STABLE'FROM v$archived_logWHERE first_time >= SYSDATE - 30 AND UPPER('&2') IN ('STANDARD', 'COMPREHENSIVE')ORDER BY time_period;
-- ------------------------------------------------------------------------------------- MEMORY CAPACITY ANALYSIS: SGA and PGA memory utilization-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 5. MEMORY CAPACITY ANALYSISPROMPT ================================================================================
COLUMN memory_component FORMAT A25COLUMN current_size_gb FORMAT 999,999.99COLUMN max_size_gb FORMAT 999,999.99COLUMN utilization_pct FORMAT 999.99COLUMN recommendation FORMAT A40
SELECT 'SGA Total' as memory_component, ROUND(SUM(value) / 1024 / 1024 / 1024, 2) as current_size_gb, ROUND((SELECT value FROM v$parameter WHERE name = 'sga_max_size') / 1024 / 1024 / 1024, 2) as max_size_gb, ROUND((SUM(value) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size')) * 100, 2) as utilization_pct, CASE WHEN (SUM(value) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size')) > 0.9 THEN 'Consider increasing SGA' ELSE 'Adequate' END as recommendationFROM v$sgaWHERE UPPER('&2') IN ('COMPREHENSIVE')GROUP BY 'SGA Total'UNION ALLSELECT 'PGA Aggregate', ROUND(value / 1024 / 1024 / 1024, 2), ROUND((SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') / 1024 / 1024 / 1024, 2), ROUND((value / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit')) * 100, 2), CASE WHEN (value / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit')) > 0.8 THEN 'Monitor PGA usage' ELSE 'Adequate' ENDFROM v$pgastatWHERE name = 'aggregate PGA target parameter' AND UPPER('&2') IN ('COMPREHENSIVE')ORDER BY memory_component;
-- ------------------------------------------------------------------------------------- CONNECTION GROWTH ANALYSIS: Session and connection trends-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- CONNECTION GROWTH ANALYSIS: Session and connection trends-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 6. CONNECTION GROWTH ANALYSISPROMPT ================================================================================
COLUMN metric FORMAT A30COLUMN current_value FORMAT 999,999COLUMN peak_value FORMAT A15COLUMN growth_trend FORMAT A15COLUMN capacity_status FORMAT A15
SELECT 'Current Sessions' as metric, current_count as current_value, max_sessions as peak_value, 'STABLE' as growth_trend, CASE WHEN current_count > 500 THEN 'HIGH' WHEN current_count > 200 THEN 'MEDIUM' ELSE 'LOW' END as capacity_statusFROM ( SELECT COUNT(*) as current_count, max_sessions FROM v$session, (SELECT value as max_sessions FROM v$parameter WHERE name = 'sessions') WHERE type = 'USER' GROUP BY max_sessions)UNION ALLSELECT 'Active Sessions', active_count, NULL as peak_value, 'STABLE', CASE WHEN active_count > 50 THEN 'HIGH' WHEN active_count > 20 THEN 'MEDIUM' ELSE 'LOW' ENDFROM ( SELECT SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) as active_count FROM v$session WHERE type = 'USER')ORDER BY metric;
-- ------------------------------------------------------------------------------------- STORAGE FORECAST: Projected storage requirements-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 7. STORAGE CAPACITY FORECASTPROMPT ================================================================================
COLUMN forecast_period FORMAT A20COLUMN component FORMAT A25COLUMN current_size_gb FORMAT 999,999.99COLUMN projected_size_gb FORMAT 999,999.99COLUMN growth_gb FORMAT 999,999.99COLUMN growth_pct FORMAT 999.99
SELECT * FROM ( SELECT '30 Days' as forecast_period, 'Database Data' as component, ROUND(db_data.used_gb, 2) as current_size_gb, ROUND(db_data.used_gb * POWER(1.05, 30/30), 2) as projected_size_gb, ROUND(db_data.used_gb * (POWER(1.05, 30/30) - 1), 2) as growth_gb, ROUND((POWER(1.05, 30/30) - 1) * 100, 2) as growth_pct FROM ( SELECT (SELECT SUM(bytes) / 1024 / 1024 / 1024 FROM dba_segments) as used_gb FROM dual ) db_data WHERE db_data.used_gb IS NOT NULL UNION ALL SELECT '&1 Days', 'Database Data', ROUND(db_data.used_gb, 2), ROUND(db_data.used_gb * POWER(1.05, &1/30), 2), ROUND(db_data.used_gb * (POWER(1.05, &1/30) - 1), 2), ROUND((POWER(1.05, &1/30) - 1) * 100, 2) FROM ( SELECT (SELECT SUM(bytes) / 1024 / 1024 / 1024 FROM dba_segments) as used_gb FROM dual ) db_data WHERE db_data.used_gb IS NOT NULL UNION ALL SELECT '&1 Days', 'Archive Logs', archive_data.daily_size_gb * &1, archive_data.daily_size_gb * &1 * 1.05, archive_data.daily_size_gb * &1 * 0.05, 5.0 FROM ( SELECT ROUND(NVL(SUM(blocks * block_size), 0) / 1024 / 1024 / 1024, 2) as daily_size_gb FROM v$archived_log WHERE first_time >= SYSDATE - 1 ) archive_data WHERE archive_data.daily_size_gb > 0)WHERE current_size_gb IS NOT NULLORDER BY CASE forecast_period WHEN '30 Days' THEN 1 ELSE 2 END, component;
-- ------------------------------------------------------------------------------------- CAPACITY RECOMMENDATIONS: Actionable scaling recommendations-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 8. CAPACITY PLANNING RECOMMENDATIONSPROMPT ================================================================================
COLUMN recommendation_type FORMAT A25COLUMN description FORMAT A60COLUMN timeframe FORMAT A15COLUMN priority FORMAT A8COLUMN estimated_cost FORMAT A15
SELECT recommendation_type, description, timeframe, priority, estimated_costFROM ( SELECT 'Storage Expansion' as recommendation_type, 'Add datafiles to tablespace: ' || tablespace_name as description, 'IMMEDIATE' as timeframe, 'HIGH' as priority, 'LOW' as estimated_cost FROM ( SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics WHERE used_percent > 90 AND ROWNUM <= 3 ) UNION ALL SELECT 'Archive Management', 'Implement archive log retention policy', '30 DAYS', 'MEDIUM', 'LOW' FROM dual WHERE (SELECT ROUND(NVL(SUM(blocks * block_size), 0) / 1024 / 1024 / 1024, 2) FROM v$archived_log WHERE first_time >= SYSDATE - 30) > 100 UNION ALL SELECT 'Memory Optimization', 'Consider increasing SGA size by 20%', '90 DAYS', 'MEDIUM', 'MEDIUM' FROM dual WHERE (SELECT ROUND(SUM(value) / 1024 / 1024 / 1024, 2) FROM v$sga) / (SELECT ROUND(value / 1024 / 1024 / 1024, 2) FROM v$parameter WHERE name = 'sga_max_size') > 0.8 UNION ALL SELECT 'Performance Scaling', 'Plan for connection pool optimization', '180 DAYS', 'LOW', 'MEDIUM' FROM dual WHERE (SELECT COUNT(*) FROM v$session WHERE type = 'USER') > 300)ORDER BY CASE priority WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END, CASE timeframe WHEN 'IMMEDIATE' THEN 1 WHEN '30 DAYS' THEN 2 WHEN '90 DAYS' THEN 3 WHEN '180 DAYS' THEN 4 ELSE 5 END;
-- ------------------------------------------------------------------------------------- RISK ASSESSMENT: Capacity-related risks and mitigation-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 9. CAPACITY RISK ASSESSMENTPROMPT ================================================================================
COLUMN risk_category FORMAT A20COLUMN risk_description FORMAT A60COLUMN probability FORMAT A15COLUMN impact FORMAT A15COLUMN mitigation_strategy FORMAT A80
SELECT risk_category, risk_description, probability, impact, mitigation_strategyFROM ( SELECT 'Storage Capacity' as risk_category, 'Tablespace ' || tablespace_name || ' at ' || ROUND(used_percent, 2) || '% utilization' as risk_description, 'HIGH' as probability, 'HIGH' as impact, 'Immediate datafile addition or tablespace resize required' as mitigation_strategy FROM dba_tablespace_usage_metrics WHERE used_percent > 90 AND ROWNUM = 1 UNION ALL SELECT 'Archive Growth', 'High archive log generation rate detected', 'MEDIUM', 'MEDIUM', 'Implement archive log compression and review retention policies' FROM dual WHERE (SELECT ROUND(NVL(SUM(blocks * block_size), 0) / 1024 / 1024 / 1024, 2) FROM v$archived_log WHERE first_time >= SYSDATE - 1) > 10 UNION ALL SELECT 'Memory Pressure', 'SGA utilization approaching maximum capacity', 'LOW', 'HIGH', 'Monitor memory usage and plan for SGA increase in next maintenance window' FROM dual WHERE (SELECT ROUND(SUM(value) / 1024 / 1024 / 1024, 2) FROM v$sga) / (SELECT ROUND(value / 1024 / 1024 / 1024, 2) FROM v$parameter WHERE name = 'sga_max_size') > 0.85)ORDER BY CASE probability WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END, CASE impact WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END; -- ------------------------------------------------------------------------------------- COMPLETION: Capacity planning summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT CAPACITY PLANNING ANALYSIS COMPLETEDPROMPT ================================================================================PROMPT PROMPT Summary:PROMPT ---------PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Forecast Period: &1 daysPROMPT Analysis Level: &2PROMPT Start Time: &&DATE1 &&TIME1PROMPT Completion Time: COLUMN END_TIME NEW_VALUE END_TIME NOPRINTSELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as END_TIME FROM DUAL;PROMPT &&END_TIMEPROMPT Report File: &&REPORT_FILEPROMPT PROMPT Key Findings:PROMPT ------------PROMPT 1. Review HIGH priority recommendations immediatelyPROMPT 2. Monitor storage growth trends regularlyPROMPT 3. Plan infrastructure upgrades based on projectionsPROMPT 4. Implement risk mitigation strategies proactivelyPROMPT 5. Schedule regular capacity reviews quarterlyPROMPT PROMPT ================================================================================
-- Display report locationPROMPT Report has been saved to: &&REPORT_FILEPROMPT To view the report: TYPE &&REPORT_FILEPROMPT ================================================================================
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- Stop output redirectionSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINTCOLUMN REPORT_FILE NEW_VALUE REPORT_FILE 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 TIME1, 'F:\DBA\Scripts\AI\Reports\DB_capacity_report_' || USER || '_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.txt' AS REPORT_FILEFROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1DEFINE REPORT_FILE = &&REPORT_FILE
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Start output redirectionSPOOL &REPORT_FILE
PROMPT ================================================================================PROMPT DATABASE CAPACITY PLANNER REPORTPROMPT ================================================================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Forecast Period: &1 daysPROMPT Analysis Level: &2PROMPT Report Timestamp: &&DATE1 &&TIME1PROMPT Report File: &&REPORT_FILEPROMPT ================================================================================PROMPT
-- Validate parametersDECLARE v_forecast_days NUMBER := NVL(TO_NUMBER('&1'), 365); v_analysis_level VARCHAR2(20) := UPPER(NVL('&2', 'STANDARD'));BEGIN IF v_forecast_days < 30 OR v_forecast_days > 1825 THEN RAISE_APPLICATION_ERROR(-20001, 'Forecast days must be between 30 and 1825 (5 years)'); END IF; IF v_analysis_level NOT IN ('BASIC', 'STANDARD', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20002, 'Analysis level must be BASIC, STANDARD, or COMPREHENSIVE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful - Forecast: ' || v_forecast_days || ' days, Level: ' || v_analysis_level);EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for forecast days'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- CURRENT CAPACITY SUMMARY: Database size and utilization overview-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 1. CURRENT CAPACITY SUMMARYPROMPT ================================================================================
COLUMN component FORMAT A30COLUMN current_size_gb FORMAT 999,999,999.99COLUMN allocated_gb FORMAT 999,999,999.99COLUMN used_gb FORMAT 999,999,999.99COLUMN utilization_pct FORMAT 999.99
SELECT 'Database Data' as component, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as current_size_gb, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as allocated_gb, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as used_gb, 100.00 as utilization_pctFROM dba_segmentsUNION ALLSELECT 'Tablespace Datafiles', ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2), ROUND(SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END) / 1024 / 1024 / 1024, 2), ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2), ROUND((SUM(bytes) / SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END)) * 100, 2)FROM dba_data_filesUNION ALLSELECT 'Archive Logs (Daily)', ROUND((SELECT NVL(SUM(blocks * block_size), 0) / 1024 / 1024 / 1024 FROM v$archived_log WHERE first_time >= TRUNC(SYSDATE)) , 2), 0, ROUND((SELECT NVL(SUM(blocks * block_size), 0) / 1024 / 1024 / 1024 FROM v$archived_log WHERE first_time >= TRUNC(SYSDATE)) , 2), 100.00FROM dualORDER BY 2 DESC;
-- ------------------------------------------------------------------------------------- TABLESPACE GROWTH ANALYSIS: Current usage and growth trends-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 2. TABLESPACE GROWTH ANALYSISPROMPT ================================================================================
COLUMN tablespace_name FORMAT A35COLUMN current_used_gb FORMAT 999,999.99COLUMN allocated_gb FORMAT 999,999.99COLUMN max_size_gb FORMAT 999,999.99COLUMN utilization_pct FORMAT 999.99COLUMN growth_status FORMAT A15
SELECT ts.tablespace_name, ROUND(ts.used_space * 8 / 1024 / 1024, 2) as current_used_gb, ROUND(ts.tablespace_size * 8 / 1024 / 1024, 2) as allocated_gb, ROUND(ts.used_percent / 100 * ts.tablespace_size * 8 / 1024 / 1024, 2) as max_size_used_equiv_gb, ROUND(ts.used_percent, 2) as utilization_pct, CASE WHEN ts.used_percent > 90 THEN 'CRITICAL' WHEN ts.used_percent > 80 THEN 'WARNING' WHEN ts.used_percent > 70 THEN 'MONITOR' ELSE 'HEALTHY' END as growth_statusFROM dba_tablespace_usage_metrics tsWHERE UPPER('&2') IN ('STANDARD', 'COMPREHENSIVE')ORDER BY utilization_pct DESC;
-- ------------------------------------------------------------------------------------- SEGMENT GROWTH FORECAST: Largest segments and growth projection-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 3. LARGEST SEGMENTS AND GROWTH PROJECTIONPROMPT ================================================================================
COLUMN owner FORMAT A15COLUMN segment_name FORMAT A30COLUMN segment_type FORMAT A15COLUMN current_size_gb FORMAT 999,999.99COLUMN growth_rate_pct FORMAT 999.99COLUMN projected_size_gb FORMAT 999,999.99COLUMN growth_impact FORMAT A15
SELECT owner, segment_name, segment_type, ROUND(bytes / 1024 / 1024 / 1024, 2) as current_size_gb, 5.0 as growth_rate_pct, ROUND(bytes / 1024 / 1024 / 1024 * POWER(1.05, &1/30), 2) as projected_size_gb, CASE WHEN bytes / 1024 / 1024 / 1024 > 10 THEN 'HIGH' WHEN bytes / 1024 / 1024 / 1024 > 5 THEN 'MEDIUM' ELSE 'LOW' END as growth_impactFROM ( SELECT owner, segment_name, segment_type, bytes, ROW_NUMBER() OVER (ORDER BY bytes DESC) as rnk FROM dba_segments WHERE segment_type IN ('TABLE', 'INDEX'))WHERE rnk <= 10ORDER BY bytes DESC;
-- ------------------------------------------------------------------------------------- ARCHIVELOG GROWTH ANALYSIS: Archive log generation rates-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 4. ARCHIVELOG GENERATION ANALYSISPROMPT ================================================================================
COLUMN time_period FORMAT A15COLUMN archive_count FORMAT 999,999COLUMN total_size_gb FORMAT 999,999.99COLUMN avg_daily_gb FORMAT 999,999.99COLUMN growth_trend FORMAT A15
SELECT 'Last 7 Days' as time_period, COUNT(*) as archive_count, ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2) as total_size_gb, ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024 / 7, 2) as avg_daily_gb, 'STABLE' as growth_trendFROM v$archived_logWHERE first_time >= SYSDATE - 7 AND UPPER('&2') IN ('STANDARD', 'COMPREHENSIVE')UNION ALLSELECT 'Last 30 Days', COUNT(*), ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2), ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024 / 30, 2), 'STABLE'FROM v$archived_logWHERE first_time >= SYSDATE - 30 AND UPPER('&2') IN ('STANDARD', 'COMPREHENSIVE')ORDER BY time_period;
-- ------------------------------------------------------------------------------------- MEMORY CAPACITY ANALYSIS: SGA and PGA memory utilization-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 5. MEMORY CAPACITY ANALYSISPROMPT ================================================================================
COLUMN memory_component FORMAT A25COLUMN current_size_gb FORMAT 999,999.99COLUMN max_size_gb FORMAT 999,999.99COLUMN utilization_pct FORMAT 999.99COLUMN recommendation FORMAT A40
SELECT 'SGA Total' as memory_component, ROUND(SUM(value) / 1024 / 1024 / 1024, 2) as current_size_gb, ROUND((SELECT value FROM v$parameter WHERE name = 'sga_max_size') / 1024 / 1024 / 1024, 2) as max_size_gb, ROUND((SUM(value) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size')) * 100, 2) as utilization_pct, CASE WHEN (SUM(value) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size')) > 0.9 THEN 'Consider increasing SGA' ELSE 'Adequate' END as recommendationFROM v$sgaWHERE UPPER('&2') IN ('COMPREHENSIVE')GROUP BY 'SGA Total'UNION ALLSELECT 'PGA Aggregate', ROUND(value / 1024 / 1024 / 1024, 2), ROUND((SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') / 1024 / 1024 / 1024, 2), ROUND((value / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit')) * 100, 2), CASE WHEN (value / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit')) > 0.8 THEN 'Monitor PGA usage' ELSE 'Adequate' ENDFROM v$pgastatWHERE name = 'aggregate PGA target parameter' AND UPPER('&2') IN ('COMPREHENSIVE')ORDER BY memory_component;
-- ------------------------------------------------------------------------------------- CONNECTION GROWTH ANALYSIS: Session and connection trends-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- CONNECTION GROWTH ANALYSIS: Session and connection trends-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 6. CONNECTION GROWTH ANALYSISPROMPT ================================================================================
COLUMN metric FORMAT A30COLUMN current_value FORMAT 999,999COLUMN peak_value FORMAT A15COLUMN growth_trend FORMAT A15COLUMN capacity_status FORMAT A15
SELECT 'Current Sessions' as metric, current_count as current_value, max_sessions as peak_value, 'STABLE' as growth_trend, CASE WHEN current_count > 500 THEN 'HIGH' WHEN current_count > 200 THEN 'MEDIUM' ELSE 'LOW' END as capacity_statusFROM ( SELECT COUNT(*) as current_count, max_sessions FROM v$session, (SELECT value as max_sessions FROM v$parameter WHERE name = 'sessions') WHERE type = 'USER' GROUP BY max_sessions)UNION ALLSELECT 'Active Sessions', active_count, NULL as peak_value, 'STABLE', CASE WHEN active_count > 50 THEN 'HIGH' WHEN active_count > 20 THEN 'MEDIUM' ELSE 'LOW' ENDFROM ( SELECT SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) as active_count FROM v$session WHERE type = 'USER')ORDER BY metric;
-- ------------------------------------------------------------------------------------- STORAGE FORECAST: Projected storage requirements-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 7. STORAGE CAPACITY FORECASTPROMPT ================================================================================
COLUMN forecast_period FORMAT A20COLUMN component FORMAT A25COLUMN current_size_gb FORMAT 999,999.99COLUMN projected_size_gb FORMAT 999,999.99COLUMN growth_gb FORMAT 999,999.99COLUMN growth_pct FORMAT 999.99
SELECT * FROM ( SELECT '30 Days' as forecast_period, 'Database Data' as component, ROUND(db_data.used_gb, 2) as current_size_gb, ROUND(db_data.used_gb * POWER(1.05, 30/30), 2) as projected_size_gb, ROUND(db_data.used_gb * (POWER(1.05, 30/30) - 1), 2) as growth_gb, ROUND((POWER(1.05, 30/30) - 1) * 100, 2) as growth_pct FROM ( SELECT (SELECT SUM(bytes) / 1024 / 1024 / 1024 FROM dba_segments) as used_gb FROM dual ) db_data WHERE db_data.used_gb IS NOT NULL UNION ALL SELECT '&1 Days', 'Database Data', ROUND(db_data.used_gb, 2), ROUND(db_data.used_gb * POWER(1.05, &1/30), 2), ROUND(db_data.used_gb * (POWER(1.05, &1/30) - 1), 2), ROUND((POWER(1.05, &1/30) - 1) * 100, 2) FROM ( SELECT (SELECT SUM(bytes) / 1024 / 1024 / 1024 FROM dba_segments) as used_gb FROM dual ) db_data WHERE db_data.used_gb IS NOT NULL UNION ALL SELECT '&1 Days', 'Archive Logs', archive_data.daily_size_gb * &1, archive_data.daily_size_gb * &1 * 1.05, archive_data.daily_size_gb * &1 * 0.05, 5.0 FROM ( SELECT ROUND(NVL(SUM(blocks * block_size), 0) / 1024 / 1024 / 1024, 2) as daily_size_gb FROM v$archived_log WHERE first_time >= SYSDATE - 1 ) archive_data WHERE archive_data.daily_size_gb > 0)WHERE current_size_gb IS NOT NULLORDER BY CASE forecast_period WHEN '30 Days' THEN 1 ELSE 2 END, component;
-- ------------------------------------------------------------------------------------- CAPACITY RECOMMENDATIONS: Actionable scaling recommendations-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 8. CAPACITY PLANNING RECOMMENDATIONSPROMPT ================================================================================
COLUMN recommendation_type FORMAT A25COLUMN description FORMAT A60COLUMN timeframe FORMAT A15COLUMN priority FORMAT A8COLUMN estimated_cost FORMAT A15
SELECT recommendation_type, description, timeframe, priority, estimated_costFROM ( SELECT 'Storage Expansion' as recommendation_type, 'Add datafiles to tablespace: ' || tablespace_name as description, 'IMMEDIATE' as timeframe, 'HIGH' as priority, 'LOW' as estimated_cost FROM ( SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics WHERE used_percent > 90 AND ROWNUM <= 3 ) UNION ALL SELECT 'Archive Management', 'Implement archive log retention policy', '30 DAYS', 'MEDIUM', 'LOW' FROM dual WHERE (SELECT ROUND(NVL(SUM(blocks * block_size), 0) / 1024 / 1024 / 1024, 2) FROM v$archived_log WHERE first_time >= SYSDATE - 30) > 100 UNION ALL SELECT 'Memory Optimization', 'Consider increasing SGA size by 20%', '90 DAYS', 'MEDIUM', 'MEDIUM' FROM dual WHERE (SELECT ROUND(SUM(value) / 1024 / 1024 / 1024, 2) FROM v$sga) / (SELECT ROUND(value / 1024 / 1024 / 1024, 2) FROM v$parameter WHERE name = 'sga_max_size') > 0.8 UNION ALL SELECT 'Performance Scaling', 'Plan for connection pool optimization', '180 DAYS', 'LOW', 'MEDIUM' FROM dual WHERE (SELECT COUNT(*) FROM v$session WHERE type = 'USER') > 300)ORDER BY CASE priority WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END, CASE timeframe WHEN 'IMMEDIATE' THEN 1 WHEN '30 DAYS' THEN 2 WHEN '90 DAYS' THEN 3 WHEN '180 DAYS' THEN 4 ELSE 5 END;
-- ------------------------------------------------------------------------------------- RISK ASSESSMENT: Capacity-related risks and mitigation-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT 9. CAPACITY RISK ASSESSMENTPROMPT ================================================================================
COLUMN risk_category FORMAT A20COLUMN risk_description FORMAT A60COLUMN probability FORMAT A15COLUMN impact FORMAT A15COLUMN mitigation_strategy FORMAT A80
SELECT risk_category, risk_description, probability, impact, mitigation_strategyFROM ( SELECT 'Storage Capacity' as risk_category, 'Tablespace ' || tablespace_name || ' at ' || ROUND(used_percent, 2) || '% utilization' as risk_description, 'HIGH' as probability, 'HIGH' as impact, 'Immediate datafile addition or tablespace resize required' as mitigation_strategy FROM dba_tablespace_usage_metrics WHERE used_percent > 90 AND ROWNUM = 1 UNION ALL SELECT 'Archive Growth', 'High archive log generation rate detected', 'MEDIUM', 'MEDIUM', 'Implement archive log compression and review retention policies' FROM dual WHERE (SELECT ROUND(NVL(SUM(blocks * block_size), 0) / 1024 / 1024 / 1024, 2) FROM v$archived_log WHERE first_time >= SYSDATE - 1) > 10 UNION ALL SELECT 'Memory Pressure', 'SGA utilization approaching maximum capacity', 'LOW', 'HIGH', 'Monitor memory usage and plan for SGA increase in next maintenance window' FROM dual WHERE (SELECT ROUND(SUM(value) / 1024 / 1024 / 1024, 2) FROM v$sga) / (SELECT ROUND(value / 1024 / 1024 / 1024, 2) FROM v$parameter WHERE name = 'sga_max_size') > 0.85)ORDER BY CASE probability WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END, CASE impact WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END; -- ------------------------------------------------------------------------------------- COMPLETION: Capacity planning summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ================================================================================PROMPT CAPACITY PLANNING ANALYSIS COMPLETEDPROMPT ================================================================================PROMPT PROMPT Summary:PROMPT ---------PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Forecast Period: &1 daysPROMPT Analysis Level: &2PROMPT Start Time: &&DATE1 &&TIME1PROMPT Completion Time: COLUMN END_TIME NEW_VALUE END_TIME NOPRINTSELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as END_TIME FROM DUAL;PROMPT &&END_TIMEPROMPT Report File: &&REPORT_FILEPROMPT PROMPT Key Findings:PROMPT ------------PROMPT 1. Review HIGH priority recommendations immediatelyPROMPT 2. Monitor storage growth trends regularlyPROMPT 3. Plan infrastructure upgrades based on projectionsPROMPT 4. Implement risk mitigation strategies proactivelyPROMPT 5. Schedule regular capacity reviews quarterlyPROMPT PROMPT ================================================================================
-- Display report locationPROMPT Report has been saved to: &&REPORT_FILEPROMPT To view the report: TYPE &&REPORT_FILEPROMPT ================================================================================
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- Stop output redirectionSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------