OracleObjects & schema

Disabled constraints and triggers

Constraints and triggers someone switched off, often for a data load, and never switched back on.

12c+

Not yet verified. How scripts are tested

ora-disabled.sql

1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN owner           FORMAT A204COLUMN table_name      HEADING "TABLE"      FORMAT A305COLUMN constraint_name HEADING "CONSTRAINT" FORMAT A306COLUMN constraint_type HEADING "TYPE"       FORMAT A47COLUMN trigger_name    HEADING "TRIGGER"    FORMAT A308COLUMN status          FORMAT A89 10SELECT owner, table_name, constraint_name, constraint_type, status11FROM dba_constraints12WHERE status = 'DISABLED'13  AND owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')14ORDER BY owner, table_name, constraint_name;15 16SELECT owner, table_name, trigger_name, status17FROM dba_triggers18WHERE status = 'DISABLED'19  AND owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')20ORDER BY owner, table_name, trigger_name;

Save it as ora-disabled.sql and run it with SQL> @ora-disabled.

Open in denrepo

Helps with

More Oracle scripts: Objects & schema

Generate DDL for a table and an indexUses DBMS_METADATA to write the CREATE statements for a table and one of its indexes into ddl_list.sql in your current directory. Prompts for the…
Invalid objectsEvery invalid object by owner and type, typically left behind by a deployment or patch. The recompile script fixes most of them.
Recompile invalid objectsRecompile one schema, or everything in the database with Oracle's utlrp script. Run the invalid objects check again afterwards.
Unusable indexes and index partitionsIndexes marked UNUSABLE, typically after a partition operation or a direct-path load. Queries that need them will either fail or fall back to full…
Foreign keys that are probably not indexedForeign keys whose columns aren't the leading columns of an index. Unindexed foreign keys cause table-level locks when the parent row is updated or…
Objects changed in the last 24 hoursApplication objects with DDL in the last day. Grants also update LAST_DDL_TIME, so not every row is a code change.