LOG TO COMPARE

 SET SERVEROUTPUT ON;
DECLARE
  -- User-configurable variables
  TYPE schema_list IS VARRAY(7) OF VARCHAR2(30);
  v_schemas schema_list := schema_list('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7'); -- Replace with your 7 schema names
  v_db_name VARCHAR2(30) := 'DB1'; -- Set to 'DB1' or 'DB2' for each database
  v_log_table_name VARCHAR2(30) := 'schema_object_counts_log_' || LOWER(v_db_name); -- Unique table name per database
  
  -- Variables for logging and timing
  v_start_time TIMESTAMP;
  v_object_count NUMBER;
  v_sql VARCHAR2(4000);
  
  -- Procedure to create log table if it doesn't exist
  PROCEDURE create_log_table IS
  BEGIN
    EXECUTE IMMEDIATE '
      CREATE TABLE ' || v_log_table_name || ' (
        log_id NUMBER GENERATED ALWAYS AS IDENTITY,
        db_name VARCHAR2(30),
        schema_name VARCHAR2(30),
        object_type VARCHAR2(30),
        object_count NUMBER,
        explanation VARCHAR2(4000),
        log_time TIMESTAMP
      )';
    DBMS_OUTPUT.PUT_LINE('Log table created: ' || v_log_table_name);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN -- Table already exists
        DBMS_OUTPUT.PUT_LINE('Log table already exists: ' || v_log_table_name);
      ELSE
        RAISE;
      END IF;
  END;
  
  -- Procedure to log counts with explanation
  PROCEDURE log_counts(p_schema VARCHAR2, p_object_type VARCHAR2, p_object_count NUMBER) IS
    v_explanation VARCHAR2(4000);
  BEGIN
    v_explanation := CASE p_object_type
      WHEN 'TABLE' THEN 'Tables store structured data in rows and columns. Used for persistent data storage.'
      WHEN 'INDEX' THEN 'Indexes improve query performance by enabling faster data retrieval. May include unique, non-unique, or bitmap indexes.'
      WHEN 'SEQUENCE' THEN 'Sequences generate unique numeric values, typically for primary keys or identifiers.'
      WHEN 'VIEW' THEN 'Views are virtual tables based on queries of underlying tables, used for simplified access or security.'
      WHEN 'SYNONYM' THEN 'Synonyms provide alternative names for database objects, often for abstraction or convenience.'
      WHEN 'PROCEDURE' THEN 'Procedures are stored PL/SQL blocks for executing reusable logic or operations.'
      WHEN 'FUNCTION' THEN 'Functions are stored PL/SQL blocks that return a value, used in queries or calculations.'
      WHEN 'PACKAGE' THEN 'Packages group related procedures, functions, and variables for modular code organization.'
      WHEN 'TRIGGER' THEN 'Triggers automatically execute PL/SQL in response to DML/DDL events on tables or schemas.'
      WHEN 'TYPE' THEN 'Types define custom data structures, often used in PL/SQL or object-relational features.'
      WHEN 'MATERIALIZED VIEW' THEN 'Materialized views store query results physically for faster access, often for reporting.'
      WHEN 'JOB' THEN 'Jobs schedule automated tasks, managed via DBMS_SCHEDULER or DBMS_JOB.'
      WHEN 'QUEUE' THEN 'Queues manage message-based communication, typically for advanced queuing (AQ).'
      ELSE 'Other object type: ' || p_object_type
    END || ' Count: ' || p_object_count || ' in schema ' || p_schema || ' on database ' || v_db_name;
    
    INSERT INTO schema_object_counts_log (db_name, schema_name, object_type, object_count, explanation, log_time)
    VALUES (v_db_name, p_schema, p_object_type, p_object_count, v_explanation, SYSTIMESTAMP);
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Logged: ' || v_db_name || ' - ' || p_schema || ', Type ' || p_object_type || ' - Count: ' || p_object_count);
  END;
  
BEGIN
  v_start_time := SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE('Starting object count for ' || v_db_name || ' at ' || v_start_time);
  
  -- Create log table
  create_log_table;
  
  -- Loop through each schema
  FOR i IN 1..v_schemas.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Processing schema: ' || v_schemas(i));
    
    -- Get distinct object types for the schema
    FOR obj_type IN (
      SELECT /*+ PARALLEL(8) */ DISTINCT object_type
      FROM all_objects
      WHERE owner = v_schemas(i)
        AND object_type NOT LIKE 'SYS_%' -- Exclude system-generated objects
      ORDER BY object_type
    ) LOOP
      BEGIN
        -- Count objects with heavy parallelism
        v_sql := 'SELECT /*+ PARALLEL(16) */ COUNT(*) FROM all_objects WHERE owner = ''' || v_schemas(i) || ''' AND object_type = ''' || obj_type.object_type || '''';
        EXECUTE IMMEDIATE v_sql INTO v_object_count;
        
        -- Log the count
        log_counts(v_schemas(i), obj_type.object_type, v_object_count);
        
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Error processing ' || v_schemas(i) || ' - ' || obj_type.object_type || ': ' || SQLERRM);
          INSERT INTO schema_object_counts_log (db_name, schema_name, object_type, object_count, explanation, log_time)
          VALUES (v_db_name, v_schemas(i), obj_type.object_type, -1, 'Error counting objects: ' || SQLERRM, SYSTIMESTAMP);
          COMMIT;
          CONTINUE;
      END;
    END LOOP;
  END LOOP;
  
  -- Summary output
  DBMS_OUTPUT.PUT_LINE('Count completed for ' || v_db_name || ' in ' || ROUND(EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start_time)), 2) || ' seconds');
  DBMS_OUTPUT.PUT_LINE('Review the log table ' || v_log_table_name || ' for detailed results.');
  
  -- Display log summary for the current database
  FOR log_rec IN (
    SELECT db_name, schema_name, object_type, object_count, explanation
    FROM schema_object_counts_log
    WHERE db_name = v_db_name
    ORDER BY schema_name, object_type
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Summary: ' || log_rec.db_name || ' - ' || log_rec.schema_name || ' - ' || log_rec.object_type || ': Count=' || log_rec.object_count || ' | ' || log_rec.explanation);
  END LOOP;
  
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Fatal error in script: ' || SQLERRM);
    RAISE;
END;
/