see what drop table cascade constraints deleted from database

Question

Is there any way (a command that I can add in the top of the script file) to know what exactly has been dropped from database when executing :

DROP TABLE MyTable CASCADE CONSTRAINTS

The way I'm doing it now is to select all referential integrity constraints before dropping myTable:

SELECT constraint_name 
FROM user_constraints 
WHERE TABLE_NAME = 'mytable' 
AND constraint_type in ('R')

And:

SELECT constraint_name 
FROM user_constraints 
WHERE constraint_type in ('R') 
and r_constraint_name in (select constraint_name 
                           from user_constraints
                           where constraint_type in ('P','U') 
                           and table_name='mytable')

So i think there is no way to know the behind scenes of oracle hmmm — maryam, May 13 '16 at 15:48

score 2 · Answer 1 · answered Dec 19 '18 at 13:52

I agree this seems like an omission, but thinking it through it's hard to see what benefits it might provide.

The main use case for CASCADE CONSTRAINTS option is when re-building a schema - usually in development - when the overhead of putting the DROP TABLE statements in the right order is too much effort. Because we are re-building the schema all the foreign key constraints should be restored, so we don't really need to know what they are.

This does assume that we have build scripts properly maintained and kept under source control. If we are not in that happy situation, dropping a table and cascading the foreign key constraints is reckless.

Similarly, if the intention is to drop the table and keep it dropped, then we should have undertaken an impact analysis which would have identified the foreign keys before dropping the table. Probably by running the sort of query you have in your question :) But your reference to a script suggests this is not the scenario you have in mind.

We can image Oracle implementing this feature with something like the RETURNING ... INTO syntax supported by DML statements. However there is problem, which is highlighted by a gap in your query. Other schemas can build foreign keys referencing our table (if we have granted the REFERENCE privilege) so the query should be over ALL_CONSTRAINTS and return OWNER as well as CONSTRAINT_NAME. This means the posited RETURNING ... INTO feature would need to populate two nested tables - or one table of a complex type - which probably requires lots of low-level jiggery-pokery (to use the technical term) without delivering much benefit, because the use case is so narrow.

score 1 · Answer 2 · answered Dec 19 '18 at 13:31

I can think of two possibilities:

If you have the recycle bin enabled, then I assume all the dropped objects will be moved there. You could query the recyclebin view to see what has been recently dropped. (This could potentially include unrelated objects that happened to be dropped around the same time.)
Activate SQL Tracing: ALTER SESSION SET SQL_TRACE = TRUE. This will cause a trace file to be generated containing all SQL statements executed by your session, including recursive SQL. You will need access to the database server to view the trace file.

see what drop table cascade constraints deleted from database

2 Answers2