This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
Does anyone actually know all the consequences? Aren't there some really important data among the dependent tables at some level? It is certainly possible - and not that complicated - to do this, but if someone didn't do an analysis of the schema, you might be in for a nasty ride. And if someone did an analysis - how come the table structure isn't part of that analysis?
Make sure there is a working backup before going any further.
A few pointers:
If the tables are all stored in a single schema, you could get the list of foreign keys from the USER_CONSTRAINTS view. Foreign constraints listed in this view contain the name of the parent key in the R_CONSTRAINT_NAME column. You'll then "translate" the constrain name to the table - again using USER_CONSTRAINTS. You might create a view that maps child tables to parent tables directly, and then use hierarchical query to obtain all dependent tables. Then delete them in reverse order. Make sure to use only ENABLED constraints, otherwise you might delete things you don't want to.
If the tables are not in one schema, you'd need an account having access to all the tables and use ALL_CONSTRAINTS instead of USER_CONSTRAINTS.
If there is a lot of data to delete, it can take a really, really long time. Your undo tablespace/rollback segments might even overflow. Assuming you want to erase all data from all affected tables, it might be better to use TRUNCATE. A parent table cannot be truncated while it has enabled foreign keys, so you'd have to disable them all and enable them after the truncate. It's a bit more work programming, but the time savings can be huge. Plus, the allocated space will be freed from the table (just deleting rows leaves unused space allocated in the tables; normally that doesn't matter, but in this case it might be desirable to free the space up).
Some tables might contain foreign keys allowing null values. Should records in these tables that contain null FK values be deleted or left there? If they should be left there, that would complicate the things a bit (or perhaps a lot), and of course, you won't be able to use TRUNCATE.
Joined: Feb 19, 2011
i dont have access to user_constraints and might not get one
In a standard setup, everyone has access to the USER_CONSTRAINTS and ALL_CONSTRAINTS views. You don't have access to DBA_CONSTRAINTS, which is fine - you don't need that.
The right to select from these views is granted to PUBLIC. A DBA would have to revoke that grant to prevent you from seeing the views, which would break many internal tools of Oracle itself, so I'd be really surprised if someone did it somewhere. I might be mistaken, though, so if you really cannot query USER_CONSTRAINTS, your task probably cannot be done. You can try SYS.USER_CONSTRAINTS instead, if the inaccessibility of that view is caused by some synonym.