schema_object_analysis.sql
-- ------------------------------------------------------------------------------------- File Name : schema_object_analysis.sql-- Author : Pierre Montbleau-- Description : Comprehensive analysis of schema objects including tables, indexes, and partitions-- Purpose : Schema object inventory, sizing analysis, and maintenance recommendations-- Call Syntax : @F:\DBA\Scripts\schema_object_analysis.sql (schema_name) (object_type) (min_size_mb)-- Parameters : schema_name - Name of schema to analyze (use '%' for all schemas)-- object_type - Type of objects to analyze (TABLE, INDEX, ALL)-- min_size_mb - Minimum object size in MB to include in report (default: 1)-- 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_schema_object_analysis_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Schema Object Analysis Script...PROMPT ==========================================PROMPT Schema Name: &1PROMPT Object Type: &2PROMPT Minimum Size: &3 MBPROMPT
-- Validate parametersDECLARE v_schema_exists NUMBER; v_object_type VARCHAR2(20) := UPPER('&2'); v_min_size NUMBER := NVL(TO_NUMBER('&3'), 1);BEGIN -- Validate schema exists if not using wildcard IF '&1' != '%' THEN SELECT COUNT(*) INTO v_schema_exists FROM dba_users WHERE username = UPPER('&1'); IF v_schema_exists = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Schema ' || UPPER('&1') || ' does not exist'); END IF; END IF; -- Validate object type IF v_object_type NOT IN ('TABLE', 'INDEX', 'ALL') THEN RAISE_APPLICATION_ERROR(-20002, 'Object type must be TABLE, INDEX, or ALL'); END IF; -- Validate minimum size IF v_min_size < 0 THEN RAISE_APPLICATION_ERROR(-20003, 'Minimum size cannot be negative'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for minimum size'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- SCHEMA SUMMARY: Overview of schema objects and total sizes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. SCHEMA OBJECT SUMMARYPROMPT =========================
COLUMN owner FORMAT A20COLUMN object_type FORMAT A15COLUMN object_count FORMAT 999,999COLUMN total_size_mb FORMAT 999,999,999COLUMN avg_size_mb FORMAT 999,999.99
SELECT owner, segment_type as object_type, COUNT(*) as object_count, ROUND(SUM(bytes) / 1024 / 1024, 2) as total_size_mb, ROUND(AVG(bytes) / 1024 / 1024, 2) as avg_size_mb, ROUND(MAX(bytes) / 1024 / 1024, 2) as max_size_mb, ROUND(MIN(bytes) / 1024 / 1024, 2) as min_size_mbFROM dba_segmentsWHERE owner = UPPER('ATTSTATS') AND (UPPER('ALL') = 'ALL' OR (UPPER('ALL') = 'TABLE' AND segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')) OR (UPPER('ALL') = 'INDEX' AND segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')))GROUP BY owner, segment_typeORDER BY total_size_mb DESC;
-- ------------------------------------------------------------------------------------- LARGEST OBJECTS: Identify largest objects in the schema-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. LARGEST OBJECTS (>&3 MB)PROMPT ============================
COLUMN segment_name FORMAT A40COLUMN segment_type FORMAT A15COLUMN tablespace_name FORMAT A20COLUMN size_mb FORMAT 999,999,999COLUMN partitions FORMAT 999
SELECT segment_name, segment_type, tablespace_name, ROUND(bytes / 1024 / 1024) as size_mb, (SELECT COUNT(*) FROM dba_segments s2 WHERE s2.owner = s1.owner AND s2.segment_name = s1.segment_name AND s2.segment_type LIKE 'TABLE%') as partitionsFROM dba_segments s1WHERE owner LIKE UPPER('&1') AND (UPPER('&2') = 'ALL' OR (UPPER('&2') = 'TABLE' AND segment_type LIKE 'TABLE%') OR (UPPER('&2') = 'INDEX' AND segment_type LIKE 'INDEX%')) AND bytes / 1024 / 1024 >= &3ORDER BY bytes DESC;
-- ------------------------------------------------------------------------------------- TABLE ANALYSIS: Detailed table information and statistics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. TABLE ANALYSISPROMPT ==================
COLUMN table_name FORMAT A30COLUMN num_rows FORMAT 999,999,999COLUMN avg_row_len FORMAT 999,999COLUMN last_analyzed FORMAT A20COLUMN compression FORMAT A10
SELECT table_name, num_rows, avg_row_len, TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as last_analyzed, compressionFROM dba_tablesWHERE owner LIKE UPPER('&1') AND (UPPER('&2') IN ('ALL', 'TABLE'))ORDER BY num_rows DESC NULLS LASTFETCH FIRST 20 ROWS ONLY;
-- ------------------------------------------------------------------------------------- INDEX ANALYSIS: Index usage and sizing information-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. INDEX ANALYSISPROMPT ==================
COLUMN index_name FORMAT A40COLUMN table_name FORMAT A40COLUMN uniqueness FORMAT A10COLUMN distinctiveness FORMAT 999.99COLUMN index_size_mb FORMAT 999,999
SELECT i.index_name, i.table_name, i.uniqueness, ROUND((i.distinct_keys / NULLIF(t.num_rows, 0)) * 100, 2) as distinctiveness, ROUND(s.bytes / 1024 / 1024) as index_size_mbFROM dba_indexes iJOIN dba_tables t ON i.owner = t.owner AND i.table_name = t.table_nameJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner LIKE UPPER('&1') AND (UPPER('&2') IN ('ALL', 'INDEX')) AND s.bytes / 1024 / 1024 >= &3ORDER BY s.bytes DESC;
-- ------------------------------------------------------------------------------------- PARTITION ANALYSIS: Partitioned object details-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. PARTITION ANALYSISPROMPT ======================
COLUMN partitioned_table FORMAT A30COLUMN partition_count FORMAT 999COLUMN total_size_mb FORMAT 999,999,999COLUMN largest_partition_mb FORMAT 999,999
SELECT segment_name as partitioned_table, COUNT(*) as partition_count, ROUND(SUM(bytes) / 1024 / 1024, 2) as total_size_mb, ROUND(MAX(bytes) / 1024 / 1024, 2) as largest_partition_mbFROM dba_segmentsWHERE owner = UPPER('ATTSTATS') AND segment_type IN ('TABLE PARTITION', 'TABLE SUBPARTITION')GROUP BY segment_nameHAVING SUM(bytes) / 1024 / 1024 >= 1ORDER BY total_size_mb DESC;
-- ------------------------------------------------------------------------------------- STORAGE RECOMMENDATIONS: Maintenance and optimization suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. STORAGE OPTIMIZATION RECOMMENDATIONSPROMPT ========================================
COLUMN recommendation FORMAT A60COLUMN object_name FORMAT A30COLUMN current_size_mb FORMAT 999,999COLUMN potential_savings_mb FORMAT 999,999
SELECT 'Consider partitioning large table: ' || segment_name as recommendation, segment_name as object_name, ROUND(bytes / 1024 / 1024) as current_size_mb, ROUND(bytes / 1024 / 1024 * 0.1) as potential_savings_mbFROM dba_segmentsWHERE owner LIKE UPPER('&1') AND segment_type = 'TABLE' AND bytes / 1024 / 1024 > 1000 AND NOT EXISTS ( SELECT 1 FROM dba_part_tables WHERE owner = dba_segments.owner AND table_name = dba_segments.segment_name )UNION ALLSELECT 'Evaluate unused large index: ' || segment_name as recommendation, segment_name as object_name, ROUND(bytes / 1024 / 1024) as current_size_mb, ROUND(bytes / 1024 / 1024 * 0.9) as potential_savings_mbFROM dba_segments sWHERE owner LIKE UPPER('&1') AND segment_type = 'INDEX' AND bytes / 1024 / 1024 > 100 AND NOT EXISTS ( SELECT 1 FROM dba_indexes i WHERE i.owner = s.owner AND i.index_name = s.segment_name AND i.distinct_keys > 0 )ORDER BY potential_savings_mb DESC;
-- ------------------------------------------------------------------------------------- OBJECT DEPENDENCIES: Key dependency relationships-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. KEY DEPENDENCY RELATIONSHIPSPROMPT ================================
COLUMN referenced_owner FORMAT A15COLUMN referenced_name FORMAT A30COLUMN referenced_type FORMAT A15COLUMN dependency_count FORMAT 999
SELECT referenced_owner, referenced_name, referenced_type, COUNT(*) as dependency_countFROM dba_dependenciesWHERE owner LIKE UPPER('&1') AND referenced_type IN ('TABLE', 'VIEW', 'PACKAGE')GROUP BY referenced_owner, referenced_name, referenced_typeHAVING COUNT(*) > 5ORDER BY dependency_count DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ==========================================PROMPT SCHEMA OBJECT ANALYSIS COMPLETEDPROMPT ==========================================PROMPT Schema Analyzed: &1PROMPT Object Type: &2PROMPT Minimum Size: &3 MBPROMPT PROMPT Summary:PROMPT - Review largest objects for optimization opportunitiesPROMPT - Consider partitioning recommendations for large tablesPROMPT - Evaluate index usage and potential savingsPROMPT
-- ------------------------------------------------------------------------------------- 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_schema_object_analysis_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Schema Object Analysis Script...PROMPT ==========================================PROMPT Schema Name: &1PROMPT Object Type: &2PROMPT Minimum Size: &3 MBPROMPT
-- Validate parametersDECLARE v_schema_exists NUMBER; v_object_type VARCHAR2(20) := UPPER('&2'); v_min_size NUMBER := NVL(TO_NUMBER('&3'), 1);BEGIN -- Validate schema exists if not using wildcard IF '&1' != '%' THEN SELECT COUNT(*) INTO v_schema_exists FROM dba_users WHERE username = UPPER('&1'); IF v_schema_exists = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Schema ' || UPPER('&1') || ' does not exist'); END IF; END IF; -- Validate object type IF v_object_type NOT IN ('TABLE', 'INDEX', 'ALL') THEN RAISE_APPLICATION_ERROR(-20002, 'Object type must be TABLE, INDEX, or ALL'); END IF; -- Validate minimum size IF v_min_size < 0 THEN RAISE_APPLICATION_ERROR(-20003, 'Minimum size cannot be negative'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for minimum size'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- SCHEMA SUMMARY: Overview of schema objects and total sizes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. SCHEMA OBJECT SUMMARYPROMPT =========================
COLUMN owner FORMAT A20COLUMN object_type FORMAT A15COLUMN object_count FORMAT 999,999COLUMN total_size_mb FORMAT 999,999,999COLUMN avg_size_mb FORMAT 999,999.99
SELECT owner, segment_type as object_type, COUNT(*) as object_count, ROUND(SUM(bytes) / 1024 / 1024, 2) as total_size_mb, ROUND(AVG(bytes) / 1024 / 1024, 2) as avg_size_mb, ROUND(MAX(bytes) / 1024 / 1024, 2) as max_size_mb, ROUND(MIN(bytes) / 1024 / 1024, 2) as min_size_mbFROM dba_segmentsWHERE owner = UPPER('ATTSTATS') AND (UPPER('ALL') = 'ALL' OR (UPPER('ALL') = 'TABLE' AND segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')) OR (UPPER('ALL') = 'INDEX' AND segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')))GROUP BY owner, segment_typeORDER BY total_size_mb DESC;
-- ------------------------------------------------------------------------------------- LARGEST OBJECTS: Identify largest objects in the schema-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. LARGEST OBJECTS (>&3 MB)PROMPT ============================
COLUMN segment_name FORMAT A40COLUMN segment_type FORMAT A15COLUMN tablespace_name FORMAT A20COLUMN size_mb FORMAT 999,999,999COLUMN partitions FORMAT 999
SELECT segment_name, segment_type, tablespace_name, ROUND(bytes / 1024 / 1024) as size_mb, (SELECT COUNT(*) FROM dba_segments s2 WHERE s2.owner = s1.owner AND s2.segment_name = s1.segment_name AND s2.segment_type LIKE 'TABLE%') as partitionsFROM dba_segments s1WHERE owner LIKE UPPER('&1') AND (UPPER('&2') = 'ALL' OR (UPPER('&2') = 'TABLE' AND segment_type LIKE 'TABLE%') OR (UPPER('&2') = 'INDEX' AND segment_type LIKE 'INDEX%')) AND bytes / 1024 / 1024 >= &3ORDER BY bytes DESC;
-- ------------------------------------------------------------------------------------- TABLE ANALYSIS: Detailed table information and statistics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. TABLE ANALYSISPROMPT ==================
COLUMN table_name FORMAT A30COLUMN num_rows FORMAT 999,999,999COLUMN avg_row_len FORMAT 999,999COLUMN last_analyzed FORMAT A20COLUMN compression FORMAT A10
SELECT table_name, num_rows, avg_row_len, TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as last_analyzed, compressionFROM dba_tablesWHERE owner LIKE UPPER('&1') AND (UPPER('&2') IN ('ALL', 'TABLE'))ORDER BY num_rows DESC NULLS LASTFETCH FIRST 20 ROWS ONLY;
-- ------------------------------------------------------------------------------------- INDEX ANALYSIS: Index usage and sizing information-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. INDEX ANALYSISPROMPT ==================
COLUMN index_name FORMAT A40COLUMN table_name FORMAT A40COLUMN uniqueness FORMAT A10COLUMN distinctiveness FORMAT 999.99COLUMN index_size_mb FORMAT 999,999
SELECT i.index_name, i.table_name, i.uniqueness, ROUND((i.distinct_keys / NULLIF(t.num_rows, 0)) * 100, 2) as distinctiveness, ROUND(s.bytes / 1024 / 1024) as index_size_mbFROM dba_indexes iJOIN dba_tables t ON i.owner = t.owner AND i.table_name = t.table_nameJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner LIKE UPPER('&1') AND (UPPER('&2') IN ('ALL', 'INDEX')) AND s.bytes / 1024 / 1024 >= &3ORDER BY s.bytes DESC;
-- ------------------------------------------------------------------------------------- PARTITION ANALYSIS: Partitioned object details-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. PARTITION ANALYSISPROMPT ======================
COLUMN partitioned_table FORMAT A30COLUMN partition_count FORMAT 999COLUMN total_size_mb FORMAT 999,999,999COLUMN largest_partition_mb FORMAT 999,999
SELECT segment_name as partitioned_table, COUNT(*) as partition_count, ROUND(SUM(bytes) / 1024 / 1024, 2) as total_size_mb, ROUND(MAX(bytes) / 1024 / 1024, 2) as largest_partition_mbFROM dba_segmentsWHERE owner = UPPER('ATTSTATS') AND segment_type IN ('TABLE PARTITION', 'TABLE SUBPARTITION')GROUP BY segment_nameHAVING SUM(bytes) / 1024 / 1024 >= 1ORDER BY total_size_mb DESC;
-- ------------------------------------------------------------------------------------- STORAGE RECOMMENDATIONS: Maintenance and optimization suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. STORAGE OPTIMIZATION RECOMMENDATIONSPROMPT ========================================
COLUMN recommendation FORMAT A60COLUMN object_name FORMAT A30COLUMN current_size_mb FORMAT 999,999COLUMN potential_savings_mb FORMAT 999,999
SELECT 'Consider partitioning large table: ' || segment_name as recommendation, segment_name as object_name, ROUND(bytes / 1024 / 1024) as current_size_mb, ROUND(bytes / 1024 / 1024 * 0.1) as potential_savings_mbFROM dba_segmentsWHERE owner LIKE UPPER('&1') AND segment_type = 'TABLE' AND bytes / 1024 / 1024 > 1000 AND NOT EXISTS ( SELECT 1 FROM dba_part_tables WHERE owner = dba_segments.owner AND table_name = dba_segments.segment_name )UNION ALLSELECT 'Evaluate unused large index: ' || segment_name as recommendation, segment_name as object_name, ROUND(bytes / 1024 / 1024) as current_size_mb, ROUND(bytes / 1024 / 1024 * 0.9) as potential_savings_mbFROM dba_segments sWHERE owner LIKE UPPER('&1') AND segment_type = 'INDEX' AND bytes / 1024 / 1024 > 100 AND NOT EXISTS ( SELECT 1 FROM dba_indexes i WHERE i.owner = s.owner AND i.index_name = s.segment_name AND i.distinct_keys > 0 )ORDER BY potential_savings_mb DESC;
-- ------------------------------------------------------------------------------------- OBJECT DEPENDENCIES: Key dependency relationships-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. KEY DEPENDENCY RELATIONSHIPSPROMPT ================================
COLUMN referenced_owner FORMAT A15COLUMN referenced_name FORMAT A30COLUMN referenced_type FORMAT A15COLUMN dependency_count FORMAT 999
SELECT referenced_owner, referenced_name, referenced_type, COUNT(*) as dependency_countFROM dba_dependenciesWHERE owner LIKE UPPER('&1') AND referenced_type IN ('TABLE', 'VIEW', 'PACKAGE')GROUP BY referenced_owner, referenced_name, referenced_typeHAVING COUNT(*) > 5ORDER BY dependency_count DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ==========================================PROMPT SCHEMA OBJECT ANALYSIS COMPLETEDPROMPT ==========================================PROMPT Schema Analyzed: &1PROMPT Object Type: &2PROMPT Minimum Size: &3 MBPROMPT PROMPT Summary:PROMPT - Review largest objects for optimization opportunitiesPROMPT - Consider partitioning recommendations for large tablesPROMPT - Evaluate index usage and potential savingsPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------