1

I have a aged and redundant schema that I want to drop. Running DROP USER old_schema CASCADE runs succesfully and it's dropped.

How can I find out whether dropping this schema results in other components in the database (under different schemas) possible breaking?

I have written tests to make sure all the components that interfaced with this schema's procedures and types (which were the only objects in this schema) are functional (and have been superseded and phased out in the release), but I was wondering if there was a way (native or otherwise) to know if there are any object that still link to this schema.

Ideally, I was hoping for some plugin / utility / SQL-snippet that will check all the links to/from your schema and list the dependencies/dependents to:

  • confirm the information I believe to be correct
  • manually action, any that I may have missed

EDIT 01:

A fleeting comment (was deleted promptly) mentioned something about INVALID OBJECTS - can anyone extrapolate on this?

lohithbb
  • 128
  • 1
  • 11
  • 1
    `AUDIT` commands come to mind. But if you don't know what you're doing with `AUDIT`, its far more than I could possibly explain in this scenario. *May require research* – ccarpenter32 Jun 13 '18 at 15:43

1 Answers1

2

There is no single utility that will do this for you, because there are so many ways that systems can be dependent on your database schema. For example, a script might login every 12 months to perform some task, some dynamic SQL might be run from another schema, or another database might run queries via a database link.

You might be able to narrow down the possibilities by checking if the schema allows direct connections, and then look at any grants on its objects.

Ultimately you will need to do some research and testing to make an assessment of the relative risk of dropping the schema. If your environment is relatively well controlled and documented you should be able to determine with a good deal of confidence what code or systems rely on the schema you wish to drop.

If it's important to drop the schema, and you wish to minimise the risk of something going wrong when you drop it, you might consider a variation on the following set of steps:

  1. Create a non-production copy of the database.

  2. Query DBA_OBJECTS for any objects where status='INVALID'. There should be none.

  3. Drop the schema (note: this is your non-prod database!).

  4. Query DBA_OBJECTS for any objects where status='INVALID'. Build scripts to fix these accordingly as appropriate.

  5. Test all applications and interfaces to the best of your (and your company's) ability. Build scripts to implement any additional fixes required for issues discovered.

  6. Create another non-production copy of the database. Drop the schema. Test your fix scripts. Perhaps get another team to do some additional testing in case something was missed.

  7. Take a backup, then drop the schema in production. Run your fix scripts.

  8. Wait for the screams when some critical bit that wasn't tested starts failing. Consider restoring the schema from backup if this happens immediately. Note: it may take 24 hours, or 12 months, before some things start failing (e.g. daily or annual scheduled jobs).

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • Steps 2, 3, 4 seem to address the crux of the matter - thank you. I've tested it and there are no INVALID DBA_OBJECTS after so that's good. Any ideas on how I can trace database links to/from the schema? The only success I've had is doing a text search (using Agent Ransack) through version control. – lohithbb Jun 14 '18 at 10:40
  • 1
    I would look into whether you could run a query on each and every database at your location to take an inventory of all the database links. – Jeffrey Kemp Jun 14 '18 at 14:27