database_documentation_generator.sql
-- ------------------------------------------------------------------------------------- File Name : database_documentation_generator.sql-- Author : Pierre Montbleau-- Description : Comprehensive Oracle database documentation and reporting utility-- Purpose : Generate complete database documentation, data dictionary reports, and system documentation-- Call Syntax : @F:\DBA\Scripts\database_documentation_generator.sql (doc_type) (output_format) (detail_level)-- Parameters : doc_type - Type of documentation (SCHEMA, SYSTEM, SECURITY, PERFORMANCE, COMPLETE)-- output_format - Output format (TEXT, HTML, CSV, SUMMARY)-- detail_level - Detail level (BASIC, STANDARD, COMPREHENSIVE)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesCLEAR 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_documentation_generator_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || UPPER('&1') || '_' || UPPER('&2') || '_' || UPPER('&3') || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Start output redirectionSPOOL "&&output_filename"
PROMPT Starting Database Documentation Generator...PROMPT ============================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Documentation Type: &1PROMPT Output Format: &2PROMPT Detail Level: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT Output File: &&output_filenamePROMPT
-- Validate parametersDECLARE v_doc_type VARCHAR2(20) := UPPER('&1'); v_output_format VARCHAR2(20) := UPPER('&2'); v_detail_level VARCHAR2(20) := UPPER('&3');BEGIN IF v_doc_type NOT IN ('SCHEMA', 'SYSTEM', 'SECURITY', 'PERFORMANCE', 'COMPLETE') THEN RAISE_APPLICATION_ERROR(-20001, 'Documentation type must be SCHEMA, SYSTEM, SECURITY, PERFORMANCE, or COMPLETE'); END IF; IF v_output_format NOT IN ('TEXT', 'HTML', 'CSV', 'SUMMARY') THEN RAISE_APPLICATION_ERROR(-20002, 'Output format must be TEXT, HTML, CSV, or SUMMARY'); END IF; IF v_detail_level NOT IN ('BASIC', 'STANDARD', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20003, 'Detail level must be BASIC, STANDARD, or COMPREHENSIVE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful'); DBMS_OUTPUT.PUT_LINE('Generating ' || v_doc_type || ' documentation in ' || v_output_format || ' format'); DBMS_OUTPUT.PUT_LINE('Output will be saved to: &&output_filename');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DATABASE OVERVIEW: Basic database information and configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. DATABASE OVERVIEW AND CONFIGURATIONPROMPT =======================================
COLUMN database_info FORMAT A40COLUMN current_value FORMAT A50COLUMN description FORMAT A60
SELECT 'Database Name' as database_info, name as current_value, 'Primary database identifier' as descriptionFROM v$databaseUNION ALLSELECT 'Instance Name', instance_name, 'Current instance identifier'FROM v$instanceUNION ALLSELECT 'Oracle Version', version, 'Database version and release information'FROM v$instanceUNION ALLSELECT 'Host Name', host_name, 'Server hostname'FROM v$instanceUNION ALLSELECT 'Startup Time', TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS'), 'Database instance startup timestamp'FROM v$instanceUNION ALLSELECT 'Archive Log Mode', log_mode, 'Database archiving mode'FROM v$databaseUNION ALLSELECT 'Database Role', database_role, 'Database role (Primary/Standby)'FROM v$databaseUNION ALLSELECT 'Block Size', (SELECT value FROM v$parameter WHERE name = 'db_block_size'), 'Database block size in bytes'FROM dualUNION ALLSELECT 'Character Set', (SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET'), 'Database character set'FROM dualORDER BY 1;
-- ------------------------------------------------------------------------------------- STORAGE CONFIGURATION: Tablespace and datafile documentation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. STORAGE CONFIGURATION DOCUMENTATIONPROMPT =======================================
COLUMN tablespace_name FORMAT A35COLUMN status FORMAT A10COLUMN contents FORMAT A10COLUMN extent_management FORMAT A15COLUMN allocation_type FORMAT A15COLUMN total_size_gb FORMAT 999,999.99COLUMN used_pct FORMAT 999.99
SELECT dt.tablespace_name, dt.status, dt.contents, dt.extent_management, dt.allocation_type, ROUND(SUM(df.bytes) / 1024 / 1024 / 1024, 2) as total_size_gb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) as used_pctFROM dba_tablespaces dtLEFT JOIN dba_data_files df ON dt.tablespace_name = df.tablespace_nameLEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_idWHERE UPPER('&1') IN ('SYSTEM', 'COMPLETE') OR (UPPER('&1') = 'SCHEMA' AND dt.contents = 'PERMANENT')GROUP BY dt.tablespace_name, dt.status, dt.contents, dt.extent_management, dt.allocation_typeORDER BY total_size_gb DESC;
-- ------------------------------------------------------------------------------------- SCHEMA INVENTORY: User schemas and object counts-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. SCHEMA INVENTORY AND OBJECT COUNTSPROMPT ======================================
COLUMN owner FORMAT A30COLUMN object_type FORMAT A20COLUMN object_count FORMAT 999,999COLUMN total_size_gb FORMAT 999,999.99COLUMN last_ddl_time FORMAT A20
SELECT owner, object_type, COUNT(*) as object_count, ROUND(SUM(NVL((SELECT SUM(bytes) FROM dba_segments ds WHERE ds.owner = do.owner AND ds.segment_name = do.object_name), 0)) / 1024 / 1024 / 1024, 2) as total_size_gb, TO_CHAR(MAX(last_ddl_time), 'YYYY-MM-DD') as last_ddl_timeFROM dba_objects doWHERE owner NOT IN ('SYS', 'SYSTEM', 'XS$NULL') AND owner NOT LIKE 'APEX%' AND object_type IN ('TABLE', 'INDEX', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'SEQUENCE') AND (UPPER('&1') IN ('SCHEMA', 'COMPLETE') OR (UPPER('&1') = 'SECURITY' AND object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')))GROUP BY owner, object_typeHAVING COUNT(*) > 0ORDER BY owner, object_count DESC;
-- ------------------------------------------------------------------------------------- TABLE DOCUMENTATION: Detailed table information-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. TABLE DOCUMENTATION AND METADATAPROMPT ====================================
COLUMN table_name FORMAT A35COLUMN owner FORMAT A25COLUMN num_rows FORMAT 999,999,999COLUMN avg_row_len FORMAT 999,999COLUMN tablespace_name FORMAT A30COLUMN last_analyzed FORMAT A20COLUMN partitioning_type FORMAT A15
SELECT table_name, owner, num_rows, avg_row_len, tablespace_name, TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as last_analyzed, partitionedFROM dba_tablesWHERE owner IN (select username from dba_users where ORACLE_MAINTAINED = 'N') AND owner not in ('SPOTLIGHT','SONDAGE') AND owner not like ('DWMASTER%') AND owner not like ('%ORACLE%') AND (UPPER('COMPLETE') IN ('SCHEMA', 'COMPLETE') OR (UPPER('COMPREHENSIVE') = 'COMPREHENSIVE' AND ROWNUM <= 20)) AND (UPPER('COMPREHENSIVE') != 'BASIC' OR num_rows > 1000)ORDER BY owner, table_name, num_rows DESC NULLS LAST;
-- ------------------------------------------------------------------------------------- COLUMN DOCUMENTATION: Table column definitions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. COLUMN DEFINITIONS AND DATA TYPESPROMPT =====================================
COLUMN table_name FORMAT A35COLUMN column_name FORMAT A35COLUMN data_type FORMAT A20COLUMN nullable FORMAT A8COLUMN data_default FORMAT A20COLUMN comments FORMAT A50
SELECT tc.table_name, tc.column_name, tc.data_type || CASE WHEN tc.data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR') THEN '(' || tc.data_length || ')' WHEN tc.data_type = 'NUMBER' AND tc.data_precision IS NOT NULL THEN '(' || tc.data_precision || CASE WHEN tc.data_scale > 0 THEN ',' || tc.data_scale END || ')' WHEN tc.data_type IN ('TIMESTAMP', 'TIMESTAMP WITH TIME ZONE', 'TIMESTAMP WITH LOCAL TIME ZONE') THEN '(' || tc.data_scale || ')' ELSE '' END as data_type, tc.nullable, '-- LONG type' as data_default, SUBSTR(cc.comments, 1, 50) as commentsFROM dba_tab_columns tcLEFT JOIN dba_col_comments cc ON tc.owner = cc.owner AND tc.table_name = cc.table_name AND tc.column_name = cc.column_nameWHERE tc.owner IN (select username from dba_users where ORACLE_MAINTAINED = 'N') AND tc.owner not like ('DWMASTER%') AND (UPPER('COMPLETE') IN ('SCHEMA', 'COMPLETE') OR (UPPER('COMPREHENSIVE') = 'COMPREHENSIVE' AND ROWNUM <= 50))ORDER BY tc.owner, tc.table_name, tc.column_id;
-- ------------------------------------------------------------------------------------- INDEX DOCUMENTATION: Index definitions and usage-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. INDEX DOCUMENTATION AND USAGEPROMPT =================================
COLUMN index_name FORMAT A55COLUMN table_name FORMAT A35COLUMN uniqueness FORMAT A10COLUMN index_type FORMAT A25COLUMN status FORMAT A10COLUMN distinct_keys FORMAT 999,999,999COLUMN leaf_blocks FORMAT 999,999
SELECT owner, index_name, table_name, uniqueness, index_type, status, distinct_keys, leaf_blocksFROM dba_indexesWHERE owner IN (select username from dba_users where ORACLE_MAINTAINED = 'N') AND owner not like ('DWMASTER%') AND (UPPER('&1') IN ('SCHEMA', 'COMPLETE', 'PERFORMANCE') OR (UPPER('&3') = 'COMPREHENSIVE' AND ROWNUM <= 20))ORDER BY owner,table_name, index_name;
-- ------------------------------------------------------------------------------------- SECURITY DOCUMENTATION: User privileges and roles-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. SECURITY AND PRIVILEGE DOCUMENTATIONPROMPT =======================================
COLUMN username FORMAT A20COLUMN account_status FORMAT A20COLUMN default_tablespace FORMAT A25COLUMN profile FORMAT A25COLUMN granted_role FORMAT A30COLUMN admin_option FORMAT A5
SELECT du.username, du.account_status, du.default_tablespace, du.profile, drp.granted_role, drp.admin_optionFROM dba_users duLEFT JOIN dba_role_privs drp ON du.username = drp.granteeWHERE du.username NOT IN ('SYS', 'SYSTEM') AND du.oracle_maintained = 'N' AND du.username not like ('DWMASTER%') AND (UPPER('&1') IN ('SECURITY', 'COMPLETE') OR (UPPER('&3') != 'BASIC'))ORDER BY du.username, drp.granted_role;
-- ------------------------------------------------------------------------------------- PERFORMANCE METRICS: Database performance characteristics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. PERFORMANCE CHARACTERISTICSPROMPT ===============================
COLUMN metric_name FORMAT A40COLUMN value FORMAT 999,999,999.99COLUMN metric_unit FORMAT A20COLUMN description FORMAT A50
SELECT metric_name, value, metric_unit, CASE metric_name WHEN 'Database CPU Time Ratio' THEN 'Percentage of time spent on CPU operations' WHEN 'Database Wait Time Ratio' THEN 'Percentage of time spent waiting' WHEN 'User Commits Per Sec' THEN 'Number of user commits per second' WHEN 'Physical Reads Per Sec' THEN 'Physical disk reads per second' WHEN 'Current Logons Count' THEN 'Current number of database sessions' ELSE 'Performance metric' END as descriptionFROM v$sysmetricWHERE group_id = 2 AND metric_name IN ( 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'User Commits Per Sec', 'User Transactions Per Sec', 'Physical Reads Per Sec', 'Current Logons Count' ) AND UPPER('&1') IN ('PERFORMANCE', 'COMPLETE')ORDER BY metric_name;
-- ------------------------------------------------------------------------------------- DATABASE PARAMETERS: Key initialization parameters-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. DATABASE PARAMETERS DOCUMENTATIONPROMPT =====================================
COLUMN parameter_name FORMAT A35COLUMN current_value FORMAT A30COLUMN description FORMAT A70COLUMN is_default FORMAT A10
SELECT name as parameter_name, value as current_value, description, CASE WHEN isdefault = 'TRUE' THEN 'DEFAULT' ELSE 'MODIFIED' END as is_defaultFROM v$parameterWHERE name IN ( 'db_name', 'db_block_size', 'sga_max_size', 'sga_target', 'pga_aggregate_target', 'processes', 'sessions', 'open_cursors', 'compatible', 'memory_target', 'db_recovery_file_dest', 'db_recovery_file_dest_size') AND UPPER('&1') IN ('SYSTEM', 'COMPLETE')ORDER BY name;
-- ------------------------------------------------------------------------------------- DATABASE LINKS AND DIRECTORY OBJECTS: External references-- -----------------------------------------------------------------------------------
PROMPT PROMPT 10. EXTERNAL REFERENCES DOCUMENTATIONPROMPT ======================================
COLUMN object_type FORMAT A15COLUMN object_name FORMAT A30COLUMN owner FORMAT A15COLUMN target_info FORMAT A100COLUMN created FORMAT A12
SELECT 'DATABASE_LINK' as object_type, db_link as object_name, owner, TO_CHAR(created, 'YYYY-MM-DD') as created, username || '@' || host as target_infoFROM dba_db_linksWHERE UPPER('COMPLETE') IN ('SYSTEM', 'COMPLETE')UNION ALLSELECT 'DIRECTORY', directory_name, owner, directory_path, NULL as created -- DBA_DIRECTORIES doesn't have CREATED columnFROM dba_directoriesWHERE UPPER('COMPLETE') IN ('SYSTEM', 'COMPLETE')ORDER BY object_type, owner, object_name;
-- ------------------------------------------------------------------------------------- DOCUMENTATION SUMMARY: Consolidated documentation metrics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 11. DOCUMENTATION SUMMARY METRICSPROMPT ==================================
COLUMN documentation_category FORMAT A30COLUMN metric FORMAT A40COLUMN count_value FORMAT 999,999,999COLUMN size_gb FORMAT 999,999.99
SELECT 'SCHEMA DOCUMENTATION' as documentation_category, 'Total User Schemas' as metric, COUNT(DISTINCT username) as count_value, NULL as size_gbFROM dba_usersWHERE oracle_maintained = 'N'UNION ALLSELECT 'SCHEMA DOCUMENTATION', 'Total Database Objects', COUNT(*), NULLFROM dba_objectsWHERE owner NOT IN ('SYS', 'SYSTEM', 'XS$NULL', 'CTXSYS', 'MDSYS', 'ORDDATA', 'OLAPSYS', 'EXFSYS')UNION ALLSELECT 'STORAGE DOCUMENTATION', 'Total Tablespaces', COUNT(*), NULLFROM dba_tablespacesWHERE contents != 'TEMPORARY'UNION ALLSELECT 'STORAGE DOCUMENTATION', 'Total Database Size (GB)', NULL, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2)FROM dba_segmentsUNION ALLSELECT 'SECURITY DOCUMENTATION', 'Total Database Users', COUNT(*), NULLFROM dba_usersWHERE oracle_maintained = 'N'UNION ALLSELECT 'PERFORMANCE DOCUMENTATION', 'Active Sessions', COUNT(*), NULLFROM v$sessionWHERE status = 'ACTIVE' AND type = 'USER'ORDER BY 1, 2;
-- ------------------------------------------------------------------------------------- DOCUMENTATION GENERATION: Generate formatted documentation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 12. DOCUMENTATION GENERATION STATUSPROMPT ====================================
DECLARE v_doc_type VARCHAR2(20) := UPPER('&1'); v_output_format VARCHAR2(20) := UPPER('&2'); v_detail_level VARCHAR2(20) := UPPER('&3'); v_doc_timestamp VARCHAR2(20) := TO_CHAR(SYSDATE, 'YYYY-MM-DD_HH24MISS');BEGIN DBMS_OUTPUT.PUT_LINE('Documentation Generation Summary'); DBMS_OUTPUT.PUT_LINE('================================'); DBMS_OUTPUT.PUT_LINE('Documentation Type: ' || v_doc_type); DBMS_OUTPUT.PUT_LINE('Output Format: ' || v_output_format); DBMS_OUTPUT.PUT_LINE('Detail Level: ' || v_detail_level); DBMS_OUTPUT.PUT_LINE('Generation Time: ' || v_doc_timestamp); DBMS_OUTPUT.PUT_LINE('Output File: &&output_filename'); DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Sections Generated:'); DBMS_OUTPUT.PUT_LINE('- Database Overview and Configuration'); DBMS_OUTPUT.PUT_LINE('- Storage Configuration'); DBMS_OUTPUT.PUT_LINE('- Schema Inventory');
IF v_doc_type IN ('SCHEMA', 'COMPLETE') THEN DBMS_OUTPUT.PUT_LINE('- Table Documentation'); DBMS_OUTPUT.PUT_LINE('- Column Definitions'); DBMS_OUTPUT.PUT_LINE('- Index Documentation'); END IF;
IF v_doc_type IN ('SECURITY', 'COMPLETE') THEN DBMS_OUTPUT.PUT_LINE('- Security and Privileges'); END IF;
IF v_doc_type IN ('PERFORMANCE', 'COMPLETE') THEN DBMS_OUTPUT.PUT_LINE('- Performance Characteristics'); END IF;
IF v_doc_type IN ('SYSTEM', 'COMPLETE') THEN DBMS_OUTPUT.PUT_LINE('- Database Parameters'); DBMS_OUTPUT.PUT_LINE('- External References'); END IF;
DBMS_OUTPUT.PUT_LINE('- Documentation Summary'); DBMS_OUTPUT.PUT_LINE(''); -- Show format-specific information DBMS_OUTPUT.PUT_LINE('Output Information:'); DBMS_OUTPUT.PUT_LINE('- Output saved to: &&output_filename'); DBMS_OUTPUT.PUT_LINE('- Generation completed successfully'); IF v_output_format IN ('HTML', 'CSV') THEN DBMS_OUTPUT.PUT_LINE('- Note: ' || v_output_format || ' format would require file output'); DBMS_OUTPUT.PUT_LINE(' Implement using UTL_FILE or external scripting for file generation'); END IF;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error during documentation generation: ' || SQLERRM);END;/
-- ------------------------------------------------------------------------------------- COMPLETION: Documentation generation summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ============================================PROMPT DATABASE DOCUMENTATION GENERATION COMPLETEDPROMPT ============================================PROMPT Documentation Type: &1PROMPT Output Format: &2PROMPT Detail Level: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT Output File: &&output_filenamePROMPT PROMPT Documentation Summary:PROMPT - Comprehensive database inventory generatedPROMPT - Schema and object documentation completePROMPT - Security and configuration details documentedPROMPT - Performance characteristics capturedPROMPT - All output saved to: &&output_filenamePROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- Stop output redirectionSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesCLEAR 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_documentation_generator_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || UPPER('&1') || '_' || UPPER('&2') || '_' || UPPER('&3') || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Start output redirectionSPOOL "&&output_filename"
PROMPT Starting Database Documentation Generator...PROMPT ============================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Documentation Type: &1PROMPT Output Format: &2PROMPT Detail Level: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT Output File: &&output_filenamePROMPT
-- Validate parametersDECLARE v_doc_type VARCHAR2(20) := UPPER('&1'); v_output_format VARCHAR2(20) := UPPER('&2'); v_detail_level VARCHAR2(20) := UPPER('&3');BEGIN IF v_doc_type NOT IN ('SCHEMA', 'SYSTEM', 'SECURITY', 'PERFORMANCE', 'COMPLETE') THEN RAISE_APPLICATION_ERROR(-20001, 'Documentation type must be SCHEMA, SYSTEM, SECURITY, PERFORMANCE, or COMPLETE'); END IF; IF v_output_format NOT IN ('TEXT', 'HTML', 'CSV', 'SUMMARY') THEN RAISE_APPLICATION_ERROR(-20002, 'Output format must be TEXT, HTML, CSV, or SUMMARY'); END IF; IF v_detail_level NOT IN ('BASIC', 'STANDARD', 'COMPREHENSIVE') THEN RAISE_APPLICATION_ERROR(-20003, 'Detail level must be BASIC, STANDARD, or COMPREHENSIVE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful'); DBMS_OUTPUT.PUT_LINE('Generating ' || v_doc_type || ' documentation in ' || v_output_format || ' format'); DBMS_OUTPUT.PUT_LINE('Output will be saved to: &&output_filename');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DATABASE OVERVIEW: Basic database information and configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. DATABASE OVERVIEW AND CONFIGURATIONPROMPT =======================================
COLUMN database_info FORMAT A40COLUMN current_value FORMAT A50COLUMN description FORMAT A60
SELECT 'Database Name' as database_info, name as current_value, 'Primary database identifier' as descriptionFROM v$databaseUNION ALLSELECT 'Instance Name', instance_name, 'Current instance identifier'FROM v$instanceUNION ALLSELECT 'Oracle Version', version, 'Database version and release information'FROM v$instanceUNION ALLSELECT 'Host Name', host_name, 'Server hostname'FROM v$instanceUNION ALLSELECT 'Startup Time', TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS'), 'Database instance startup timestamp'FROM v$instanceUNION ALLSELECT 'Archive Log Mode', log_mode, 'Database archiving mode'FROM v$databaseUNION ALLSELECT 'Database Role', database_role, 'Database role (Primary/Standby)'FROM v$databaseUNION ALLSELECT 'Block Size', (SELECT value FROM v$parameter WHERE name = 'db_block_size'), 'Database block size in bytes'FROM dualUNION ALLSELECT 'Character Set', (SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET'), 'Database character set'FROM dualORDER BY 1;
-- ------------------------------------------------------------------------------------- STORAGE CONFIGURATION: Tablespace and datafile documentation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. STORAGE CONFIGURATION DOCUMENTATIONPROMPT =======================================
COLUMN tablespace_name FORMAT A35COLUMN status FORMAT A10COLUMN contents FORMAT A10COLUMN extent_management FORMAT A15COLUMN allocation_type FORMAT A15COLUMN total_size_gb FORMAT 999,999.99COLUMN used_pct FORMAT 999.99
SELECT dt.tablespace_name, dt.status, dt.contents, dt.extent_management, dt.allocation_type, ROUND(SUM(df.bytes) / 1024 / 1024 / 1024, 2) as total_size_gb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) as used_pctFROM dba_tablespaces dtLEFT JOIN dba_data_files df ON dt.tablespace_name = df.tablespace_nameLEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_idWHERE UPPER('&1') IN ('SYSTEM', 'COMPLETE') OR (UPPER('&1') = 'SCHEMA' AND dt.contents = 'PERMANENT')GROUP BY dt.tablespace_name, dt.status, dt.contents, dt.extent_management, dt.allocation_typeORDER BY total_size_gb DESC;
-- ------------------------------------------------------------------------------------- SCHEMA INVENTORY: User schemas and object counts-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. SCHEMA INVENTORY AND OBJECT COUNTSPROMPT ======================================
COLUMN owner FORMAT A30COLUMN object_type FORMAT A20COLUMN object_count FORMAT 999,999COLUMN total_size_gb FORMAT 999,999.99COLUMN last_ddl_time FORMAT A20
SELECT owner, object_type, COUNT(*) as object_count, ROUND(SUM(NVL((SELECT SUM(bytes) FROM dba_segments ds WHERE ds.owner = do.owner AND ds.segment_name = do.object_name), 0)) / 1024 / 1024 / 1024, 2) as total_size_gb, TO_CHAR(MAX(last_ddl_time), 'YYYY-MM-DD') as last_ddl_timeFROM dba_objects doWHERE owner NOT IN ('SYS', 'SYSTEM', 'XS$NULL') AND owner NOT LIKE 'APEX%' AND object_type IN ('TABLE', 'INDEX', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'SEQUENCE') AND (UPPER('&1') IN ('SCHEMA', 'COMPLETE') OR (UPPER('&1') = 'SECURITY' AND object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')))GROUP BY owner, object_typeHAVING COUNT(*) > 0ORDER BY owner, object_count DESC;
-- ------------------------------------------------------------------------------------- TABLE DOCUMENTATION: Detailed table information-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. TABLE DOCUMENTATION AND METADATAPROMPT ====================================
COLUMN table_name FORMAT A35COLUMN owner FORMAT A25COLUMN num_rows FORMAT 999,999,999COLUMN avg_row_len FORMAT 999,999COLUMN tablespace_name FORMAT A30COLUMN last_analyzed FORMAT A20COLUMN partitioning_type FORMAT A15
SELECT table_name, owner, num_rows, avg_row_len, tablespace_name, TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as last_analyzed, partitionedFROM dba_tablesWHERE owner IN (select username from dba_users where ORACLE_MAINTAINED = 'N') AND owner not in ('SPOTLIGHT','SONDAGE') AND owner not like ('DWMASTER%') AND owner not like ('%ORACLE%') AND (UPPER('COMPLETE') IN ('SCHEMA', 'COMPLETE') OR (UPPER('COMPREHENSIVE') = 'COMPREHENSIVE' AND ROWNUM <= 20)) AND (UPPER('COMPREHENSIVE') != 'BASIC' OR num_rows > 1000)ORDER BY owner, table_name, num_rows DESC NULLS LAST;
-- ------------------------------------------------------------------------------------- COLUMN DOCUMENTATION: Table column definitions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. COLUMN DEFINITIONS AND DATA TYPESPROMPT =====================================
COLUMN table_name FORMAT A35COLUMN column_name FORMAT A35COLUMN data_type FORMAT A20COLUMN nullable FORMAT A8COLUMN data_default FORMAT A20COLUMN comments FORMAT A50
SELECT tc.table_name, tc.column_name, tc.data_type || CASE WHEN tc.data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR') THEN '(' || tc.data_length || ')' WHEN tc.data_type = 'NUMBER' AND tc.data_precision IS NOT NULL THEN '(' || tc.data_precision || CASE WHEN tc.data_scale > 0 THEN ',' || tc.data_scale END || ')' WHEN tc.data_type IN ('TIMESTAMP', 'TIMESTAMP WITH TIME ZONE', 'TIMESTAMP WITH LOCAL TIME ZONE') THEN '(' || tc.data_scale || ')' ELSE '' END as data_type, tc.nullable, '-- LONG type' as data_default, SUBSTR(cc.comments, 1, 50) as commentsFROM dba_tab_columns tcLEFT JOIN dba_col_comments cc ON tc.owner = cc.owner AND tc.table_name = cc.table_name AND tc.column_name = cc.column_nameWHERE tc.owner IN (select username from dba_users where ORACLE_MAINTAINED = 'N') AND tc.owner not like ('DWMASTER%') AND (UPPER('COMPLETE') IN ('SCHEMA', 'COMPLETE') OR (UPPER('COMPREHENSIVE') = 'COMPREHENSIVE' AND ROWNUM <= 50))ORDER BY tc.owner, tc.table_name, tc.column_id;
-- ------------------------------------------------------------------------------------- INDEX DOCUMENTATION: Index definitions and usage-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. INDEX DOCUMENTATION AND USAGEPROMPT =================================
COLUMN index_name FORMAT A55COLUMN table_name FORMAT A35COLUMN uniqueness FORMAT A10COLUMN index_type FORMAT A25COLUMN status FORMAT A10COLUMN distinct_keys FORMAT 999,999,999COLUMN leaf_blocks FORMAT 999,999
SELECT owner, index_name, table_name, uniqueness, index_type, status, distinct_keys, leaf_blocksFROM dba_indexesWHERE owner IN (select username from dba_users where ORACLE_MAINTAINED = 'N') AND owner not like ('DWMASTER%') AND (UPPER('&1') IN ('SCHEMA', 'COMPLETE', 'PERFORMANCE') OR (UPPER('&3') = 'COMPREHENSIVE' AND ROWNUM <= 20))ORDER BY owner,table_name, index_name;
-- ------------------------------------------------------------------------------------- SECURITY DOCUMENTATION: User privileges and roles-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. SECURITY AND PRIVILEGE DOCUMENTATIONPROMPT =======================================
COLUMN username FORMAT A20COLUMN account_status FORMAT A20COLUMN default_tablespace FORMAT A25COLUMN profile FORMAT A25COLUMN granted_role FORMAT A30COLUMN admin_option FORMAT A5
SELECT du.username, du.account_status, du.default_tablespace, du.profile, drp.granted_role, drp.admin_optionFROM dba_users duLEFT JOIN dba_role_privs drp ON du.username = drp.granteeWHERE du.username NOT IN ('SYS', 'SYSTEM') AND du.oracle_maintained = 'N' AND du.username not like ('DWMASTER%') AND (UPPER('&1') IN ('SECURITY', 'COMPLETE') OR (UPPER('&3') != 'BASIC'))ORDER BY du.username, drp.granted_role;
-- ------------------------------------------------------------------------------------- PERFORMANCE METRICS: Database performance characteristics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. PERFORMANCE CHARACTERISTICSPROMPT ===============================
COLUMN metric_name FORMAT A40COLUMN value FORMAT 999,999,999.99COLUMN metric_unit FORMAT A20COLUMN description FORMAT A50
SELECT metric_name, value, metric_unit, CASE metric_name WHEN 'Database CPU Time Ratio' THEN 'Percentage of time spent on CPU operations' WHEN 'Database Wait Time Ratio' THEN 'Percentage of time spent waiting' WHEN 'User Commits Per Sec' THEN 'Number of user commits per second' WHEN 'Physical Reads Per Sec' THEN 'Physical disk reads per second' WHEN 'Current Logons Count' THEN 'Current number of database sessions' ELSE 'Performance metric' END as descriptionFROM v$sysmetricWHERE group_id = 2 AND metric_name IN ( 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'User Commits Per Sec', 'User Transactions Per Sec', 'Physical Reads Per Sec', 'Current Logons Count' ) AND UPPER('&1') IN ('PERFORMANCE', 'COMPLETE')ORDER BY metric_name;
-- ------------------------------------------------------------------------------------- DATABASE PARAMETERS: Key initialization parameters-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. DATABASE PARAMETERS DOCUMENTATIONPROMPT =====================================
COLUMN parameter_name FORMAT A35COLUMN current_value FORMAT A30COLUMN description FORMAT A70COLUMN is_default FORMAT A10
SELECT name as parameter_name, value as current_value, description, CASE WHEN isdefault = 'TRUE' THEN 'DEFAULT' ELSE 'MODIFIED' END as is_defaultFROM v$parameterWHERE name IN ( 'db_name', 'db_block_size', 'sga_max_size', 'sga_target', 'pga_aggregate_target', 'processes', 'sessions', 'open_cursors', 'compatible', 'memory_target', 'db_recovery_file_dest', 'db_recovery_file_dest_size') AND UPPER('&1') IN ('SYSTEM', 'COMPLETE')ORDER BY name;
-- ------------------------------------------------------------------------------------- DATABASE LINKS AND DIRECTORY OBJECTS: External references-- -----------------------------------------------------------------------------------
PROMPT PROMPT 10. EXTERNAL REFERENCES DOCUMENTATIONPROMPT ======================================
COLUMN object_type FORMAT A15COLUMN object_name FORMAT A30COLUMN owner FORMAT A15COLUMN target_info FORMAT A100COLUMN created FORMAT A12
SELECT 'DATABASE_LINK' as object_type, db_link as object_name, owner, TO_CHAR(created, 'YYYY-MM-DD') as created, username || '@' || host as target_infoFROM dba_db_linksWHERE UPPER('COMPLETE') IN ('SYSTEM', 'COMPLETE')UNION ALLSELECT 'DIRECTORY', directory_name, owner, directory_path, NULL as created -- DBA_DIRECTORIES doesn't have CREATED columnFROM dba_directoriesWHERE UPPER('COMPLETE') IN ('SYSTEM', 'COMPLETE')ORDER BY object_type, owner, object_name;
-- ------------------------------------------------------------------------------------- DOCUMENTATION SUMMARY: Consolidated documentation metrics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 11. DOCUMENTATION SUMMARY METRICSPROMPT ==================================
COLUMN documentation_category FORMAT A30COLUMN metric FORMAT A40COLUMN count_value FORMAT 999,999,999COLUMN size_gb FORMAT 999,999.99
SELECT 'SCHEMA DOCUMENTATION' as documentation_category, 'Total User Schemas' as metric, COUNT(DISTINCT username) as count_value, NULL as size_gbFROM dba_usersWHERE oracle_maintained = 'N'UNION ALLSELECT 'SCHEMA DOCUMENTATION', 'Total Database Objects', COUNT(*), NULLFROM dba_objectsWHERE owner NOT IN ('SYS', 'SYSTEM', 'XS$NULL', 'CTXSYS', 'MDSYS', 'ORDDATA', 'OLAPSYS', 'EXFSYS')UNION ALLSELECT 'STORAGE DOCUMENTATION', 'Total Tablespaces', COUNT(*), NULLFROM dba_tablespacesWHERE contents != 'TEMPORARY'UNION ALLSELECT 'STORAGE DOCUMENTATION', 'Total Database Size (GB)', NULL, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2)FROM dba_segmentsUNION ALLSELECT 'SECURITY DOCUMENTATION', 'Total Database Users', COUNT(*), NULLFROM dba_usersWHERE oracle_maintained = 'N'UNION ALLSELECT 'PERFORMANCE DOCUMENTATION', 'Active Sessions', COUNT(*), NULLFROM v$sessionWHERE status = 'ACTIVE' AND type = 'USER'ORDER BY 1, 2;
-- ------------------------------------------------------------------------------------- DOCUMENTATION GENERATION: Generate formatted documentation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 12. DOCUMENTATION GENERATION STATUSPROMPT ====================================
DECLARE v_doc_type VARCHAR2(20) := UPPER('&1'); v_output_format VARCHAR2(20) := UPPER('&2'); v_detail_level VARCHAR2(20) := UPPER('&3'); v_doc_timestamp VARCHAR2(20) := TO_CHAR(SYSDATE, 'YYYY-MM-DD_HH24MISS');BEGIN DBMS_OUTPUT.PUT_LINE('Documentation Generation Summary'); DBMS_OUTPUT.PUT_LINE('================================'); DBMS_OUTPUT.PUT_LINE('Documentation Type: ' || v_doc_type); DBMS_OUTPUT.PUT_LINE('Output Format: ' || v_output_format); DBMS_OUTPUT.PUT_LINE('Detail Level: ' || v_detail_level); DBMS_OUTPUT.PUT_LINE('Generation Time: ' || v_doc_timestamp); DBMS_OUTPUT.PUT_LINE('Output File: &&output_filename'); DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Sections Generated:'); DBMS_OUTPUT.PUT_LINE('- Database Overview and Configuration'); DBMS_OUTPUT.PUT_LINE('- Storage Configuration'); DBMS_OUTPUT.PUT_LINE('- Schema Inventory');
IF v_doc_type IN ('SCHEMA', 'COMPLETE') THEN DBMS_OUTPUT.PUT_LINE('- Table Documentation'); DBMS_OUTPUT.PUT_LINE('- Column Definitions'); DBMS_OUTPUT.PUT_LINE('- Index Documentation'); END IF;
IF v_doc_type IN ('SECURITY', 'COMPLETE') THEN DBMS_OUTPUT.PUT_LINE('- Security and Privileges'); END IF;
IF v_doc_type IN ('PERFORMANCE', 'COMPLETE') THEN DBMS_OUTPUT.PUT_LINE('- Performance Characteristics'); END IF;
IF v_doc_type IN ('SYSTEM', 'COMPLETE') THEN DBMS_OUTPUT.PUT_LINE('- Database Parameters'); DBMS_OUTPUT.PUT_LINE('- External References'); END IF;
DBMS_OUTPUT.PUT_LINE('- Documentation Summary'); DBMS_OUTPUT.PUT_LINE(''); -- Show format-specific information DBMS_OUTPUT.PUT_LINE('Output Information:'); DBMS_OUTPUT.PUT_LINE('- Output saved to: &&output_filename'); DBMS_OUTPUT.PUT_LINE('- Generation completed successfully'); IF v_output_format IN ('HTML', 'CSV') THEN DBMS_OUTPUT.PUT_LINE('- Note: ' || v_output_format || ' format would require file output'); DBMS_OUTPUT.PUT_LINE(' Implement using UTL_FILE or external scripting for file generation'); END IF;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error during documentation generation: ' || SQLERRM);END;/
-- ------------------------------------------------------------------------------------- COMPLETION: Documentation generation summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ============================================PROMPT DATABASE DOCUMENTATION GENERATION COMPLETEDPROMPT ============================================PROMPT Documentation Type: &1PROMPT Output Format: &2PROMPT Detail Level: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT Output File: &&output_filenamePROMPT PROMPT Documentation Summary:PROMPT - Comprehensive database inventory generatedPROMPT - Schema and object documentation completePROMPT - Security and configuration details documentedPROMPT - Performance characteristics capturedPROMPT - All output saved to: &&output_filenamePROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- Stop output redirectionSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------