For DBAs and Developers.
When you are developing schemas, a lot of objects get created. It can be really confusing to keep track of them all. Especially when you now need to create a few dozen in a new environment.
What is even worse, is when you start to maintain databases and schemas that you didn’t build yourself. If you drop a table or unsuccesfully modify a view, all their children will now become invalid. Suddenly child procedures and packages that reference them will not work.
Here you can find some sqlplus scripts that I have used for years to see the dependencies in schema objects. Run the script, and enter in the object you have questions on. Very fast and effective.
They have been extremely useful to determine things like:
- The order of operations to create objects. Parents first. Then children.
- The impact of dropping a view, package, procedure.
- Causes of object invalidation such as another invalid object.
- All the tables that a package is accessing directly. For those multiple 18 table joins.
- Whether a package, function or procedure calls other prodecures.
- Obscure issues, such as references to variables in packages.
To Find Just The Parents of an Oracle Database Object:
Parent objects must be created first, before creating the child object. If the parent object is dropped, all subsequent child objects will become invalid.
This info is very important in new environments, such as moving from Dev to Test. Or Test to Prod.