In Oracle 19c database, when we drop a table what happens to the procedures, triggers, index that uses this table?

Question

In Oracle 19c database, when we drop a table what happens to the procedures, triggers, index that uses this table?

• Will the triggers, procedures, index gets dropped automatically or will it become INVALID status?

• I want to know what is the correct process that needs to be followed while dropping the table when you know you already have the triggers, procedures, index associated with that particular table?

Please help me out.

Answer 1

Indexes and triggers on the table will be dropped (as will grants)

Synonyms and views will become invalid

Hard-coded references to the table in procedures, packages, functions and triggers will make them invalid. References via dynamic SQL won't result in invalidation, but would fail when executed.

Query the DBA_DEPENDENCIES view to see which objects have dependencies and will get invalidated. There can be knock on impacts (dropping a table invalidates a procedure and a package that calls that procedure will be invalidated even if it doesn't reference the table directly).

If all usages are within the same user/schema, you can query USER_DEPENDENCIES instead. Don't bother with ALL_DEPENDENCIES view as, if another user has created objects referencing the victim table, you might not have privileges to see that object anyway.