Ryalli's world

Thursday, July 10, 2025

ASH/AWR Reports

You're looking for how to download the HTML-formatted AWR or ASH reports that you've generated in your Oracle RDS instance. Since you don't have direct operating system access on RDS, you can't just scp the files. AWS provides specific mechanisms for this.

There are two primary ways to download these reports from Oracle RDS:

Through the AWS RDS Console (Recommended and Easiest)
Using rdsadmin.rds_file_util.read_text_file from a SQL client

Let's detail each method.

Method 1: Downloading via the AWS RDS Console (Easiest)

This is the most common and user-friendly way. When you generate an AWR or ASH report using rdsadmin.rds_run_awr_report or rdsadmin.rds_run_ash_report (especially if you specify report_type => 'HTML'), RDS automatically makes these reports available for download in the console.

Steps:

Generate the Report in HTML Format:

Make sure you specified 'HTML' as the report_type when calling the rdsadmin procedures.

For AWR:

SQL

SELECT rdsadmin.rds_run_awr_report(
           l_begin_snap => YOUR_BEGIN_SNAP_ID,
           l_end_snap   => YOUR_END_SNAP_ID,
           l_report_type => 'HTML' -- <<< IMPORTANT: Specify HTML
       ) AS AWR_REPORT_TEXT FROM DUAL;

For ASH:

SQL

BEGIN
    rdsadmin.rds_run_ash_report(
        begin_time => TO_TIMESTAMP('YYYY-MM-DD HH24:MI:SS', 'YYYY-MM-DD HH24:MI:SS'), -- Your start time
        end_time   => TO_TIMESTAMP('YYYY-MM-DD HH24:MI:SS', 'YYYY-MM-DD HH24:MI:SS'),   -- Your end time
        report_type => 'HTML' -- <<< IMPORTANT: Specify HTML
    );
END;
/

After executing the above, the report will be generated and placed in a default directory managed by RDS (usually BDUMP or a specific diagnostic directory).

Navigate to the RDS Console:
- Go to the AWS Management Console and navigate to RDS.
- In the navigation pane, choose Databases.
- Select your Oracle DB instance.
Go to the Logs & events Tab:
- Click on the "Logs & events" tab.
Scroll Down to the "Logs" Section:
- In the "Logs" section, you'll see a list of various log files.
Search for your Report File:
- AWR reports generated in HTML will typically have names like awrrpt_BEGINSNAP_ENDSNAP.html (e.g., awrrpt_123_124.html).
- ASH reports generated in HTML will typically have names like ashrpt_YYYYMMDDHH24MISS_YYYYMMDDHH24MISS.html (e.g., ashrpt_20250709000000_20250709010000.html).
- You might need to use the search/filter box if you have many log files.
Download the Report:
- Select the .html report file you want to download.
- Click the "Download" button.

The file will download to your local machine, and you can then open it in any web browser.

Method 2: Downloading via SQL Client using `rdsadmin.rds_file_util.read_text_file`

This method is useful if you want to automate the retrieval, or if you prefer to stay within your SQL client, but it requires more steps and manual file creation on your local machine.

Steps:

Generate the Report (if not already done):
Use the rdsadmin.rds_run_awr_report or rdsadmin.rds_run_ash_report as shown in Method 1, ensuring report_type => 'HTML'.
Identify the Report Filename:
You'll need the exact filename (e.g., awrrpt_123_124.html) and the directory it's in (usually BDUMP by default for these reports). You can list files in the BDUMP directory:
SQL
```
SELECT filename, filesize, mtime
FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP'))
WHERE filename LIKE 'awrrpt_%.html' OR filename LIKE 'ashrpt_%.html'
ORDER BY mtime DESC;
```

Read the HTML Content using SQL:

The rdsadmin.rds_file_util.read_text_file function reads the content of the file.2 You'll need to spool this output to a local file.

For SQL*Plus (recommended for this method):

SQL

-- Set output formatting to prevent line breaks and headers in the HTML content
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 32767 -- Max line size to avoid wrapping HTML
SET LONG 32767      -- Max long for CLOB output
SET TRIMSPOOL ON    -- Trim trailing spaces

-- Spool the output to a local HTML file
SPOOL C:\Path\To\Your\Report\awrrpt_123_124.html -- <<< CHANGE THIS PATH AND FILENAME

SELECT text
FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'awrrpt_123_124.html')); -- <<< CHANGE FILENAME

SPOOL OFF
SET HEADING ON
SET FEEDBACK ON
SET PAGESIZE 14
SET LINESIZE 80 -- Reset your SQL*Plus settings

For SQL Developer/Toad:
You might need to copy the CLOB output directly from the query result grid and paste it into a text editor, then save it as an .html file. This is more manual than SPOOL but works if SPOOL isn't an option or you prefer the GUI.

Open the HTML File:
Once saved to your local machine, open the .html file with your preferred web browser.

You've provided a very good set of SQL queries targeting dba_hist_active_sess_history and dba_hist_snapshot, which are the core views for AWR and ASH data. These queries are excellent starting points for a detailed performance investigation.

However, since you're operating in an AWS RDS for Oracle environment, there are crucial considerations and necessary adjustments. Oracle RDS for Oracle restricts direct access to some DBMS_WORKLOAD_REPOSITORY functions and requires the use of rdsadmin specific packages for generating AWR/ASH reports and interacting with diagnostic files.

Here's my assessment and how to "tune" (adapt) them for your RDS environment and general best practices:

General Assessment:

Good Starting Point: The logic in each query directly targets the relevant performance metrics.
ASH-Focused: Most of the queries hit dba_hist_active_sess_history, which is excellent for detailed, granular analysis during spikes.
Missing Bind Variables for FETCH FIRST: While FETCH FIRST N ROWS ONLY is good, a bind variable could make it more flexible.
No Schema-Specific Filtering: For multi-tenant or multi-application databases, filtering by schema/user could be beneficial (though user_id is selected in one query).
DBID and Instance Number for AWR: DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTMLtypically requires DBID and instance number. For RDS, the instance number is usually 1, and DBID can be retrieved from v$database.

Detailed Review and Tuning for Oracle RDS:

Let's go through each query, assess it, and provide the RDS-adapted version or tuning tips.

Crucial RDS Note: In RDS, you cannot directly call DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML as a SELECT statement directly in SQL*Plus/SQL Developer that outputs HTML. You must use rdsadmin.rds_run_awr_report for report generation, and then retrieve the report from the RDS Console or using rdsadmin.rds_file_util.read_text_file.

1. 01_awr_generate_html.sql - Generate AWR HTML Report

Original Query:

SQL

SELECT output
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    (SELECT dbid FROM v$database),
    1, -- Instance Number
    :begin_snap_id,
    :end_snap_id
));

Assessment:
- Problematic for RDS: Direct invocation of DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML in this TABLE() format is generally not supported for direct output in RDS.
- Correct Parameters: The use of DBID, Instance Number, begin_snap_id, end_snap_id is correct for AWR.

Tuning/RDS Adaptation:

You must use the rdsadmin.rds_run_awr_report procedure. This procedure places the generated report file in a diagnostic directory on the RDS instance, which you then download via the AWS Console or read via rdsadmin.rds_file_util.read_text_file.

Recommendation:

SQL

-- RDS-Adapted: Generate AWR HTML Report
-- This procedure will place the awrrpt_...html file in a diagnostic directory on RDS.
-- You will then download it from the AWS RDS Console (Logs & events -> Logs) or read it using rdsadmin.rds_file_util.read_text_file.
BEGIN
    rdsadmin.rds_run_awr_report(
        l_begin_snap => :begin_snap_id, -- Bind variable for AWR begin snapshot ID
        l_end_snap   => :end_snap_id,   -- Bind variable for AWR end snapshot ID
        l_report_type => 'HTML'         -- Specify HTML output
    );
END;
/

To Retrieve: Use AWS RDS Console (Logs & events tab) or SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'awrrpt_YOUR_BEGIN_SNAP_ID_YOUR_END_SNAP_ID.html')); (using SPOOL for SQL*Plus).

2. 02_top_10_sessions_cpu.sql - Top 10 sessions consuming CPU

Original Query:

SQL

SELECT session_id, session_serial#, COUNT(*) AS samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN :begin_time AND :end_time
  AND session_state = 'ON CPU'
GROUP BY session_id, session_serial#
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

Assessment:
- Excellent: Directly targets CPU-consuming sessions using session_state = 'ON CPU'. FETCH FIRST 10 ROWS ONLY is good.

Tuning/RDS Adaptation:

Include more identifying columns: PROGRAM, MODULE, USERNAME, SQL_ID. This makes the output much more useful for debugging.
Join V$SESSION (if current) or DBA_USERS: To get the username from user_id.

Recommendation:

SQL

-- Top N sessions consuming CPU during a specific period
SELECT
    h.session_id,
    h.session_serial#,
    u.username,
    h.program,
    h.module,
    h.sql_id,
    COUNT(*) AS cpu_samples -- Renamed alias for clarity
FROM dba_hist_active_sess_history h
JOIN dba_users u ON h.user_id = u.user_id
WHERE h.sample_time BETWEEN :begin_time AND :end_time
  AND h.session_state = 'ON CPU'
GROUP BY
    h.session_id,
    h.session_serial#,
    u.username,
    h.program,
    h.module,
    h.sql_id
ORDER BY cpu_samples DESC
FETCH FIRST 10 ROWS ONLY; -- Use a bind variable here if you want: FETCH FIRST :num_rows ROWS ONLY

3. 03_top_10_sqls_cpu.sql - Top 10 SQLs by CPU

Original Query:

SQL

SELECT sql_id, COUNT(*) AS samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN :begin_time AND :end_time
  AND session_state = 'ON CPU'
GROUP BY sql_id
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

Assessment:
- Good: Correctly identifies CPU-bound SQL.

Tuning/RDS Adaptation:

Get the SQL text: This is essential for understanding the query. You can join DBA_HIST_SQLTEXT.
Consider total elapsed time: While CPU is a focus, sometimes a query is high CPU and high elapsed time due to other waits.

Recommendation:

SQL

-- Top N SQLs by CPU consumption during a specific period
SELECT
    h.sql_id,
    s.sql_text, -- Get SQL text
    COUNT(*) AS cpu_samples
FROM dba_hist_active_sess_history h
JOIN dba_hist_sqltext s ON h.sql_id = s.sql_id AND h.dbid = s.dbid -- Join to get SQL text
WHERE h.sample_time BETWEEN :begin_time AND :end_time
  AND h.session_state = 'ON CPU'
  AND h.sql_id IS NOT NULL -- Exclude background processes/non-SQL activity
GROUP BY h.sql_id, s.sql_text
ORDER BY cpu_samples DESC
FETCH FIRST 10 ROWS ONLY; -- Use a bind variable here if you want: FETCH FIRST :num_rows ROWS ONLY

Note on SQL_TEXT: DBA_HIST_SQLTEXT.SQL_TEXT is a LONG datatype. In many SQL clients, you might need to set SET LONG XXX (e.g., SET LONG 20000) to retrieve the full text, or use DBMS_METADATA.GET_DDL for SQL statements if available and if you have the hash value/address. For ASH, the SQL_TEXT in DBA_HIST_SQLTEXT is often sufficient.

4. 04_top_wait_events.sql - Top wait events

Original Query:

SQL

SELECT event, COUNT(*) AS waits
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN :begin_time AND :end_time
  AND session_state = 'WAITING'
GROUP BY event
ORDER BY waits DESC
FETCH FIRST 10 ROWS ONLY;

Assessment:
- Good: Directly identifies top wait events.

Tuning/RDS Adaptation:

Consider wait class: Grouping by wait_class can provide a higher-level view of the bottleneck (e.g., 'User I/O', 'Concurrency', 'Commit').

Recommendation:

SQL

-- Top N wait events during a specific period
SELECT
    h.event,
    h.wait_class, -- Include wait class
    COUNT(*) AS wait_samples
FROM dba_hist_active_sess_history h
WHERE h.sample_time BETWEEN :begin_time AND :end_time
  AND h.session_state = 'WAITING'
  AND h.wait_class != 'Idle' -- Exclude idle waits
GROUP BY h.event, h.wait_class
ORDER BY wait_samples DESC
FETCH FIRST 10 ROWS ONLY; -- Use a bind variable if desired

5. 05_top_users.sql - Top users by session count

Original Query:

SQL

SELECT user_id, COUNT(*) AS active_sessions
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN :begin_time AND :end_time
GROUP BY user_id
ORDER BY active_sessions DESC;

Assessment:
- Good: Identifies active users.

Tuning/RDS Adaptation:

Get username: The user_id is less readable than the username.

Recommendation:

SQL

-- Top N users by active session samples during a specific period
SELECT
    u.username,
    COUNT(*) AS active_session_samples -- Renamed for clarity (it's active session samples, not raw count)
FROM dba_hist_active_sess_history h
JOIN dba_users u ON h.user_id = u.user_id
WHERE h.sample_time BETWEEN :begin_time AND :end_time
GROUP BY u.username
ORDER BY active_session_samples DESC
FETCH FIRST 10 ROWS ONLY; -- Use a bind variable if desired

6. 06_top_modules_programs.sql - Top programs and modules

Original Query:

SQL

SELECT program, COUNT(*) AS samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN :begin_time AND :end_time
GROUP BY program
ORDER BY samples DESC;

Assessment:
- Good: Identifies programs.

Tuning/RDS Adaptation:

Include module: Often, MODULE provides more granular detail than PROGRAM.

Recommendation:

SQL

-- Top N programs and modules by active session samples during a specific period
SELECT
    h.program,
    h.module, -- Include module for more detail
    COUNT(*) AS active_session_samples
FROM dba_hist_active_sess_history h
WHERE h.sample_time BETWEEN :begin_time AND :end_time
GROUP BY h.program, h.module
ORDER BY active_session_samples DESC
FETCH FIRST 10 ROWS ONLY; -- Use a bind variable if desired

7. 07_db_load.sql - DB Load profile

Original Query:

SQL

SELECT TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') AS minute,
       COUNT(*) AS active_sessions
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN :begin_time AND :end_time
GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI')
ORDER BY minute;

Assessment:
- Excellent: Provides a minute-by-minute (or second-by-second if you adapt TO_CHAR) view of active sessions, directly showing the load profile over time.
Tuning/RDS Adaptation:
- Recommendation: Keep as is. This query is fundamental for visualizing load spikes from ASH. You could get more granular with seconds if needed: TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI:SS').

8. 08_blocking_sessions.sql - Blocking sessions

Original Query:

SQL

SELECT blocking_session, session_id, session_serial#, COUNT(*) AS samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN :begin_time AND :end_time
  AND blocking_session IS NOT NULL
GROUP BY blocking_session, session_id, session_serial#
ORDER BY samples DESC;

Assessment:
- Good: Identifies blocking chains from ASH.

Tuning/RDS Adaptation:

Get more info on blocking/blocked sessions: Username, program, SQL ID.

Recommendation:

SQL

-- Top N blocking/blocked session pairs by active samples
SELECT
    h.blocking_session,
    u_blocker.username AS blocking_username,
    h.session_id,
    h.session_serial#,
    u_blocked.username AS blocked_username,
    h.sql_id AS blocked_sql_id,
    h.event AS blocked_wait_event,
    COUNT(*) AS samples
FROM dba_hist_active_sess_history h
LEFT JOIN dba_users u_blocker ON h.blocking_session_id = u_blocker.user_id -- Note: blocking_session_id exists in ASH
LEFT JOIN dba_users u_blocked ON h.user_id = u_blocked.user_id
WHERE h.sample_time BETWEEN :begin_time AND :end_time
  AND h.blocking_session IS NOT NULL -- This implies it's a session being blocked
GROUP BY
    h.blocking_session,
    u_blocker.username,
    h.session_id,
    h.session_serial#,
    u_blocked.username,
    h.sql_id,
    h.event
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY; -- Use a bind variable if desired

Note: blocking_session_id is the correct column for the ID of the blocking session in dba_hist_active_sess_history.

9. 09_top_objects.sql - Top accessed objects

Original Query:

SQL

SELECT o.object_name, o.object_type, COUNT(*) AS access_count
FROM dba_hist_active_sess_history h
JOIN dba_objects o ON h.current_obj# = o.object_id
WHERE h.sample_time BETWEEN :begin_time AND :end_time
GROUP BY o.object_name, o.object_type
ORDER BY access_count DESC;

Assessment:
- Good: Identifies hot objects.

Tuning/RDS Adaptation:

Include owner: Crucial for unique identification of objects across schemas.
Filter for relevant object types: You might only be interested in TABLE or INDEX.

Recommendation:

SQL

-- Top N accessed objects by active session samples
SELECT
    o.owner AS object_owner, -- Include owner
    o.object_name,
    o.object_type,
    COUNT(*) AS access_samples
FROM dba_hist_active_sess_history h
JOIN dba_objects o ON h.current_obj# = o.object_id AND h.dbid = o.owner_id -- Add dbid/owner_id to join for robustness
WHERE h.sample_time BETWEEN :begin_time AND :end_time
  AND h.current_obj# IS NOT NULL
  AND o.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'AUDSYS', 'RDSADMIN') -- Exclude internal Oracle objects
  AND o.object_type IN ('TABLE', 'INDEX', 'PARTITION', 'SUBPARTITION') -- Focus on common types
GROUP BY o.owner, o.object_name, o.object_type
ORDER BY access_samples DESC
FETCH FIRST 10 ROWS ONLY; -- Use a bind variable if desired

By using these adapted queries, you'll be able to extract much richer and more contextual information from your Oracle RDS AWR/ASH data, enabling a more precise performance diagnosis. Remember to always provide the correct bind variables (:begin_time, :end_time, :begin_snap_id, :end_snap_id) when executing these queries.

Wednesday, July 9, 2025

constraint check - 3

Step-by-Step Implementation

Phase 1: On the `PROD` Database

1. Create a SQL Collection Type (if you haven't already): This type is needed for the PL/SQL block to handle lists of table names efficiently.

SQL

CREATE OR REPLACE TYPE T_VARCHAR2_LIST IS VARRAY(4000) OF VARCHAR2(128);
/

2. Create a Global Temporary Table to store PROD's constraint information: This will hold the exact state of constraints from your PROD environment.

SQL

CREATE GLOBAL TEMPORARY TABLE temp_prod_constraints (
    table_owner         VARCHAR2(128) NOT NULL,
    table_name          VARCHAR2(128) NOT NULL,
    constraint_name     VARCHAR2(128) NOT NULL,
    constraint_type     VARCHAR2(1)   NOT NULL,
    search_condition_vc VARCHAR2(4000), -- Crucial for matching system-generated names
    status              VARCHAR2(8)   NOT NULL,
    validated           VARCHAR2(13)  NOT NULL
) ON COMMIT PRESERVE ROWS;

3. Insert PROD's constraint data into the temporary table: This captures the "source of truth" for your comparison. You'll insert data for the specific schema and tables you're interested in.

SQL

DECLARE
    p_schema_name      VARCHAR2(128) := 'YOUR_PROD_SCHEMA_NAME'; -- <<<<< Set your PROD schema name
    -- <<<<< List your specific table names from PROD here
    p_table_names      T_VARCHAR2_LIST := T_VARCHAR2_LIST(
        'PROD_TABLE_1',
        'PROD_TABLE_2',
        -- Add all your relevant PROD table names here
        'PROD_TABLE_N'
    );
BEGIN
    INSERT INTO temp_prod_constraints (
        table_owner,
        table_name,
        constraint_name,
        constraint_type,
        search_condition_vc,
        status,
        validated
    )
    SELECT
        owner,
        table_name,
        constraint_name,
        constraint_type,
        search_condition_vc, -- Get the condition for matching
        status,
        validated
    FROM
        all_constraints
    WHERE
        owner = p_schema_name
        AND table_name IN (SELECT COLUMN_VALUE FROM TABLE(p_table_names))
        AND constraint_type IN ('C', 'P', 'U') -- Only CHECK, PK, UK (adjust if you need FK)
        AND status = 'ENABLED'; -- Only consider enabled constraints for this comparison
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' rows into temp_prod_constraints from PROD.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error inserting into temp_prod_constraints: ' || SQLERRM);
        RAISE;
END;
/

4. Export data from temp_prod_constraints: Use Toad's Export Wizard for this:

Right-click on temp_prod_constraints in the Schema Browser -> Export Data -> Export Wizard.
Choose Delimited Text File (CSV is highly recommended).
Ensure "Include column headers" is checked.
Save it as a file (e.g., prod_constraints_export.csv) to your local machine.

Phase 2: On the `PROD_COPY` Database

1. Create the SQL Collection Type (if not already present):

SQL

CREATE OR REPLACE TYPE T_VARCHAR2_LIST IS VARRAY(4000) OF VARCHAR2(128);
/

2. Create an identical Global Temporary Table for importing PROD's data: It must have the exact same structure as temp_prod_constraints.

SQL

CREATE GLOBAL TEMPORARY TABLE temp_prod_constraints ( -- Yes, use the same name for simplicity
    table_owner         VARCHAR2(128) NOT NULL,
    table_name          VARCHAR2(128) NOT NULL,
    constraint_name     VARCHAR2(128) NOT NULL,
    constraint_type     VARCHAR2(1)   NOT NULL,
    search_condition_vc VARCHAR2(4000),
    status              VARCHAR2(8)   NOT NULL,
    validated           VARCHAR2(13)  NOT NULL
) ON COMMIT PRESERVE ROWS;

3. Import prod_constraints_export.csv into temp_prod_constraints on PROD_COPY: Use Toad's Import Wizard:

Connect to PROD_COPY.
Right-click on temp_prod_constraints in the Schema Browser -> Import Data -> Import Wizard.
Select "Add File" and choose prod_constraints_export.csv.
Follow the wizard, ensuring correct delimiter, text qualifier, and header row.
Choose "Insert" mode.

4. Perform the Comparison Query: Now you can compare PROD_COPY's actual constraints (ALL_CONSTRAINTS) against the PROD data you just imported (temp_prod_constraints). This query will highlight the differences you're interested in.

SQL

SELECT
    'Difference Type' AS difference_category,
    COALESCE(prod_c.table_owner, copy_c.owner) AS owner,
    COALESCE(prod_c.table_name, copy_c.table_name) AS table_name,
    COALESCE(prod_c.constraint_type, copy_c.constraint_type) AS constraint_type,
    COALESCE(prod_c.search_condition_vc, copy_c.search_condition_vc) AS search_condition,
    prod_c.validated AS prod_validated_status,
    copy_c.validated AS copy_validated_status,
    prod_c.status AS prod_status,
    copy_c.status AS copy_status,
    prod_c.constraint_name AS prod_constraint_name,
    copy_c.constraint_name AS copy_constraint_name
FROM
    temp_prod_constraints prod_c
FULL OUTER JOIN
    all_constraints copy_c
    ON prod_c.table_owner = copy_c.owner
    AND prod_c.table_name = copy_c.table_name
    AND prod_c.constraint_type = copy_c.constraint_type
    AND prod_c.search_condition_vc = copy_c.search_condition_vc -- Match by condition text
WHERE
    -- Only show differences in VALIDATED or STATUS, or where a constraint exists in one but not the other
    (
        prod_c.validated IS DISTINCT FROM copy_c.validated OR
        prod_c.status IS DISTINCT FROM copy_c.status OR
        (prod_c.constraint_name IS NULL AND copy_c.constraint_name IS NOT NULL) OR -- Exists in COPY but not PROD
        (prod_c.constraint_name IS NOT NULL AND copy_c.constraint_name IS NULL)    -- Exists in PROD but not COPY
    )
    AND COALESCE(prod_c.table_owner, copy_c.owner) = 'YOUR_PROD_SCHEMA_NAME' -- <<<<< Replace
    AND COALESCE(prod_c.table_name, copy_c.table_name) IN (
        -- <<<<< List your table names here, they should match the list you used in PROD
        'PROD_TABLE_1',
        'PROD_TABLE_2',
        'PROD_TABLE_N'
    )
    AND COALESCE(prod_c.status, copy_c.status) = 'ENABLED' -- Only enabled constraints for comparison
ORDER BY
    table_name, constraint_type, search_condition;

Understanding the Comparison Query:

FULL OUTER JOIN: This is key. It ensures you see:
- Rows where constraints exist in both (prod_c and copy_c are not null).
- Rows where a constraint exists in PROD (prod_c not null, copy_c null).
- Rows where a constraint exists in PROD_COPY (copy_c not null, prod_c null).
COALESCE: Used to pick the non-null value for columns like owner, table_name, etc., ensuring they appear even if one side of the join is null.
prod_c.search_condition_vc = copy_c.search_condition_vc: This is the critical matching criterion for system-generated constraints. It matches based on the actual logic of the constraint.
WHERE Clause for Differences:
- prod_c.validated IS DISTINCT FROM copy_c.validated: Finds where the VALIDATED status is different. This will spot your PROD (VALIDATED) vs PROD_COPY (NOT VALIDATED) scenarios.
- prod_c.status IS DISTINCT FROM copy_c.status: Finds where the STATUS (ENABLED/DISABLED) is different.
- The IS NULL checks identify constraints present in one environment but not the other (which could also be a root cause of your count differences).
Filtering: The WHERE clause also includes filters for your specific schema and table names, and for ENABLED status (as you previously focused on ENABLED constraints).

By following these steps, you'll get a precise report of the differences in constraint status (and even existence) between your PROD and PROD_COPY environments, giving you the information needed for targeted synchronization actions.

Constraint Check - 2

SELECT

owner,

table_name,

constraint_type,

status,

validated,

COUNT(*) AS constraint_count

FROM

all_constraints

WHERE

owner = 'YOUR_SCHEMA_NAME' -- <<<<< IMPORTANT: Replace with the actual schema owner

AND table_name IN ( -- <<<<< IMPORTANT: List your 40-50 table names here

'TABLE_NAME_1',

'TABLE_NAME_2',

'TABLE_NAME_3',

-- ... up to 40-50 table names

'TABLE_NAME_N'

)

GROUP BY

owner,

table_name,

constraint_type,

status,

validated

ORDER BY

owner,

table_name,

constraint_type, validated DESC; -- Show NOT VALIDATED first for each group

-- Run this SQL command ONCE in your schema (e.g., MY_SCHEMA)

CREATE OR REPLACE TYPE T_VARCHAR2_LIST IS VARRAY(4000) OF VARCHAR2(128);

SET SERVEROUTPUT ON SIZE UNLIMITED; -- Ensure output is not truncated and displayed in your SQL client

DECLARE

-- Configuration Parameters (IMPORTANT: Customize these)

p_schema_name VARCHAR2(128) := 'YOUR_SCHEMA_NAME'; -- <<<<< Set your target schema here

-- <<<<< List your target table names here (up to 40-50)

-- Use the SQL type T_VARCHAR2_LIST defined above

p_table_names T_VARCHAR2_LIST := T_VARCHAR2_LIST(

'TABLE_NAME_1',

'TABLE_NAME_2',

'TABLE_NAME_3',

-- Add all your table names here, separated by commas

'TABLE_NAME_N'

);

-- Internal Variables

v_sql_stmt VARCHAR2(1000);

v_constraint_count NUMBER := 0;

v_separator VARCHAR2(80) := RPAD('-', 80, '-'); -- For visual separation

v_header_format VARCHAR2(200); -- For formatted output header

v_detail_format VARCHAR2(200); -- For formatted output details

BEGIN

-- Initialize format strings for visual output

v_header_format := RPAD('OWNER', 15) || RPAD('TABLE_NAME', 25) || RPAD('CONSTRAINT_NAME', 30) || RPAD('TYPE', 8) || RPAD('STATUS', 8) || 'VALIDATED';

v_detail_format := RPAD('%s', 15) || RPAD('%s', 25) || RPAD('%s', 30) || RPAD('%s', 8) || RPAD('%s', 8) || '%s';

-- --- REPORTING PHASE ---

DBMS_OUTPUT.PUT_LINE(v_separator);

DBMS_OUTPUT.PUT_LINE('-- Constraint Analysis Report for Schema: ' || p_schema_name);

DBMS_OUTPUT.PUT_LINE('-- Tables being analyzed: ' || p_table_names.COUNT || ' tables.');

DBMS_OUTPUT.PUT_LINE(v_separator);

DBMS_OUTPUT.PUT_LINE(' ');

DBMS_OUTPUT.PUT_LINE('Finding constraints that are:');

DBMS_OUTPUT.PUT_LINE(' - Enabled');

DBMS_OUTPUT.PUT_LINE(' - VALIDATED (target for conversion to NOVALIDATE)');

DBMS_OUTPUT.PUT_LINE(' - Of type CHECK (C), PRIMARY KEY (P), or UNIQUE (U)');

DBMS_OUTPUT.PUT_LINE(' ');

DBMS_OUTPUT.PUT_LINE(v_separator);

DBMS_OUTPUT.PUT_LINE('Here are the constraints found matching the criteria:');

DBMS_OUTPUT.PUT_LINE(v_separator);

DBMS_OUTPUT.PUT_LINE(v_header_format);

DBMS_OUTPUT.PUT_LINE(v_separator);

-- Corrected SQL query using TABLE() to convert PL/SQL collection to SQL collection

FOR con_rec IN (

SELECT /*+ NO_PARALLEL */

ac.owner,

ac.table_name,

ac.constraint_name,

ac.constraint_type,

ac.status,

ac.validated,

ac.search_condition_vc

FROM

all_constraints ac

WHERE

ac.owner = p_schema_name

AND ac.table_name IN (SELECT COLUMN_VALUE FROM TABLE(p_table_names)) -- Corrected line

AND ac.constraint_type IN ('C', 'P', 'U')

AND ac.status = 'ENABLED'

AND ac.validated = 'VALIDATED'

ORDER BY

ac.owner,

ac.table_name,

ac.constraint_type,

CASE WHEN ac.validated = 'NOT VALIDATED' THEN 1 ELSE 2 END,

ac.constraint_name

) LOOP

v_constraint_count := v_constraint_count + 1;

-- Output constraint details in a formatted way

DBMS_OUTPUT.PUT_LINE(

UTL_LMS.FORMAT_MESSAGE(v_detail_format,

con_rec.owner,

con_rec.table_name,

con_rec.constraint_name,

con_rec.constraint_type,

con_rec.status,

con_rec.validated)

);

-- Print search_condition_vc if available (for CHECK constraints)

IF con_rec.constraint_type = 'C' AND con_rec.search_condition_vc IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(RPAD(' ', 15) || 'Condition: ' || con_rec.search_condition_vc);

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE(v_separator);

DBMS_OUTPUT.PUT_LINE('Total constraints found matching criteria: ' || v_constraint_count);

DBMS_OUTPUT.PUT_LINE(v_separator);

-- --- SQL GENERATION PHASE ---

DBMS_OUTPUT.PUT_LINE(' ');

DBMS_OUTPUT.PUT_LINE(v_separator);

DBMS_OUTPUT.PUT_LINE('-- Generated SQL to set identified constraints to ENABLE NOVALIDATE --');

DBMS_OUTPUT.PUT_LINE('-- IMPORTANT: REVIEW THESE COMMANDS CAREFULLY BEFORE UNCOMMENTING AND EXECUTING!');

DBMS_OUTPUT.PUT_LINE(v_separator);

IF v_constraint_count > 0 THEN

-- Re-querying using the corrected method to ensure consistency

FOR con_rec IN (

SELECT

ac.owner,

ac.table_name,

ac.constraint_name,

ac.search_condition_vc

FROM

all_constraints ac

WHERE

ac.owner = p_schema_name

AND ac.table_name IN (SELECT COLUMN_VALUE FROM TABLE(p_table_names)) -- Corrected line

AND ac.constraint_type IN ('C', 'P', 'U')

AND ac.status = 'ENABLED'

AND ac.validated = 'VALIDATED'

ORDER BY

ac.owner, ac.table_name, ac.constraint_name

) LOOP

v_sql_stmt := 'ALTER TABLE ' || con_rec.owner || '.' || con_rec.table_name ||

' ENABLE CONSTRAINT ' || con_rec.constraint_name || ' NOVALIDATE;';

DBMS_OUTPUT.PUT_LINE(v_sql_stmt);

-- UNCOMMENT THE LINE BELOW AND THE COMMIT AT THE END TO EXECUTE THE STATEMENTS DIRECTLY.

-- EXECUTE IMMEDIATE v_sql_stmt;

END LOOP;

ELSE

DBMS_OUTPUT.PUT_LINE('-- No constraints found matching the criteria. No SQL generated.');

END IF;

DBMS_OUTPUT.PUT_LINE(v_separator);

DBMS_OUTPUT.PUT_LINE('-- End of generated SQL. ');

-- COMMIT; -- UNCOMMENT THIS IF YOU UNCOMMENTED EXECUTE IMMEDIATE ABOVE

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(v_separator);

DBMS_OUTPUT.PUT_LINE('!!! An ERROR occurred during script execution !!!');

DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

DBMS_OUTPUT.PUT_LINE(v_separator);

RAISE; -- Re-raise the exception to stop execution and indicate failure

END;

CONSTRAINT CHECK

Phase 1: On the Source Database (e.g., Production)

1. Create a Global Temporary Table to store source constraint information. This table will hold the constraint details from your production environment.

SQL

CREATE GLOBAL TEMPORARY TABLE temp_source_constraints (

table_owner VARCHAR2(128) NOT NULL,

table_name VARCHAR2(128) NOT NULL,

constraint_name VARCHAR2(128) NOT NULL,

constraint_type VARCHAR2(1) NOT NULL,

search_condition_vc VARCHAR2(4000), -- Stores the actual condition for matching

status VARCHAR2(8) NOT NULL,

validated VARCHAR2(13) NOT NULL

) ON COMMIT PRESERVE ROWS;

ON COMMIT PRESERVE ROWS: This ensures the data you insert into this temporary table persists for the duration of your session, even after a COMMIT.

2. Insert relevant constraint data from your source table into the temporary table. We'll focus on CHECKconstraints (CONSTRAINT_TYPE = 'C'). You can also include PRIMARY KEY ('P') and UNIQUE ('U') constraints if you need to synchronize their VALIDATED status as well.

SQL

INSERT INTO temp_source_constraints (

table_owner,

table_name,

constraint_name,

constraint_type,

search_condition_vc,

status,

validated

)

SELECT

owner,

table_name,

constraint_name,

constraint_type,

search_condition_vc, -- This is crucial for matching across databases

status,

validated

FROM

all_constraints

WHERE

owner = 'YOUR_SOURCE_SCHEMA_NAME' -- <<<<< IMPORTANT: Replace with your actual production schema name

AND table_name = 'YOUR_SOURCE_TABLE_NAME' -- <<<<< IMPORTANT: Replace with your actual production table name

AND constraint_type IN ('C', 'P', 'U'); -- Add 'P', 'U' if you need to sync PK/UK validated status

COMMIT; -- Commit the insert into the GTT (data remains due to ON COMMIT PRESERVE ROWS)

3. Export the data from temp_source_constraints table. Use Toad's Export Wizard for this:

In Toad, open the Schema Browser.Navigate to your user's temporary tables or find temp_source_constraints.Right-click on temp_source_constraints and choose Export Data > Export Wizard.On the "Select Output Format" page, choose Delimited Text File (CSV is generally the most portable and easiest to import).Ensure "Include column headers" is checked.Specify a file name (e.g., source_constraints_sync.csv) and a location on your local machine.Click through the remaining steps and "Export Data Now".

Phase 2: On the Target Database (e.g., Development/Test)

1. Create an identical Global Temporary Table on the target database. The structure must be exactly the same as the one on the source.

SQL

CREATE GLOBAL TEMPORARY TABLE temp_target_constraints_sync (

table_owner VARCHAR2(128) NOT NULL,

table_name VARCHAR2(128) NOT NULL,

constraint_name VARCHAR2(128) NOT NULL,

constraint_type VARCHAR2(1) NOT NULL,

search_condition_vc VARCHAR2(4000),

status VARCHAR2(8) NOT NULL,

validated VARCHAR2(13) NOT NULL

) ON COMMIT PRESERVE ROWS;

2. Import the source_constraints_sync.csv file into temp_target_constraints_sync on the target database.Use Toad's Import Wizard:

In Toad, connect to your target database.Open the Schema Browser.Navigate to your user's temporary tables or find temp_target_constraints_sync.Right-click on temp_target_constraints_sync and choose Import Data > Import Wizard.On the "Import File" page, click "Add File" and browse to select the source_constraints_sync.csv file you exported earlier.Follow the wizard, ensuring correct delimiter, text qualifier, and that "Column name as header" is checked.On the "Select Target" page, ensure "A single existing table" is selected and temp_target_constraints_sync is chosen.Choose "Insert" as the import mode.Click through the remaining steps and "Import Data Now".

3. Generate and Execute Dynamic SQL to Synchronize VALIDATED Status. This is the core synchronization step. This PL/SQL block will generate ALTER TABLE ... ENABLE CONSTRAINT ... NOVALIDATE statements for constraints on your target table that meet these conditions:

They are CHECK, PRIMARY KEY, or UNIQUE constraints.Their SEARCH_CONDITION_VC matches a constraint from the temp_target_constraints_sync table (which holds the source's constraint info).Their current VALIDATED status on the target is 'VALIDATED', but the source constraint (from temp_target_constraints_sync) has a VALIDATED status of 'NOT VALIDATED'.

SQL

SET SERVEROUTPUT ON SIZE UNLIMITED; -- Enable output in your SQL client

DECLARE

v_sql_stmt VARCHAR2(1000);

-- IMPORTANT: Replace these with your actual target table and schema names

v_target_table_name VARCHAR2(128) := 'YOUR_TARGET_TABLE_NAME';

v_target_schema_name VARCHAR2(128) := 'YOUR_TARGET_SCHEMA_NAME';

BEGIN

DBMS_OUTPUT.PUT_LINE('-- Generating ALTER statements to synchronize VALIDATED status for ' || v_target_schema_name || '.' || v_target_table_name);

DBMS_OUTPUT.PUT_LINE('-- Review these commands carefully before executing.');

DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');

FOR con_rec IN (

SELECT

ac.owner AS target_owner,

ac.table_name AS target_table,

ac.constraint_name AS target_constraint_name,

ac.validated AS current_target_validated_status,

tsc.validated AS source_validated_status_to_match,

tsc.search_condition_vc AS source_constraint_condition

FROM

all_constraints ac

JOIN

temp_target_constraints_sync tsc

ON ac.owner = v_target_schema_name -- Match target schema

AND ac.table_name = v_target_table_name -- Match target table

AND ac.constraint_type = tsc.constraint_type -- Match constraint type ('C', 'P', 'U')

AND ac.search_condition_vc = tsc.search_condition_vc -- *** CRITICAL: Match by actual condition ***

WHERE

ac.owner = v_target_schema_name

AND ac.table_name = v_target_table_name

AND ac.constraint_type IN ('C', 'P', 'U') -- Only CHECK, PK, UK constraints

AND ac.status = 'ENABLED' -- Only consider enabled constraints on target

AND ac.validated = 'VALIDATED' -- Target is currently VALIDATED

AND tsc.validated = 'NOT VALIDATED' -- Source (from GTT) is NOT VALIDATED

) LOOP

-- Generate the ALTER statement to set the target constraint to NOVALIDATE

v_sql_stmt := 'ALTER TABLE ' || con_rec.target_owner || '.' || con_rec.target_table ||

' ENABLE CONSTRAINT ' || con_rec.target_constraint_name || ' NOVALIDATE; -- Matched by condition: ' || con_rec.source_constraint_condition;

DBMS_OUTPUT.PUT_LINE(v_sql_stmt);

-- UNCOMMENT THE LINE BELOW TO EXECUTE THE STATEMENTS DIRECTLY.

-- EXECUTE IMMEDIATE v_sql_stmt;

END LOOP;

DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');

DBMS_OUTPUT.PUT_LINE('-- Synchronization script generation complete.');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

RAISE; -- Re-raise the exception after printing

END;

Final Steps after Running Phase 2, Step 3:

Review the Output: Carefully examine the ALTER TABLE statements printed by DBMS_OUTPUT.Execute Commands: If you didn't uncomment EXECUTE IMMEDIATE, copy the generated ALTER TABLEstatements and run them manually on your target database.Verify: After execution, you can re-query ALL_CONSTRAINTS on your target table to confirm that the VALIDATEDstatus of the relevant constraints now matches the NOT VALIDATED status from your source.

This methodical approach ensures that even with system-generated constraint names, you can accurately identify and synchronize the VALIDATED status based on the actual constraint logic.

SET SERVEROUTPUT ON SIZE UNLIMITED; -- Enable output in your SQL client

DECLARE

v_sql_stmt VARCHAR2(1000);

-- IMPORTANT: Replace these with your actual target table and schema names

v_target_table_name VARCHAR2(128) := 'YOUR_TARGET_TABLE_NAME';

v_target_schema_name VARCHAR2(128) := 'YOUR_TARGET_SCHEMA_NAME';

BEGIN

DBMS_OUTPUT.PUT_LINE('-- Generating ALTER statements to synchronize VALIDATED status for ' || v_target_schema_name || '.' || v_target_table_name);

DBMS_OUTPUT.PUT_LINE('-- Review these commands carefully before executing.');

DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');

FOR con_rec IN (

SELECT

ac.owner AS target_owner,

ac.table_name AS target_table,

ac.constraint_name AS target_constraint_name,

ac.validated AS current_target_validated_status,

tsc.validated AS source_validated_status_to_match,

tsc.search_condition_vc AS source_constraint_condition

FROM

all_constraints ac

JOIN

temp_target_constraints_sync tsc

ON ac.owner = v_target_schema_name -- Match target schema

AND ac.table_name = v_target_table_name -- Match target table

AND ac.constraint_type = tsc.constraint_type -- Match constraint type ('C', 'P', 'U')

AND ac.search_condition_vc = tsc.search_condition_vc -- *** CRITICAL: Match by actual condition ***

WHERE

ac.owner = v_target_schema_name

AND ac.table_name = v_target_table_name

AND ac.constraint_type IN ('C', 'P', 'U') -- Only CHECK, PK, UK constraints

AND ac.status = 'ENABLED' -- Only consider enabled constraints on target

AND ac.validated = 'VALIDATED' -- Target is currently VALIDATED

AND tsc.validated = 'NOT VALIDATED' -- Source (from GTT) is NOT VALIDATED

) LOOP

-- Generate the ALTER statement to set the target constraint to NOVALIDATE

v_sql_stmt := 'ALTER TABLE ' || con_rec.target_owner || '.' || con_rec.target_table ||

' ENABLE CONSTRAINT ' || con_rec.target_constraint_name || ' NOVALIDATE; -- Matched by condition: ' || con_rec.source_constraint_condition;

DBMS_OUTPUT.PUT_LINE(v_sql_stmt);

-- UNCOMMENT THE LINE BELOW TO EXECUTE THE STATEMENTS DIRECTLY.

-- EXECUTE IMMEDIATE v_sql_stmt;

END LOOP;

DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');

DBMS_OUTPUT.PUT_LINE('-- Synchronization script generation complete.');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

RAISE; -- Re-raise the exception after printing

END;

what does this script do?

This PL/SQL script is designed to synchronize the VALIDATED status of certain constraints on a target Oracle table to match a "NOT VALIDATED" state found on a corresponding source table, even when constraint names differ.

Here's a breakdown of what the script does, step by step:Enables Server Output (SET SERVEROUTPUT ON SIZE UNLIMITED;): This command, run before the PL/SQL block, ensures that any messages generated by DBMS_OUTPUT.PUT_LINE within the block are displayed in your SQL client (like SQL*Plus, SQL Developer, Toad, etc.).Declares Variables:v_sql_stmt VARCHAR2(1000): A variable to hold the dynamically generated ALTER TABLE SQL statements.v_target_table_name VARCHAR2(128): Placeholder for the name of the table on the target database whose constraints you want to modify. You must replace 'YOUR_TARGET_TABLE_NAME' with the actual table name.v_target_schema_name VARCHAR2(128): Placeholder for the schema (owner) of the target table. You must replace 'YOUR_TARGET_SCHEMA_NAME' with the actual schema name.Prints Header Information: DBMS_OUTPUT.PUT_LINE statements print comments and a separator to the output, providing context about what the script is doing.Iterates Through Relevant Constraints (FOR con_rec IN (...) LOOP): This is the core logic. The FOR loop iterates over a result set generated by a SELECT statement. This SELECT statement is designed to identify specific constraints that need their VALIDATED status changed:FROM all_constraints ac: Queries the ALL_CONSTRAINTS data dictionary view, which contains information about all constraints accessible to the current user in the current database (this is your targetdatabase).JOIN temp_target_constraints_sync tsc: It joins ALL_CONSTRAINTS (the actual state on target) with temp_target_constraints_sync (which is a global temporary table containing the source's constraint information that you previously imported).ON ac.owner = v_target_schema_name AND ac.table_name = v_target_table_name: These conditions ensure the join focuses on the specific target table you're interested in.AND ac.constraint_type = tsc.constraint_type: This matches constraints by their type (e.g., 'C' for CHECK, 'P' for Primary Key, 'U' for Unique).AND ac.search_condition_vc = tsc.search_condition_vc: This is the MOST CRITICAL part.Since system-generated constraint names (SYS_C...) are inconsistent between databases, this condition matches constraints based on their actual logical definition (e.g., salary > 0 or status IN ('A', 'B')). This allows the script to find corresponding constraints even if their names are different.WHERE ac.owner = v_target_schema_name AND ac.table_name = v_target_table_name: Redundant but harmless, ensures focus.AND ac.constraint_type IN ('C', 'P', 'U'): Filters to include only CHECK, PRIMARY KEY, and UNIQUEconstraints, as these are the ones whose VALIDATED status you typically manage.AND ac.status = 'ENABLED': Only considers constraints on the target table that are currently enabled. You wouldn't be enabling a disabled constraint to NOVALIDATE in this context.AND ac.validated = 'VALIDATED': This filters for constraints on the target table that are currently marked as VALIDATED.AND tsc.validated = 'NOT VALIDATED': This filters for constraints where the source (represented by tsc) has a NOT VALIDATED status.In essence, the loop processes every constraint on the target table that is currently ENABLED VALIDATED but corresponds to a constraint on the source table that was ENABLED NOT VALIDATED.Generates Dynamic ALTER TABLE Statement: Inside the loop, for each con_rec (constraint record) that meets the criteria, the script constructs an ALTER TABLE statement in the v_sql_stmt variable:ALTER TABLE [target_owner].[target_table]ENABLE CONSTRAINT [target_constraint_name]NOVALIDATE;A comment -- Matched by condition: [source_constraint_condition] is appended for clarity, showing the actual condition used for matching.Prints Generated Statement: DBMS_OUTPUT.PUT_LINE(v_sql_stmt); prints the generated ALTER TABLEcommand to the client's output. This allows you to review the commands before executing them.Optional: Executes Statement Directly (EXECUTE IMMEDIATE v_sql_stmt;): This line is commented out (--). If you uncomment it, the script will not just print the commands, but it will also immediately execute each ALTER TABLE statement as it's generated. It is highly recommended to keep this commented out initially and review the generated output before manual execution.Prints Footer Information: After the loop finishes, more DBMS_OUTPUT.PUT_LINE statements confirm the script's completion.Error Handling (EXCEPTION WHEN OTHERS THEN ...): This block catches any unexpected errors that might occur during the script's execution. If an error occurs, it prints the SQL error message (SQLERRM) and then RAISE;re-raises the exception, stopping the PL/SQL block's execution and propagating the error back to the client.In summary, this script is a tool to automate the process of bringing the VALIDATED status of target table constraints in line with those of a source table, specifically converting VALIDATED constraints to NOT VALIDATEDbased on a match of their logical conditions. It's typically used as part of a larger data synchronization or migration process where consistency of constraint validation status is required, especially for ALTER TABLE EXCHANGE PARTITIONoperations.

Saturday, May 17, 2025

SSL

#!/bin/bash

read -s -p "Enter DB password: " DB_PASS

echo ""

DB_LIST_FILE="db_list.txt"

while IFS= read -r line || [[ -n "$line" ]]; do

[[ -z "$line" || "$line" =~ ^# ]] && continue

USER_CONN="${line%%@*}"

DB_DESC="${line#*@}"

FULL_CONN="$USER_CONN/$DB_PASS@$DB_DESC"

# Execute SQL and capture output

OUTPUT=$(sqlplus -s "$FULL_CONN" <<EOF

SET SERVEROUTPUT ON

SET FEEDBACK OFF

SET HEADING OFF

WHENEVER SQLERROR EXIT SQL.SQLCODE

DECLARE

l_protocol VARCHAR2(10);

l_db_name VARCHAR2(50);

BEGIN

SELECT SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL')

INTO l_protocol

FROM dual;

SELECT name INTO l_db_name FROM v\$database;

DBMS_OUTPUT.PUT_LINE('DB_NAME=' || l_db_name);

DBMS_OUTPUT.PUT_LINE('Protocol=' || l_protocol);

IF UPPER(l_protocol) = 'TCPS' THEN

DBMS_OUTPUT.PUT_LINE('✅ SSL is enabled for database ' || l_db_name || ' (TCPS connection).');

ELSE

DBMS_OUTPUT.PUT_LINE('❌ SSL is NOT enabled for database ' || l_db_name || ' (protocol: ' || l_protocol || ').');

END IF;

END;

EXIT;

EOF

)

# Extract DB_NAME from output

DB_NAME=$(echo "$OUTPUT" | grep '^DB_NAME=' | cut -d= -f2)

echo "---------------------------"

echo "Connecting to: $DB_NAME"

echo "$OUTPUT"

echo "---------------------------"

echo ""

done < "$DB_LIST_FILE"

revanth@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=dbhost1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL1)))'

revanth@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=dbhost2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL2)))'

Ryalli's world

Thursday, July 10, 2025

ASH/AWR Reports

Method 1: Downloading via the AWS RDS Console (Easiest)

Method 2: Downloading via SQL Client using `rdsadmin.rds_file_util.read_text_file`

Wednesday, July 9, 2025

constraint check - 3

Step-by-Step Implementation

Phase 1: On the `PROD` Database

Phase 2: On the `PROD_COPY` Database

Understanding the Comparison Query:

Constraint Check - 2

CONSTRAINT CHECK

Saturday, May 17, 2025

SSL

Blog Archive

About Me

Ryalli's world

Thursday, July 10, 2025

ASH/AWR Reports

Method 1: Downloading via the AWS RDS Console (Easiest)

Method 2: Downloading via SQL Client using rdsadmin.rds_file_util.read_text_file

Wednesday, July 9, 2025

constraint check - 3

Step-by-Step Implementation

Phase 1: On the PROD Database

Phase 2: On the PROD_COPY Database

Understanding the Comparison Query:

Constraint Check - 2

CONSTRAINT CHECK

Saturday, May 17, 2025

SSL

Blog Archive

About Me

Method 2: Downloading via SQL Client using `rdsadmin.rds_file_util.read_text_file`

Phase 1: On the `PROD` Database

Phase 2: On the `PROD_COPY` Database