```sql
SET LINESIZE 32767
SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
SET TERMOUT ON
SET TRIMSPOOL ON
SET TRIMOUT ON
-- Hardcode schema list and output directory
DEFINE schema_list = 'SCHEMA1,SCHEMA2,SCHEMA3'
DEFINE output_dir = 'C:\Reports\'
-- Spool to local directory (may not work in Toad; use Script Output export instead)
SPOOL "&output_dir.schema_compare_report_&SYSDATE..csv"
-- Report Header (as comments in CSV)
PROMPT # Database Schema Comparison Report
PROMPT # Generated on: &SYSDATE
PROMPT # Database: &DATABASE_NAME
PROMPT # Schemas: &schema_list
PROMPT # Overall object counts per schema for tables, indexes, constraints, sequences, etc.
-- CSV Header
PROMPT "Schema","Object Type","Count"
-- Anonymous PL/SQL Block
BEGIN
-- Declare variables
FOR schema_rec IN (
SELECT UPPER(TRIM(REGEXP_SUBSTR('&schema_list', '[^,]+', 1, LEVEL))) AS schema_name
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT('&schema_list', ',') + 1
) LOOP
IF schema_rec.schema_name IS NOT NULL THEN
BEGIN
-- Object counts from dba_objects (TABLE, INDEX, SEQUENCE, PROCEDURE, PACKAGE, SYNONYM, etc.)
FOR obj IN (
SELECT object_type, COUNT(*) AS object_count
FROM dba_objects
WHERE owner = schema_rec.schema_name
GROUP BY object_type
ORDER BY object_type
) LOOP
DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","' || obj.object_type || '","' || obj.object_count || '"');
END LOOP;
-- Constraint counts from dba_constraints
DECLARE
v_constraint_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_constraint_count
FROM dba_constraints
WHERE owner = schema_rec.schema_name;
DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","CONSTRAINT","' || v_constraint_count || '"');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","CONSTRAINT","0"');
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","ERROR","' || REPLACE(SQLERRM, '"', '""') || '"');
END;
END IF;
END LOOP;
END;
/
SPOOL OFF
PROMPT Report generated: &output_dir.schema_compare_report_&SYSDATE..csv
PROMPT To save as CSV in Toad, right-click the Script Output, select 'Export Dataset', choose CSV format, and save to your local directory (e.g., &output_dir).
```
### Key Changes
1. **Anonymous PL/SQL Block**:
- Replaced the `UNION ALL` plain SQL queries with an anonymous PL/SQL block using `DBMS_OUTPUT` to generate CSV rows.
- Iterates over schemas using a cursor and outputs counts for each object type.
2. **Simplified Output**:
- Reports only the **overall count** per schema for:
- Object types from `dba_objects` (e.g., TABLE, INDEX, SEQUENCE, PROCEDURE, PACKAGE, SYNONYM).
- CONSTRAINT (total count from `dba_constraints`, not broken down by type).
- Excludes detailed breakdowns (e.g., tablespace usage, constraint types, invalid objects) for simplicity.
- CSV format: `Schema,Object Type,Count` (e.g., `"SCHEMA1","TABLE","50"`).
3. **Hardcoded Values**:
- Schema list: `DEFINE schema_list = 'SCHEMA1,SCHEMA2,SCHEMA3'` (edit to your schemas).
- Output directory: `DEFINE output_dir = 'C:\Reports\'` (edit to your directory).
4. **Constraints**:
- Included a single count of all constraints per schema from `dba_constraints`, as you questioned their inclusion but they were part of your earlier requirements.
- If you don’t want constraints, comment out or delete the constraint block:
```sql
-- DECLARE
-- v_constraint_count NUMBER;
-- BEGIN
-- SELECT COUNT(*) INTO v_constraint_count
-- FROM dba_constraints
-- WHERE owner = schema_rec.schema_name;
-- DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","CONSTRAINT","' || v_constraint_count || '"');
-- EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- DBMS_OUTPUT.PUT_LINE('"' || schema_rec.schema_name || '","CONSTRAINT","0"');
-- END;
```
5. **Toad Compatibility**:
- Outputs via `DBMS_OUTPUT` to Toad’s Script Output tab.
- Includes instructions to export from Script Output to CSV, as Toad may not write `SPOOL` files reliably.
6. **Excel-Friendly**:
- CSV headers: `Schema,Object Type,Count`.
- Fields are quoted to handle special characters, ensuring Excel parses columns correctly.
- No Percentage column, as you requested a plain count for simplicity.
### Updated PowerShell Comparison Script
The PowerShell script needs to be updated to handle the simplified CSV output (Schema, Object Type, Count). Below is the modified script to compare the source and target CSV files and produce a comparison CSV for Excel.
<xaiArtifact artifact_id="345c5809-7506-4a03-8f77-8d92775ad9a0" artifact_version_id="07826d33-73a3-4507-84af-bfa3e4acd1cb" title="Compare-SchemaReports.ps1" contentType="text/powershell">
```powershell
<#
.SYNOPSIS
Compares two Oracle schema comparison report CSV files and generates a difference report in CSV format.
.DESCRIPTION
This script parses two CSV files generated by schema_compare_report_toad.sql, compares object counts
for each schema and object type, and outputs differences in a CSV file for Excel.
.PARAMETER SourceFile
Path to the source database report (.csv).
.PARAMETER TargetFile
Path to the target database report (.csv).
.PARAMETER OutputFile
Path to save the comparison report (optional, defaults to schema_comparison_diff
================================
```sql
SET LINESIZE 32767
SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
SET TERMOUT ON
SET TRIMSPOOL ON
SET TRIMOUT ON
-- Prompt for schema names once as a comma-separated list
ACCEPT schema_list PROMPT 'Enter schema names (comma-separated, up to 7, e.g., SCHEMA1,SCHEMA2,SCHEMA3): '
-- Prompt for output directory (Windows path, e.g., C:\Reports\)
ACCEPT output_dir PROMPT 'Enter local output directory (e.g., C:\Reports\): '
-- Attempt to spool to local directory (may not work in Toad)
SPOOL "&output_dir.schema_compare_report_&SYSDATE..csv"
-- Report Header (as a comment in CSV)
PROMPT # Database Schema Comparison Report
PROMPT # Generated on: &SYSDATE
PROMPT # Database: &DATABASE_NAME
PROMPT # Schemas: &schema_list
PROMPT # Run this report on both Source and Target databases for comparison
-- Summary of Object Counts by Type across all schemas
PROMPT "Section","Schema","Object Type","Status","Object Count","Percentage"
SELECT
'Summary' AS "Section",
owner AS "Schema",
object_type AS "Object Type",
status AS "Status",
COUNT(*) AS "Object Count",
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY owner), 2) AS "Percentage"
FROM dba_objects
WHERE owner IN (
SELECT UPPER(TRIM(REGEXP_SUBSTR('&schema_list', '[^,]+', 1, LEVEL)))
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT('&schema_list', ',') + 1
)
GROUP BY owner, object_type, status
ORDER BY owner, object_type, status;
-- Detailed Report per Schema
DECLARE
v_schema VARCHAR2(30);
v_error_count NUMBER := 0;
v_error_message VARCHAR2(4000);
CURSOR schema_cursor IS
SELECT UPPER(TRIM(REGEXP_SUBSTR('&schema_list', '[^,]+', 1, LEVEL))) AS schema_name
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT('&schema_list', ',') + 1;
BEGIN
-- Enable DBMS_OUTPUT
DBMS_OUTPUT.ENABLE(NULL);
-- Object Type Counts
FOR rec IN schema_cursor LOOP
v_schema := rec.schema_name;
IF v_schema IS NOT NULL THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Objects,' || v_schema || ',Object Type,Count');
FOR obj IN (
SELECT object_type, COUNT(*) AS object_count
FROM dba_objects
WHERE owner = v_schema
GROUP BY object_type
ORDER BY object_type
) LOOP
DBMS_OUTPUT.PUT_LINE('Objects,' || v_schema || ',' || obj.object_type || ',' || obj.object_count);
END LOOP;
-- Tablespace Usage for Tables
DBMS_OUTPUT.PUT_LINE('Tablespace_Tables,' || v_schema || ',Tablespace,Count');
FOR tbl IN (
SELECT tablespace_name, COUNT(*) AS table_count
FROM dba_tables
WHERE owner = v_schema
GROUP BY tablespace_name
ORDER BY tablespace_name
) LOOP
DBMS_OUTPUT.PUT_LINE('Tablespace_Tables,' || v_schema || ',' || NVL(tbl.tablespace_name, 'N/A') || ',' || tbl.table_count);
END LOOP;
-- Tablespace Usage for Indexes
DBMS_OUTPUT.PUT_LINE('Tablespace_Indexes,' || v_schema || ',Tablespace,Count');
FOR idx IN (
SELECT tablespace_name, COUNT(*) AS index_count
FROM dba_indexes
WHERE owner = v_schema
GROUP BY tablespace_name
ORDER BY tablespace_name
) LOOP
DBMS_OUTPUT.PUT_LINE('Tablespace_Indexes,' || v_schema || ',' || NVL(idx.tablespace_name, 'N/A') || ',' || idx.index_count);
END LOOP;
-- Constraints by Type
DBMS_OUTPUT.PUT_LINE('Constraints,' || v_schema || ',Constraint Type,Count');
FOR cons IN (
SELECT
CASE constraint_type
WHEN 'C' THEN 'Check'
WHEN 'P' THEN 'Primary Key'
WHEN 'U' THEN 'Unique'
WHEN 'R' THEN 'Foreign Key'
WHEN 'V' THEN 'View Check'
WHEN 'O' THEN 'Read Only View'
ELSE constraint_type
END AS constraint_type,
COUNT(*) AS constraint_count
FROM dba_constraints
WHERE owner = v_schema
GROUP BY constraint_type
ORDER BY constraint_type
) LOOP
DBMS_OUTPUT.PUT_LINE('Constraints,' || v_schema || ',' || cons.constraint_type || ',' || cons.constraint_count);
END LOOP;
-- Invalid Objects
DBMS_OUTPUT.PUT_LINE('Invalid_Objects,' || v_schema || ',Object Type,Object Name');
FOR inv IN (
SELECT object_name, object_type
FROM dba_objects
WHERE owner = v_schema AND status != 'VALID'
ORDER BY object_type, object_name
) LOOP
DBMS_OUTPUT.PUT_LINE('Invalid_Objects,' || v_schema || ',' || inv.object_type || ',' || inv.object_name);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
v_error_count := v_error_count + 1;
v_error_message := SQLERRM;
DBMS_OUTPUT.PUT_LINE('Errors,' || v_schema || ',Error Message,' || REPLACE(v_error_message, ',', '||'));
END;
END IF;
END LOOP;
-- Summary of Errors
IF v_error_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('Errors,All Schemas,Total Errors,' || v_error_count);
END IF;
END;
/
SPOOL OFF
PROMPT Report generated: &output_dir.schema_compare_report_&SYSDATE..csv
PROMPT To save as CSV in Toad, right-click the grid, select 'Export Dataset', choose CSV format, and save to your local directory (e.g., &output_dir).
```
### Changes in the SQL Script
1. **Toad Compatibility**:
- Removed quoting around fields in the output to simplify CSV export from Toad’s grid, as Toad handles quoting automatically when exporting.
- Added `SET TRIMSPOOL ON` and `SET TRIMOUT ON` to remove trailing spaces, ensuring clean CSV output.
- Kept `SPOOL` as a fallback, but added a `PROMPT` instructing users to export from Toad’s grid if `SPOOL` fails.
2. **Output Directory Prompt**:
- Retained `ACCEPT output_dir` to specify the local directory (e.g., `C:\Reports\`).
- The `SPOOL` command uses this path, but Toad may ignore it, so we rely on manual export if needed.
3. **CSV Format**:
- Output is formatted as comma-separated values without quotes (Toad’s export adds quotes as needed).
- Sections are clearly labeled (e.g., `Summary`, `Objects`) for Excel compatibility.
4. **Error Handling**:
- Errors are logged in the `Errors` section, with commas in error messages replaced by `||` to avoid CSV parsing issues.
5. **Instructions for Toad**:
- Added a `PROMPT` at the end guiding users to export the results from Toad’s grid to a CSV file.
### Steps to Run in Toad
1. **Set Up Toad**:
- Ensure Toad for Oracle is installed and configured to connect to your Oracle RDS instances (source and target).
- Add the RDS connection:
- In Toad, go to `Database > New Connection`.
- Enter the RDS endpoint (e.g., `mydb.123456789012.us-east-1.rds.amazonaws.com:1521/ORCL`), username, and password.
- Test the connection to confirm it works.
2. **Create Local Directory**:
- Create a directory on your Windows machine (e.g., `C:\Reports`):
```cmd
mkdir C:\Reports
```
3. **Run the Script in Toad**:
- Save the script as `C:\Scripts\schema_compare_report_toad.sql`.
- Open Toad’s **SQL Editor**:
- Go to `Database > SQL Editor`.
- Load the script: `File > Open > C:\Scripts\schema_compare_report_toad.sql`.
- Execute the script:
- Press `F5` (Run as Script) or click the “Run as Script” button.
- Respond to prompts:
```
Enter schema names (comma-separated, up to 7, e.g., SCHEMA1,SCHEMA2,SCHEMA3): SCHEMA1,SCHEMA2
Enter local output directory (e.g., C:\Reports\): C:\Reports\
```
- **Check Output**:
- If `SPOOL` works in your Toad version, check `C:\Reports\schema_compare_report_YYYYMMDD_HHMMSS.csv`.
- If no file is written (common in Toad), the results will appear in the **Script Output** tab or **Data Grid**.
4. **Export to CSV from Toad**:
- If `SPOOL` doesn’t write the file:
- In the **Script Output** tab or **Data Grid**, right-click the results.
- Select `Export Dataset`.
- Choose:
- **Format**: CSV
- **File**: `C:\Reports\schema_compare_report_SOURCE.csv` (or `TARGET.csv` for the target database)
- **Delimiter**: Comma
========================================================================
SET LINESIZE 200
SET PAGESIZE 50000
SET HEADING ON
SET FEEDBACK OFF
SET ECHO OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
-- Define schema names
ACCEPT schema1 PROMPT 'Enter the name for Schema 1: '
ACCEPT schema2 PROMPT 'Enter the name for Schema 2: '
ACCEPT schema3 PROMPT 'Enter the name for Schema 3: '
ACCEPT schema4 PROMPT 'Enter the name for Schema 4: '
ACCEPT schema5 PROMPT 'Enter the name for Schema 5: '
ACCEPT schema6 PROMPT 'Enter the name for Schema 6: '
ACCEPT schema7 PROMPT 'Enter the name for Schema 7: '
-- Spool the output to a file
SPOOL schema_compare_report_&schema1._&SYSDATE..lst
-- Report Header
PROMPT ==============================================================================
PROMPT Database Schema Comparison Report
PROMPT ==============================================================================
PROMPT Generated on: &SYSDATE
PROMPT Database: &DATABASE_NAME
PROMPT Run this report on both Source and Target databases for comparison
PROMPT ==============================================================================
-- Summary of Object Counts by Type across all schemas
PROMPT
PROMPT --- 1. SUMMARY OF OBJECT COUNTS BY SCHEMA AND TYPE ---
PROMPT
SELECT
owner AS "Schema",
object_type AS "Object Type",
status AS "Status",
COUNT(*) AS "Object Count",
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY owner), 2) AS "Percentage"
FROM dba_objects
WHERE owner IN (
UPPER('&schema1.'), UPPER('&schema2.'), UPPER('&schema3.'),
UPPER('&schema4.'), UPPER('&schema5.'), UPPER('&schema6.'), UPPER('&schema7.')
)
GROUP BY owner, object_type, status
ORDER BY owner, object_type, status;
-- Detailed Report per Schema
PROMPT
PROMPT --- 2. DETAILED OBJECT REPORT PER SCHEMA ---
PROMPT
DECLARE
v_schema VARCHAR2(30);
TYPE schema_array_t IS TABLE OF VARCHAR2(30);
v_schemas schema_array_t := schema_array_t(
UPPER('&schema1.'), UPPER('&schema2.'), UPPER('&schema3.'),
UPPER('&schema4.'), UPPER('&schema5.'), UPPER('&schema6.'), UPPER('&schema7.')
);
v_error_count NUMBER := 0;
BEGIN
-- Enable DBMS_OUTPUT
DBMS_OUTPUT.ENABLE(NULL);
FOR i IN 1..v_schemas.COUNT LOOP
v_schema := v_schemas(i);
IF v_schema IS NOT NULL THEN
BEGIN
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('--- SCHEMA: ' || v_schema || ' ---');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
-- Object Type Counts
DBMS_OUTPUT.PUT_LINE(RPAD('Object Type', 25) || RPAD('Count', 10));
DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || RPAD('-', 10, '-'));
FOR rec IN (
SELECT object_type, COUNT(*) AS object_count
FROM dba_objects
WHERE owner = v_schema
GROUP BY object_type
ORDER BY object_type
) LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(rec.object_type, 25) || RPAD(rec.object_count, 10));
END LOOP;
-- Tablespace Usage for Tables
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Tablespace Usage (Tables)');
DBMS_OUTPUT.PUT_LINE(RPAD('-', 35, '-'));
FOR rec IN (
SELECT tablespace_name, COUNT(*) AS table_count
FROM dba_tables
WHERE owner = v_schema
GROUP BY tablespace_name
ORDER BY tablespace_name
) LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(NVL(rec.tablespace_name, 'N/A'), 25) || RPAD(rec.table_count, 10));
END LOOP;
-- Tablespace Usage for Indexes
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Tablespace Usage (Indexes)');
DBMS_OUTPUT.PUT_LINE(RPAD('-', 35, '-'));
FOR rec IN (
SELECT tablespace_name, COUNT(*) AS index_count
FROM dba_indexes
WHERE owner = v_schema
GROUP BY tablespace_name
ORDER BY tablespace_name
) LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(NVL(rec.tablespace_name, 'N/A'), 25) || RPAD(rec.index_count, 10));
END LOOP;
-- Constraints by Type
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Constraints by Type');
DBMS_OUTPUT.PUT_LINE(RPAD('-', 35, '-'));
FOR rec IN (
SELECT
CASE constraint_type
WHEN 'C' THEN 'Check'
WHEN 'P' THEN 'Primary Key'
WHEN 'U' THEN 'Unique'
WHEN 'R' THEN 'Foreign Key'
WHEN 'V' THEN 'View Check'
WHEN 'O' THEN 'Read Only View'
ELSE constraint_type
END AS constraint_type,
COUNT(*) AS constraint_count
FROM dba_constraints
WHERE owner = v_schema
GROUP BY constraint_type
ORDER BY constraint_type
) LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(rec.constraint_type, 25) || RPAD(rec.constraint_count, 10));
END LOOP;
-- Invalid Objects
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Invalid Objects');
DBMS_OUTPUT.PUT_LINE(RPAD('-', 35, '-'));
FOR rec IN (
SELECT object_name, object_type
FROM dba_objects
WHERE owner = v_schema AND status != 'VALID'
ORDER BY object_type, object_name
) LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(rec.object_type, 25) || rec.object_name);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
v_error_count := v_error_count + 1;
DBMS_OUTPUT.PUT_LINE('Error processing schema ' || v_schema || ': ' || SQLERRM);
END;
END IF;
END LOOP;
-- Summary of Errors
IF v_error_count > 0 THEN
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('WARNING: ' || v_error_count || ' error(s) encountered during report generation');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
END IF;
END;
/
-- Report Footer
PROMPT
PROMPT ==============================================================================
PROMPT End of Schema Comparison Report
PROMPT ==============================================================================
SPOOL OFF
No comments:
Post a Comment