database_inventory_report.sql
-- ------------------------------------------------------------------------------------- File Name : database_inventory_report.sql-- Author : Pierre Montbleau-- Description : Comprehensive Oracle database inventory and configuration reporting-- Purpose : Document database configuration, objects, and settings for inventory management-- Call Syntax : @F:\DBA\Scripts\database_inventory_report.sql (report_type) (output_level)-- Parameters : report_type - Type of inventory report (SUMMARY, DETAILED, COMPLETE)-- output_level - Output detail level (BASIC, STANDARD, VERBOSE)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREENset verify OFFCOLUMN "_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_inventory_report_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 300SET PAGESIZE 1000SET SERVEROUTPUT ON
PROMPT Starting Database Inventory Report...PROMPT =====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Report Type: &1PROMPT Output Level: &2PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_report_type VARCHAR2(20) := UPPER(NVL('&1', 'SUMMARY')); v_output_level VARCHAR2(20) := UPPER(NVL('&2', 'STANDARD'));BEGIN IF v_report_type NOT IN ('SUMMARY', 'DETAILED', 'COMPLETE') THEN RAISE_APPLICATION_ERROR(-20001, 'Report type must be SUMMARY, DETAILED, or COMPLETE'); END IF; IF v_output_level NOT IN ('BASIC', 'STANDARD', 'VERBOSE') THEN RAISE_APPLICATION_ERROR(-20002, 'Output level must be BASIC, STANDARD, or VERBOSE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DATABASE BASIC INFORMATION: Core database identification and version-- -----------------------------------------------------------------------------------
SPOOL &output_filename
PROMPT PROMPT 1. DATABASE BASIC INFORMATIONPROMPT ==============================
COLUMN database_info FORMAT A40COLUMN current_value FORMAT A50
SELECT 'Database Name' as database_info, name as current_valueFROM v$databaseUNION ALLSELECT 'DB Unique Name', db_unique_nameFROM v$databaseUNION ALLSELECT 'Instance Name', instance_nameFROM v$instanceUNION ALLSELECT 'Instance Status', statusFROM v$instanceUNION ALLSELECT 'Oracle Version', versionFROM v$instanceUNION ALLSELECT 'Host Name', host_nameFROM v$instanceUNION ALLSELECT 'Startup Time', TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS')FROM v$instanceUNION ALLSELECT 'Archive Log Mode', log_modeFROM v$databaseUNION ALLSELECT 'Database Role', database_roleFROM v$databaseORDER BY 1;
-- ------------------------------------------------------------------------------------- STORAGE CONFIGURATION: Tablespace and datafile inventory-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. STORAGE CONFIGURATION INVENTORYPROMPT ===================================
COLUMN tablespace_name FORMAT A35COLUMN status FORMAT A10COLUMN contents FORMAT A10COLUMN extent_management FORMAT A15COLUMN allocation_type FORMAT A15COLUMN SEGMENT_SPACE_MANAGEMENT FORMAT A10COLUMN datafile_count FORMAT 999COLUMN total_size_gb FORMAT 999,999.99
SELECT tablespace_name, status, contents, extent_management, allocation_type, SEGMENT_SPACE_MANAGEMENT, (SELECT COUNT(*) FROM dba_data_files df WHERE df.tablespace_name = dt.tablespace_name) as datafile_count, ROUND((SELECT SUM(bytes) FROM dba_data_files df WHERE df.tablespace_name = dt.tablespace_name) / 1024 / 1024 / 1024, 2) as total_size_gbFROM dba_tablespaces dtWHERE UPPER('&1') IN ('DETAILED', 'COMPLETE') OR (UPPER('&1') = 'SUMMARY' AND ROWNUM <= 10) -- AND OWNER in (select username from dba_users where oracle_maintained = 'N')ORDER BY 8 DESC NULLS LAST;
-- ------------------------------------------------------------------------------------- DATAFILE DETAILS: Individual datafile specifications-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. DATAFILE CONFIGURATION DETAILSPROMPT ==================================
COLUMN file_name FORMAT A65COLUMN tablespace_name FORMAT A30COLUMN size_gb FORMAT 999,999.99COLUMN autoextensible FORMAT A5COLUMN max_size_gb FORMAT 999,999.99COLUMN increment_gb FORMAT 999.99COLUMN status FORMAT A10
SELECT file_name, tablespace_name, ROUND(bytes / 1024 / 1024 / 1024, 2) as size_gb, autoextensible, ROUND(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END / 1024 / 1024 / 1024, 2) as max_size_gb, ROUND(increment_by * 8192 / 1024 / 1024 / 1024, 2) as increment_gb, statusFROM dba_data_filesWHERE UPPER('&1') IN ('DETAILED', 'COMPLETE') OR (UPPER('&1') = 'SUMMARY' AND ROWNUM <= 5) -- AND OWNER in (select username from dba_users where oracle_maintained = 'N')ORDER BY tablespace_name, file_name;
-- ------------------------------------------------------------------------------------- SCHEMA INVENTORY: User accounts and schema information-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. SCHEMA AND USER ACCOUNT INVENTORYPROMPT =====================================
COLUMN username FORMAT A30COLUMN account_status FORMAT A20COLUMN default_tablespace FORMAT A30COLUMN temporary_tablespace FORMAT A20COLUMN created FORMAT A12COLUMN profile FORMAT A30COLUMN object_count FORMAT 999,999
SELECT username, account_status, default_tablespace, temporary_tablespace, TO_CHAR(created, 'YYYY-MM-DD') as created, profile, (SELECT COUNT(*) FROM dba_objects WHERE owner = du.username) as object_countFROM dba_users duWHERE username in (select username from dba_users where oracle_maintained = 'N') AND (UPPER('&1') IN ('DETAILED', 'COMPLETE') OR (UPPER('&1') = 'SUMMARY' AND (SELECT COUNT(*) FROM dba_objects WHERE owner = du.username) > 0))ORDER BY 4 DESC, username;
-- ------------------------------------------------------------------------------------- OBJECT COUNT SUMMARY: Database objects by type and schema-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. DATABASE OBJECT COUNT SUMMARYPROMPT =================================
COLUMN owner FORMAT A20COLUMN object_type FORMAT A20COLUMN object_count FORMAT 999,999COLUMN total_size_gb FORMAT 999,999.99
WITH filtered_objects AS ( SELECT do.owner, do.object_type, do.object_name FROM dba_objects do WHERE do.owner IN (select username from dba_users where oracle_maintained = 'N') AND do.object_type IN ('TABLE','INDEX','VIEW','PROCEDURE','FUNCTION','PACKAGE','SEQUENCE') AND (UPPER('&1') IN ('DETAILED','COMPLETE') OR EXISTS ( SELECT 1 FROM dual WHERE UPPER('&1') = 'SUMMARY' AND ROWNUM <= 100 ))),segment_sizes AS ( SELECT ds.owner, ds.segment_name, SUM(ds.bytes) AS total_bytes FROM dba_segments ds WHERE OWNER in (select username from dba_users where oracle_maintained = 'N') GROUP BY ds.owner, ds.segment_name)SELECT fo.owner, fo.object_type, COUNT(*) as object_count, ROUND(COALESCE(SUM(ss.total_bytes), 0) / 1024 / 1024 / 1024, 2) as total_size_gbFROM filtered_objects foLEFT JOIN segment_sizes ss ON fo.owner = ss.owner AND fo.object_name = ss.segment_nameGROUP BY fo.owner, fo.object_typeHAVING COUNT(*) > 0ORDER BY fo.owner, object_count DESC;
-- ------------------------------------------------------------------------------------- DATABASE PARAMETERS: Key initialization parameters-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. KEY DATABASE PARAMETERSPROMPT ===========================
COLUMN parameter_name FORMAT A35COLUMN parameter_value FORMAT A40COLUMN description FORMAT A70
SELECT name as parameter_name, value as parameter_value, descriptionFROM v$parameterWHERE name IN ( 'db_name', 'db_block_size', 'sga_max_size', 'sga_target', 'pga_aggregate_target', 'processes', 'sessions', 'transactions', 'open_cursors', 'compatible', 'memory_target', 'memory_max_target', 'db_recovery_file_dest', 'db_recovery_file_dest_size')ORDER BY name;
-- ------------------------------------------------------------------------------------- MEMORY CONFIGURATION: SGA and PGA component details-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MEMORY COMPONENT CONFIGURATIONPROMPT ==================================
COLUMN component FORMAT A30COLUMN current_size_gb FORMAT 999,999.99COLUMN min_size_gb FORMAT 999,999.99COLUMN max_size_gb FORMAT 999,999.99COLUMN oper_count FORMAT 999,999COLUMN last_oper_type FORMAT A20
SELECT component, ROUND(current_size / 1024 / 1024 / 1024, 2) as current_size_gb, ROUND(min_size / 1024 / 1024 / 1024, 2) as min_size_gb, ROUND(max_size / 1024 / 1024 / 1024, 2) as max_size_gb, oper_count, last_oper_typeFROM v$memory_dynamic_componentsWHERE current_size > 0 AND UPPER('&1') IN ('DETAILED', 'COMPLETE')ORDER BY current_size DESC;
-- ------------------------------------------------------------------------------------- DATABASE FEATURES: Enabled database options and features-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. DATABASE OPTIONS AND FEATURESPROMPT =================================
COLUMN feature_name FORMAT A60COLUMN detected_usage FORMAT A20COLUMN currently_used FORMAT A5COLUMN first_usage FORMAT A12COLUMN last_usage FORMAT A12
SELECT name as feature_name, detected_usages as detected_usage, currently_used, TO_CHAR(first_usage_date, 'YYYY-MM-DD') as first_usage, TO_CHAR(last_usage_date, 'YYYY-MM-DD') as last_usageFROM dba_feature_usage_statisticsWHERE detected_usages > 0 -- AND UPPER('&1') IN ('COMPLETE') AND name NOT LIKE '%SQL%'ORDER BY last_usage_date DESC NULLS LAST, detected_usages DESCFETCH FIRST 20 ROWS ONLY;
-- ------------------------------------------------------------------------------------- SECURITY CONFIGURATION: Profiles, roles, and privileges-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. SECURITY CONFIGURATION SUMMARYPROMPT ==================================
COLUMN security_component FORMAT A30COLUMN component_name FORMAT A30COLUMN setting FORMAT A30COLUMN user_count FORMAT 999,999
SELECT 'PROFILE' as security_component, profile as component_name, 'N/A' as setting, COUNT(*) as user_countFROM dba_usersWHERE username in (select username from dba_users where oracle_maintained = 'N')GROUP BY profileUNION ALLSELECT 'ROLE', granted_role, 'N/A', COUNT(*)FROM dba_role_privsWHERE grantee IN (select username from dba_users where oracle_maintained = 'N')GROUP BY granted_roleHAVING COUNT(*) > 5UNION ALLSELECT 'SYSTEM_PRIVILEGE', privilege, 'N/A', COUNT(*)FROM dba_sys_privsWHERE grantee IN (select username from dba_users where oracle_maintained = 'N')GROUP BY privilegeHAVING COUNT(*) > 3ORDER BY 1, 4 DESC;
-- ------------------------------------------------------------------------------------- DATABASE LINKS AND DIRECTORY OBJECTS: External references-- -----------------------------------------------------------------------------------
PROMPT PROMPT 10. EXTERNAL REFERENCES INVENTORYPROMPT ===================================
set lin 1000COLUMN object_type FORMAT A25COLUMN owner FORMAT A15COLUMN object_name FORMAT A50COLUMN target_info FORMAT A100COLUMN created FORMAT A12
SELECT 'DATABASE_LINK' as object_type, owner, db_link as object_name, TO_CHAR(created, 'YYYY-MM-DD') as created, username || '@' || host as target_infoFROM dba_db_linksWHERE UPPER('&1') IN ('DETAILED', 'COMPLETE')UNION ALLSELECT 'DIRECTORY', owner, directory_name, NULL, directory_pathFROM dba_directoriesWHERE UPPER('&1') IN ('DETAILED', 'COMPLETE')ORDER BY object_type, owner, object_name;
-- ------------------------------------------------------------------------------------- LARGE OBJECT SUMMARY: Largest database objects-- -----------------------------------------------------------------------------------
PROMPT PROMPT 11. LARGEST DATABASE OBJECTSPROMPT =============================
COLUMN owner FORMAT A25COLUMN segment_name FORMAT A60COLUMN segment_type FORMAT A15COLUMN tablespace_name FORMAT A30COLUMN size_gb FORMAT 999,999.99COLUMN partitions FORMAT 999
SELECT owner, segment_name, segment_type, tablespace_name, ROUND(bytes / 1024 / 1024 / 1024, 2) as size_gb, (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 in (select username from dba_users where oracle_maintained = 'N') AND segment_type IN ('TABLE', 'INDEX', 'TABLE PARTITION', 'INDEX PARTITION') AND bytes / 1024 / 1024 / 1024 > 0.1 -- Larger than 100MB AND (UPPER('&1') IN ('DETAILED', 'COMPLETE') OR (UPPER('&1') = 'SUMMARY' AND ROWNUM <= 15))ORDER BY bytes DESC;
-- ------------------------------------------------------------------------------------- INVENTORY SUMMARY STATISTICS: Consolidated database metrics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 12. DATABASE INVENTORY SUMMARY STATISTICSPROMPT ==========================================
COLUMN inventory_category FORMAT A30COLUMN metric FORMAT A40COLUMN count_value FORMAT 999,999,999COLUMN size_gb FORMAT 999,999,999.99
SELECT 'STORAGE' as inventory_category, 'Total Database Size' as metric, NULL as count_value, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as size_gbFROM dba_segmentsWHERE OWNER in (select username from dba_users where oracle_maintained = 'N')UNION ALLSELECT 'STORAGE', 'Total Datafile Capacity', NULL, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2)FROM dba_data_filesUNION ALLSELECT 'SCHEMAS', 'Total User Accounts', COUNT(*), NULLFROM dba_usersWHERE username in (select username from dba_users where oracle_maintained = 'N')UNION ALLSELECT 'OBJECTS', 'Total Database Objects', COUNT(*), NULLFROM dba_objectsWHERE OWNER in (select username from dba_users where oracle_maintained = 'N')UNION ALLSELECT 'OBJECTS', 'Total Tables', COUNT(*), NULLFROM dba_objectsWHERE object_type = 'TABLE' AND OWNER in (select username from dba_users where oracle_maintained = 'N')UNION ALLSELECT 'OBJECTS', 'Total Indexes', COUNT(*), NULLFROM dba_objectsWHERE object_type = 'INDEX' AND OWNER in (select username from dba_users where oracle_maintained = 'N')UNION ALLSELECT 'PERFORMANCE', 'Total Sessions', COUNT(*), NULLFROM v$sessionORDER BY 1, 2;
-- ------------------------------------------------------------------------------------- COMPLETION: Inventory report summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =====================================PROMPT DATABASE INVENTORY REPORT COMPLETEDPROMPT =====================================PROMPT Report Type: &1PROMPT Output Level: &2PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Report Contents:PROMPT - Database identification and version informationPROMPT - Storage configuration and tablespace detailsPROMPT - Schema and user account inventoryPROMPT - Object counts and sizing informationPROMPT - Key configuration parametersPROMPT - Security and external reference summaryPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREENset verify OFFCOLUMN "_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_inventory_report_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 300SET PAGESIZE 1000SET SERVEROUTPUT ON
PROMPT Starting Database Inventory Report...PROMPT =====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Report Type: &1PROMPT Output Level: &2PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_report_type VARCHAR2(20) := UPPER(NVL('&1', 'SUMMARY')); v_output_level VARCHAR2(20) := UPPER(NVL('&2', 'STANDARD'));BEGIN IF v_report_type NOT IN ('SUMMARY', 'DETAILED', 'COMPLETE') THEN RAISE_APPLICATION_ERROR(-20001, 'Report type must be SUMMARY, DETAILED, or COMPLETE'); END IF; IF v_output_level NOT IN ('BASIC', 'STANDARD', 'VERBOSE') THEN RAISE_APPLICATION_ERROR(-20002, 'Output level must be BASIC, STANDARD, or VERBOSE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DATABASE BASIC INFORMATION: Core database identification and version-- -----------------------------------------------------------------------------------
SPOOL &output_filename
PROMPT PROMPT 1. DATABASE BASIC INFORMATIONPROMPT ==============================
COLUMN database_info FORMAT A40COLUMN current_value FORMAT A50
SELECT 'Database Name' as database_info, name as current_valueFROM v$databaseUNION ALLSELECT 'DB Unique Name', db_unique_nameFROM v$databaseUNION ALLSELECT 'Instance Name', instance_nameFROM v$instanceUNION ALLSELECT 'Instance Status', statusFROM v$instanceUNION ALLSELECT 'Oracle Version', versionFROM v$instanceUNION ALLSELECT 'Host Name', host_nameFROM v$instanceUNION ALLSELECT 'Startup Time', TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS')FROM v$instanceUNION ALLSELECT 'Archive Log Mode', log_modeFROM v$databaseUNION ALLSELECT 'Database Role', database_roleFROM v$databaseORDER BY 1;
-- ------------------------------------------------------------------------------------- STORAGE CONFIGURATION: Tablespace and datafile inventory-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. STORAGE CONFIGURATION INVENTORYPROMPT ===================================
COLUMN tablespace_name FORMAT A35COLUMN status FORMAT A10COLUMN contents FORMAT A10COLUMN extent_management FORMAT A15COLUMN allocation_type FORMAT A15COLUMN SEGMENT_SPACE_MANAGEMENT FORMAT A10COLUMN datafile_count FORMAT 999COLUMN total_size_gb FORMAT 999,999.99
SELECT tablespace_name, status, contents, extent_management, allocation_type, SEGMENT_SPACE_MANAGEMENT, (SELECT COUNT(*) FROM dba_data_files df WHERE df.tablespace_name = dt.tablespace_name) as datafile_count, ROUND((SELECT SUM(bytes) FROM dba_data_files df WHERE df.tablespace_name = dt.tablespace_name) / 1024 / 1024 / 1024, 2) as total_size_gbFROM dba_tablespaces dtWHERE UPPER('&1') IN ('DETAILED', 'COMPLETE') OR (UPPER('&1') = 'SUMMARY' AND ROWNUM <= 10) -- AND OWNER in (select username from dba_users where oracle_maintained = 'N')ORDER BY 8 DESC NULLS LAST;
-- ------------------------------------------------------------------------------------- DATAFILE DETAILS: Individual datafile specifications-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. DATAFILE CONFIGURATION DETAILSPROMPT ==================================
COLUMN file_name FORMAT A65COLUMN tablespace_name FORMAT A30COLUMN size_gb FORMAT 999,999.99COLUMN autoextensible FORMAT A5COLUMN max_size_gb FORMAT 999,999.99COLUMN increment_gb FORMAT 999.99COLUMN status FORMAT A10
SELECT file_name, tablespace_name, ROUND(bytes / 1024 / 1024 / 1024, 2) as size_gb, autoextensible, ROUND(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END / 1024 / 1024 / 1024, 2) as max_size_gb, ROUND(increment_by * 8192 / 1024 / 1024 / 1024, 2) as increment_gb, statusFROM dba_data_filesWHERE UPPER('&1') IN ('DETAILED', 'COMPLETE') OR (UPPER('&1') = 'SUMMARY' AND ROWNUM <= 5) -- AND OWNER in (select username from dba_users where oracle_maintained = 'N')ORDER BY tablespace_name, file_name;
-- ------------------------------------------------------------------------------------- SCHEMA INVENTORY: User accounts and schema information-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. SCHEMA AND USER ACCOUNT INVENTORYPROMPT =====================================
COLUMN username FORMAT A30COLUMN account_status FORMAT A20COLUMN default_tablespace FORMAT A30COLUMN temporary_tablespace FORMAT A20COLUMN created FORMAT A12COLUMN profile FORMAT A30COLUMN object_count FORMAT 999,999
SELECT username, account_status, default_tablespace, temporary_tablespace, TO_CHAR(created, 'YYYY-MM-DD') as created, profile, (SELECT COUNT(*) FROM dba_objects WHERE owner = du.username) as object_countFROM dba_users duWHERE username in (select username from dba_users where oracle_maintained = 'N') AND (UPPER('&1') IN ('DETAILED', 'COMPLETE') OR (UPPER('&1') = 'SUMMARY' AND (SELECT COUNT(*) FROM dba_objects WHERE owner = du.username) > 0))ORDER BY 4 DESC, username;
-- ------------------------------------------------------------------------------------- OBJECT COUNT SUMMARY: Database objects by type and schema-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. DATABASE OBJECT COUNT SUMMARYPROMPT =================================
COLUMN owner FORMAT A20COLUMN object_type FORMAT A20COLUMN object_count FORMAT 999,999COLUMN total_size_gb FORMAT 999,999.99
WITH filtered_objects AS ( SELECT do.owner, do.object_type, do.object_name FROM dba_objects do WHERE do.owner IN (select username from dba_users where oracle_maintained = 'N') AND do.object_type IN ('TABLE','INDEX','VIEW','PROCEDURE','FUNCTION','PACKAGE','SEQUENCE') AND (UPPER('&1') IN ('DETAILED','COMPLETE') OR EXISTS ( SELECT 1 FROM dual WHERE UPPER('&1') = 'SUMMARY' AND ROWNUM <= 100 ))),segment_sizes AS ( SELECT ds.owner, ds.segment_name, SUM(ds.bytes) AS total_bytes FROM dba_segments ds WHERE OWNER in (select username from dba_users where oracle_maintained = 'N') GROUP BY ds.owner, ds.segment_name)SELECT fo.owner, fo.object_type, COUNT(*) as object_count, ROUND(COALESCE(SUM(ss.total_bytes), 0) / 1024 / 1024 / 1024, 2) as total_size_gbFROM filtered_objects foLEFT JOIN segment_sizes ss ON fo.owner = ss.owner AND fo.object_name = ss.segment_nameGROUP BY fo.owner, fo.object_typeHAVING COUNT(*) > 0ORDER BY fo.owner, object_count DESC;
-- ------------------------------------------------------------------------------------- DATABASE PARAMETERS: Key initialization parameters-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. KEY DATABASE PARAMETERSPROMPT ===========================
COLUMN parameter_name FORMAT A35COLUMN parameter_value FORMAT A40COLUMN description FORMAT A70
SELECT name as parameter_name, value as parameter_value, descriptionFROM v$parameterWHERE name IN ( 'db_name', 'db_block_size', 'sga_max_size', 'sga_target', 'pga_aggregate_target', 'processes', 'sessions', 'transactions', 'open_cursors', 'compatible', 'memory_target', 'memory_max_target', 'db_recovery_file_dest', 'db_recovery_file_dest_size')ORDER BY name;
-- ------------------------------------------------------------------------------------- MEMORY CONFIGURATION: SGA and PGA component details-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. MEMORY COMPONENT CONFIGURATIONPROMPT ==================================
COLUMN component FORMAT A30COLUMN current_size_gb FORMAT 999,999.99COLUMN min_size_gb FORMAT 999,999.99COLUMN max_size_gb FORMAT 999,999.99COLUMN oper_count FORMAT 999,999COLUMN last_oper_type FORMAT A20
SELECT component, ROUND(current_size / 1024 / 1024 / 1024, 2) as current_size_gb, ROUND(min_size / 1024 / 1024 / 1024, 2) as min_size_gb, ROUND(max_size / 1024 / 1024 / 1024, 2) as max_size_gb, oper_count, last_oper_typeFROM v$memory_dynamic_componentsWHERE current_size > 0 AND UPPER('&1') IN ('DETAILED', 'COMPLETE')ORDER BY current_size DESC;
-- ------------------------------------------------------------------------------------- DATABASE FEATURES: Enabled database options and features-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. DATABASE OPTIONS AND FEATURESPROMPT =================================
COLUMN feature_name FORMAT A60COLUMN detected_usage FORMAT A20COLUMN currently_used FORMAT A5COLUMN first_usage FORMAT A12COLUMN last_usage FORMAT A12
SELECT name as feature_name, detected_usages as detected_usage, currently_used, TO_CHAR(first_usage_date, 'YYYY-MM-DD') as first_usage, TO_CHAR(last_usage_date, 'YYYY-MM-DD') as last_usageFROM dba_feature_usage_statisticsWHERE detected_usages > 0 -- AND UPPER('&1') IN ('COMPLETE') AND name NOT LIKE '%SQL%'ORDER BY last_usage_date DESC NULLS LAST, detected_usages DESCFETCH FIRST 20 ROWS ONLY;
-- ------------------------------------------------------------------------------------- SECURITY CONFIGURATION: Profiles, roles, and privileges-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. SECURITY CONFIGURATION SUMMARYPROMPT ==================================
COLUMN security_component FORMAT A30COLUMN component_name FORMAT A30COLUMN setting FORMAT A30COLUMN user_count FORMAT 999,999
SELECT 'PROFILE' as security_component, profile as component_name, 'N/A' as setting, COUNT(*) as user_countFROM dba_usersWHERE username in (select username from dba_users where oracle_maintained = 'N')GROUP BY profileUNION ALLSELECT 'ROLE', granted_role, 'N/A', COUNT(*)FROM dba_role_privsWHERE grantee IN (select username from dba_users where oracle_maintained = 'N')GROUP BY granted_roleHAVING COUNT(*) > 5UNION ALLSELECT 'SYSTEM_PRIVILEGE', privilege, 'N/A', COUNT(*)FROM dba_sys_privsWHERE grantee IN (select username from dba_users where oracle_maintained = 'N')GROUP BY privilegeHAVING COUNT(*) > 3ORDER BY 1, 4 DESC;
-- ------------------------------------------------------------------------------------- DATABASE LINKS AND DIRECTORY OBJECTS: External references-- -----------------------------------------------------------------------------------
PROMPT PROMPT 10. EXTERNAL REFERENCES INVENTORYPROMPT ===================================
set lin 1000COLUMN object_type FORMAT A25COLUMN owner FORMAT A15COLUMN object_name FORMAT A50COLUMN target_info FORMAT A100COLUMN created FORMAT A12
SELECT 'DATABASE_LINK' as object_type, owner, db_link as object_name, TO_CHAR(created, 'YYYY-MM-DD') as created, username || '@' || host as target_infoFROM dba_db_linksWHERE UPPER('&1') IN ('DETAILED', 'COMPLETE')UNION ALLSELECT 'DIRECTORY', owner, directory_name, NULL, directory_pathFROM dba_directoriesWHERE UPPER('&1') IN ('DETAILED', 'COMPLETE')ORDER BY object_type, owner, object_name;
-- ------------------------------------------------------------------------------------- LARGE OBJECT SUMMARY: Largest database objects-- -----------------------------------------------------------------------------------
PROMPT PROMPT 11. LARGEST DATABASE OBJECTSPROMPT =============================
COLUMN owner FORMAT A25COLUMN segment_name FORMAT A60COLUMN segment_type FORMAT A15COLUMN tablespace_name FORMAT A30COLUMN size_gb FORMAT 999,999.99COLUMN partitions FORMAT 999
SELECT owner, segment_name, segment_type, tablespace_name, ROUND(bytes / 1024 / 1024 / 1024, 2) as size_gb, (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 in (select username from dba_users where oracle_maintained = 'N') AND segment_type IN ('TABLE', 'INDEX', 'TABLE PARTITION', 'INDEX PARTITION') AND bytes / 1024 / 1024 / 1024 > 0.1 -- Larger than 100MB AND (UPPER('&1') IN ('DETAILED', 'COMPLETE') OR (UPPER('&1') = 'SUMMARY' AND ROWNUM <= 15))ORDER BY bytes DESC;
-- ------------------------------------------------------------------------------------- INVENTORY SUMMARY STATISTICS: Consolidated database metrics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 12. DATABASE INVENTORY SUMMARY STATISTICSPROMPT ==========================================
COLUMN inventory_category FORMAT A30COLUMN metric FORMAT A40COLUMN count_value FORMAT 999,999,999COLUMN size_gb FORMAT 999,999,999.99
SELECT 'STORAGE' as inventory_category, 'Total Database Size' as metric, NULL as count_value, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as size_gbFROM dba_segmentsWHERE OWNER in (select username from dba_users where oracle_maintained = 'N')UNION ALLSELECT 'STORAGE', 'Total Datafile Capacity', NULL, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2)FROM dba_data_filesUNION ALLSELECT 'SCHEMAS', 'Total User Accounts', COUNT(*), NULLFROM dba_usersWHERE username in (select username from dba_users where oracle_maintained = 'N')UNION ALLSELECT 'OBJECTS', 'Total Database Objects', COUNT(*), NULLFROM dba_objectsWHERE OWNER in (select username from dba_users where oracle_maintained = 'N')UNION ALLSELECT 'OBJECTS', 'Total Tables', COUNT(*), NULLFROM dba_objectsWHERE object_type = 'TABLE' AND OWNER in (select username from dba_users where oracle_maintained = 'N')UNION ALLSELECT 'OBJECTS', 'Total Indexes', COUNT(*), NULLFROM dba_objectsWHERE object_type = 'INDEX' AND OWNER in (select username from dba_users where oracle_maintained = 'N')UNION ALLSELECT 'PERFORMANCE', 'Total Sessions', COUNT(*), NULLFROM v$sessionORDER BY 1, 2;
-- ------------------------------------------------------------------------------------- COMPLETION: Inventory report summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =====================================PROMPT DATABASE INVENTORY REPORT COMPLETEDPROMPT =====================================PROMPT Report Type: &1PROMPT Output Level: &2PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Report Contents:PROMPT - Database identification and version informationPROMPT - Storage configuration and tablespace detailsPROMPT - Schema and user account inventoryPROMPT - Object counts and sizing informationPROMPT - Key configuration parametersPROMPT - Security and external reference summaryPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------