OracleObjects & schema

Objects changed in the last 24 hours

Application objects with DDL in the last day. Grants also update LAST_DDL_TIME, so not every row is a code change.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN owner       FORMAT A204COLUMN object_type HEADING "TYPE"     FORMAT A205COLUMN object_name HEADING "NAME"     FORMAT A356COLUMN last_ddl    HEADING "LAST DDL" FORMAT A167COLUMN status      FORMAT A78 9SELECT owner, object_type, object_name,10       TO_CHAR(last_ddl_time, 'YYYY-MM-DD HH24:MI') AS last_ddl,11       status12FROM dba_objects13WHERE last_ddl_time > SYSDATE - 114  AND owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')15ORDER BY last_ddl_time DESC;

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

Open in denrepo

Helps with

• ORA-04068: existing state of packages%s%s%s has been discarded

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…
• Disabled constraints and triggersConstraints and triggers someone switched off, often for a data load, and never switched back on.