-- Generates DDL to enable constraints with NOVALIDATE for specified schemas
SELECT
-- Order foreign keys ('R') last to minimize dependency issues
CASE WHEN constraint_type = 'R' THEN 2 ELSE 1 END AS sort_order,
-- Construct DDL statement with quoted identifiers for safety
'ALTER TABLE "' || owner || '"."' || table_name ||
'" ENABLE NOVALIDATE CONSTRAINT "' || constraint_name || '";' AS ddl_statement
FROM
dba_constraints
WHERE
-- Target specified schemas
owner IN ('SCHEMA_A', 'SCHEMA_B', 'SCHEMA_C', 'SCHEMA_D', 'SCHEMA_E', 'SCHEMA_F', 'SCHEMA_G')
-- Exclude constraints already in ENABLED NOVALIDATE state
AND NOT (status = 'ENABLED' AND validated = 'NOT VALIDATED')
-- Include only primary key, unique, foreign key, and check constraints
AND constraint_type IN ('P', 'U', 'R', 'C')
-- Exclude system-generated constraints to avoid ORA-31603
AND constraint_name NOT LIKE 'SYS_C%'
ORDER BY
sort_order, table_name, constraint_name;
-
- File: precheck_sequences.sql
SET LINESIZE 4000
SET PAGESIZE 0
SPOOL precheck_sequences.txt
SELECT
sequence_owner AS schema_name,
COUNT(*) AS sequence_count,
LISTAGG(sequence_name, ', ') WITHIN GROUP (ORDER BY sequence_name) AS sequence_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7') -- Replace with your 7 schemas
GROUP BY sequence_owner
UNION ALL
SELECT
'TOTAL' AS schema_name,
COUNT(*) AS sequence_count,
NULL AS sequence_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
ORDER BY schema_name;
SPOOL OFF
-- File: generate_sequences_increment_by_1.sql
SET LINESIZE 4000
SET PAGESIZE 0
SPOOL create_sequences_increment_by_1.sql
SELECT
'CREATE SEQUENCE ' || sequence_owner || '.' || sequence_name ||
' START WITH ' || last_number ||
' INCREMENT BY 1' ||
CASE WHEN max_value IS NULL THEN ' NOMAXVALUE' ELSE ' MAXVALUE ' || max_value END ||
CASE WHEN min_value IS NULL THEN ' NOMINVALUE' ELSE ' MINVALUE ' || min_value END ||
CASE WHEN cycle_flag = 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END ||
CASE WHEN cache_size = 0 THEN ' NOCACHE' ELSE ' CACHE ' || cache_size END ||
CASE WHEN order_flag = 'Y' THEN ' ORDER' ELSE ' NOORDER' END ||
CASE WHEN keep_value = 'N' THEN ' NOKEEP' ELSE ' KEEP' END ||
CASE WHEN scale_flag = 'N' THEN ' NOSCALE' WHEN scale_flag = 'Y' THEN ' SCALE' ELSE '' END ||
CASE WHEN session_flag = 'N' THEN ' GLOBAL' WHEN session_flag = 'Y' THEN ' SESSION' ELSE '' END || ';' AS create_statement
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7') -- Replace with your 7 schemas
ORDER BY sequence_owner, sequence_name;
SPOOL OFF
-- File: generate_sequences_preserve_all.sql
SET LINESIZE 4000
SET PAGESIZE 0
SPOOL create_sequences_preserve_all.sql
SELECT
'CREATE SEQUENCE ' || sequence_owner || '.' || sequence_name ||
' START WITH ' || last_number ||
' INCREMENT BY ' || increment_by ||
CASE WHEN max_value IS NULL THEN ' NOMAXVALUE' ELSE ' MAXVALUE ' || max_value END ||
CASE WHEN min_value IS NULL THEN ' NOMINVALUE' ELSE ' MINVALUE ' || min_value END ||
CASE WHEN cycle_flag = 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END ||
CASE WHEN cache_size = 0 THEN ' NOCACHE' ELSE ' CACHE ' || cache_size END ||
CASE WHEN order_flag = 'Y' THEN ' ORDER' ELSE ' NOORDER' END ||
CASE WHEN keep_value = 'N' THEN ' NOKEEP' ELSE ' KEEP' END ||
CASE WHEN scale_flag = 'N' THEN ' NOSCALE' WHEN scale_flag = 'Y' THEN ' SCALE' ELSE '' END ||
CASE WHEN session_flag = 'N' THEN ' GLOBAL' WHEN session_flag = 'Y' THEN ' SESSION' ELSE '' END || ';' AS create_statement
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7') -- Replace with your 7 schemas
ORDER BY sequence_owner, sequence_name;
SPOOL OFF
-- File: postcheck_sequences.sql
SET LINESIZE 4000
SET PAGESIZE 0
SPOOL postcheck_sequences.txt
SELECT
sequence_owner AS schema_name,
COUNT(*) AS sequence_count,
LISTAGG(sequence_name, ', ') WITHIN GROUP (ORDER BY sequence_name) AS sequence_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7') -- Replace with your 7 schemas
GROUP BY sequence_owner
UNION ALL
SELECT
'TOTAL' AS schema_name,
COUNT(*) AS sequence_count,
NULL AS sequence_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
ORDER BY schema_name;
SPOOL OFF
===
SET LINESIZE 4000
SET PAGESIZE 0
SPOOL precheck_sequences_and_constraints.txt
-- Sequences Pre-Check
SELECT
'SEQUENCES,' || sequence_owner AS schema_name,
COUNT(*) AS object_count,
LISTAGG(sequence_name, ', ') WITHIN GROUP (ORDER BY sequence_name) AS object_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7') -- Replace with your 7 schemas
GROUP BY sequence_owner
UNION ALL
SELECT
'SEQUENCES,TOTAL' AS schema_name,
COUNT(*) AS object_count,
NULL AS object_names
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
UNION ALL
-- Constraints Pre-Check
SELECT
'CONSTRAINTS,' || owner AS schema_name,
COUNT(*) AS object_count,
LISTAGG(constraint_name || ' (' || constraint_type || ')', ', ') WITHIN GROUP (ORDER BY constraint_name) AS object_names
FROM all_constraints
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
AND constraint_type IN ('P', 'U', 'R', 'C') -- Primary, Unique, Foreign, Check
GROUP BY owner
UNION ALL
SELECT
'CONSTRAINTS,TOTAL' AS schema_name,
COUNT(*) AS object_count,
NULL AS object_names
FROM all_constraints
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
AND constraint_type IN ('P', 'U', 'R', 'C')
ORDER BY schema_name;
SPOOL OFF
SET LINESIZE 4000
SET PAGESIZE 0
SPOOL postcheck_sequences_and_constraints.txt
-- Sequence Counts and Names
SELECT
'SEQUENCES,' || sequence_owner AS schema_name,
COUNT(*) AS object_count
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
GROUP BY sequence_owner
UNION ALL
SELECT
'SEQUENCES,TOTAL' AS schema_name,
COUNT(*) AS object_count
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
UNION ALL
SELECT
'SEQUENCES,' || sequence_owner AS schema_name,
sequence_name AS object_count
FROM all_sequences
WHERE sequence_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
UNION ALL
-- Constraint Counts and Names
SELECT
'CONSTRAINTS,' || owner AS schema_name,
COUNT(*) AS object_count
FROM all_constraints
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
AND constraint_type IN ('P', 'U', 'R', 'C')
GROUP BY owner
UNION ALL
SELECT
'CONSTRAINTS,TOTAL' AS schema_name,
COUNT(*) AS object_count
FROM all_constraints
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
AND constraint_type IN ('P', 'U', 'R', 'C')
UNION ALL
SELECT
'CONSTRAINTS,' || owner AS schema_name,
constraint_name || ' (' || constraint_type || ')' AS object_count
FROM all_constraints
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7')
AND constraint_type IN ('P', 'U', 'R', 'C')
ORDER BY schema_name, object_count;
SPOOL OFF
=============
DECLARE
CURSOR csr IS
SELECT sequence_name,
sequence_owner AS schema_name,
last_number,
min_value,
max_value,
increment_by,
cycle_flag,
order_flag,
cache_size,
keep_value,
scale_flag,
session_flag
FROM all_sequences
WHERE sequence_owner = 'YOUR_SCHEMA'; -- Replace with your source schema
v_total_sequences NUMBER;
BEGIN
-- Count total sequences for validation
SELECT COUNT(*) INTO v_total_sequences
FROM all_sequences
WHERE sequence_owner = 'YOUR_SCHEMA';
-- Output header for validation report
DBMS_OUTPUT.PUT_LINE('Total sequences found in schema ' || 'YOUR_SCHEMA' || ': ' || v_total_sequences);
DBMS_OUTPUT.PUT_LINE('Schema | Sequence Name | Start With | Increment By | Min Value | Max Value | Cycle | Order | Cache | Keep | Scale | Session | Create Statement');
DBMS_OUTPUT.PUT_LINE('-------|---------------|------------|--------------|-----------|-----------|-------|-------|-------|------|-------|--------|-----------------');
FOR rec IN csr LOOP
-- Output validation data
DBMS_OUTPUT.PUT_LINE(
rec.schema_name || ' | ' ||
rec.sequence_name || ' | ' ||
rec.last_number || ' | ' ||
'1' || ' | ' ||
NVL(TO_CHAR(rec.min_value), 'NULL') || ' | ' ||
NVL(TO_CHAR(rec.max_value), 'NULL') || ' | ' ||
rec.cycle_flag || ' | ' ||
rec.order_flag || ' | ' ||
rec.cache_size || ' | ' ||
rec.keep_value || ' | ' ||
NVL(rec.scale_flag, 'N/A') || ' | ' ||
rec.session_flag || ' | ' ||
'CREATE SEQUENCE ' || rec.schema_name || '.' || rec.sequence_name ||
' START WITH ' || rec.last_number ||
' INCREMENT BY 1' ||
CASE WHEN rec.max_value IS NULL THEN ' NOMAXVALUE' ELSE ' MAXVALUE ' || rec.max_value END ||
CASE WHEN rec.min_value IS NULL THEN ' NOMINVALUE' ELSE ' MINVALUE ' || rec.min_value END ||
CASE WHEN rec.cycle_flag = 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END ||
CASE WHEN rec.cache_size = 0 THEN ' NOCACHE' ELSE ' CACHE ' || rec.cache_size END ||
CASE WHEN rec.order_flag = 'Y' THEN ' ORDER' ELSE ' NOORDER' END ||
CASE WHEN rec.keep_value = 'N' THEN ' NOKEEP' ELSE ' KEEP' END ||
CASE WHEN rec.scale_flag = 'N' THEN ' NOSCALE' WHEN rec.scale_flag = 'Y' THEN ' SCALE' ELSE '' END ||
CASE WHEN rec.session_flag = 'N' THEN ' GLOBAL' WHEN rec.session_flag = 'Y' THEN ' SESSION' ELSE '' END || ';'
);
END LOOP;
-- Final instruction
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Copy the CREATE SEQUENCE statements above and execute them in the target database.');
DBMS_OUTPUT.PUT_LINE('Ensure the schema ' || '''YOUR_SCHEMA''' || ' exists in the target database.');
DBMS_OUTPUT.PUT_LINE('Verify that ' || v_total_sequences || ' sequences are created in the target database.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RAISE;
END;
/
SELECT
d.name AS db_name,
d.db_unique_name,
d.open_mode,
CASE
WHEN d.database_role = 'PRIMARY' THEN 'PRIMARY'
WHEN d.database_role = 'PHYSICAL STANDBY' THEN 'PHYSICAL STANDBY'
WHEN d.database_role = 'LOGICAL STANDBY' THEN 'LOGICAL STANDBY'
WHEN d.database_role = 'SNAPSHOT STANDBY' THEN 'SNAPSHOT STANDBY'
ELSE d.database_role
END AS database_role,
CASE
WHEN d.protection_mode = 'MAXIMUM PROTECTION' THEN 'MAX PROTECTION'
WHEN d.protection_mode = 'MAXIMUM AVAILABILITY' THEN 'MAX AVAILABILITY'
WHEN d.protection_mode = 'MAXIMUM PERFORMANCE' THEN 'MAX PERFORMANCE'
ELSE d.protection_mode
END AS protection_mode,
d.protection_level,
d.remote_archive,
d.flashback_on,
d.log_mode,
CASE WHEN dr.status = 'VALID' THEN 'APPLYING' ELSE dr.status END AS apply_status,
dr.process AS apply_process,
dr.error AS last_error
FROM v$database d
LEFT JOIN v$dataguard_status dr ON 1=1
WHERE ROWNUM = 1;
d.name AS db_name,
d.db_unique_name,
d.open_mode,
CASE
WHEN d.database_role = 'PRIMARY' THEN 'PRIMARY'
WHEN d.database_role = 'PHYSICAL STANDBY' THEN 'PHYSICAL STANDBY'
WHEN d.database_role = 'LOGICAL STANDBY' THEN 'LOGICAL STANDBY'
WHEN d.database_role = 'SNAPSHOT STANDBY' THEN 'SNAPSHOT STANDBY'
ELSE d.database_role
END AS database_role,
CASE
WHEN d.protection_mode = 'MAXIMUM PROTECTION' THEN 'MAX PROTECTION'
WHEN d.protection_mode = 'MAXIMUM AVAILABILITY' THEN 'MAX AVAILABILITY'
WHEN d.protection_mode = 'MAXIMUM PERFORMANCE' THEN 'MAX PERFORMANCE'
ELSE d.protection_mode
END AS protection_mode,
d.protection_level,
d.remote_archive,
d.flashback_on,
d.log_mode,
CASE WHEN dr.status = 'VALID' THEN 'APPLYING' ELSE dr.status END AS apply_status,
dr.process AS apply_process,
dr.error AS last_error
FROM v$database d
LEFT JOIN v$dataguard_status dr ON 1=1
WHERE ROWNUM = 1;
SELECT
T.tablespace_name,
-- Total Size
TO_CHAR(T.total_blocks * B.block_size / 1024 / 1024, '999,999.00') AS "Total Size (MB)",
-- Used Size
TO_CHAR(SUM(S.used_blocks * B.block_size) / 1024 / 1024, '999,999.00') AS "Used Size (MB)",
-- Free Size (Total - Used)
TO_CHAR((T.total_blocks * B.block_size - SUM(S.used_blocks * B.block_size)) / 1024 / 1024, '999,999.00') AS "Free Size (MB)",
-- Used Percentage
TO_CHAR(ROUND(SUM(S.used_blocks) / T.total_blocks * 100, 2), '999.00') AS "Used (%)"
FROM
V$SORT_SEGMENT S,
DBA_TABLESPACES B,
(
SELECT
tablespace_name,
SUM(blocks) AS total_blocks
FROM DBA_TEMP_FILES
GROUP BY tablespace_name
) T
WHERE
S.tablespace_name(+) = T.tablespace_name
AND T.tablespace_name = B.tablespace_name
GROUP BY
T.tablespace_name, T.total_blocks, B.block_size
ORDER BY "Used (%)" DESC;
SELECT
S.sid,
S.serial#,
S.username,
S.osuser,
S.program,
T.blocks * B.block_size / 1024 / 1024 AS "MB Used",
T.tablespace
FROM
V$TEMPSEG_USAGE T,
V$SESSION S,
DBA_TABLESPACES B
WHERE
T.session_addr = S.saddr
AND T.tablespace = B.tablespace_name
ORDER BY
"MB Used" DESC;
SELECT
ARCH.THREAD# AS "Thread",
ARCH.SEQUENCE# AS "Last Received",
APPL.SEQUENCE# AS "Last Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) AS "Seq Diff",
ROUND((SYSDATE - MAX(ARCH.FIRST_TIME)) * 1440, 1) AS "Time Lag (min)",
CASE
WHEN (ARCH.SEQUENCE# - APPL.SEQUENCE#) > 10 THEN ' CRITICAL'
WHEN (ARCH.SEQUENCE# - APPL.SEQUENCE#) > 5 THEN ' HIGH'
ELSE 'OK'
END AS "Status"
FROM
(SELECT THREAD#, SEQUENCE#, FIRST_TIME
FROM V$ARCHIVED_LOG
WHERE (THREAD#, FIRST_TIME) IN (
SELECT THREAD#, MAX(FIRST_TIME)
FROM V$ARCHIVED_LOG
GROUP BY THREAD#
)
) ARCH,
(SELECT THREAD#, SEQUENCE#
FROM V$LOG_HISTORY
WHERE (THREAD#, FIRST_TIME) IN (
SELECT THREAD#, MAX(FIRST_TIME)
FROM V$LOG_HISTORY
GROUP BY THREAD#
)
) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
GROUP BY ARCH.THREAD#, ARCH.SEQUENCE#, APPL.SEQUENCE#, ARCH.FIRST_TIME
ORDER BY 1;
No comments:
Post a Comment