Very Useful Scripts To Find Object Dependencies

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.

——

/*

By Rodger Lepinsky
Script to find all the parents that the object is dependent on,
what must be created and valid first, before the object can be created and valid.

Only searches one layer of dependency
Successive generations of parents are not displayed.

*/

accept ls_REF_name prompt “Enter an object to find references to: ” ;

Select
TYPE || ‘ ‘ ||
OWNER || ‘.’ || NAME || ‘ references ‘ ||
REFERENCED_TYPE || ‘ ‘ ||
REFERENCED_OWNER || ‘.’ || REFERENCED_NAME
as DEPENDENCIES
From all_dependencies
Where name = UPPER(LTRIM(RTRIM( ‘&ls_REF_name’ )))
AND (REFERENCED_OWNER <> ‘SYS’
AND REFERENCED_OWNER <> ‘SYSTEM’
AND REFERENCED_OWNER <> ‘PUBLIC’
)
AND (OWNER <> ‘SYS’
AND OWNER <> ‘SYSTEM’
AND OWNER <> ‘PUBLIC’
)
order by OWNER, name,
REFERENCED_TYPE ,
REFERENCED_OWNER ,
REFERENCED_name
/

————

@frefs_parents
Enter an object to find references to: fnd_global

DEPENDENCIES
—————————————————————————————————-
PACKAGE BODY APPS.FND_GLOBAL references NON-EXISTENT APPS.DBMS_OUTPUT
PACKAGE BODY APPS.FND_GLOBAL references NON-EXISTENT APPS.DBMS_SESSION
PACKAGE BODY APPS.FND_GLOBAL references NON-EXISTENT APPS.DBMS_SQL
PACKAGE BODY APPS.FND_GLOBAL references NON-EXISTENT APPS.DBMS_UTILITY
PACKAGE BODY APPS.FND_GLOBAL references NON-EXISTENT APPS.PLITBLM
PACKAGE BODY APPS.FND_GLOBAL references NON-EXISTENT APPS.V$NLS_PARAMETERS
PACKAGE BODY APPS.FND_GLOBAL references PACKAGE APPS.APP_EXCEPTION
PACKAGE BODY APPS.FND_GLOBAL references PACKAGE APPS.FND_CLIENT_INFO
…
PACKAGE BODY APPS.FND_GLOBAL references PACKAGE APPS.FND_PROFILE
PACKAGE BODY APPS.FND_GLOBAL references PACKAGE APPS.FND_RELEASE
PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_APPLICATION
PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_LANGUAGES
PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_LOOKUP_TYPES
PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_PROFILE_OPTIONS
PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_PROFILE_OPTION_VALUES
PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_USER
PACKAGE BODY APPS.FND_GLOBAL references VIEW APPS.FND_APPLICATION_VL
PACKAGE BODY APPS.FND_GLOBAL references VIEW APPS.FND_RESPONSIBILITY_VL
PACKAGE BODY APPS.FND_GLOBAL references VIEW APPS.FND_USER_RESP_GROUPS

30 rows selected.

Note: If you see ‘NON-EXISTENT’ in the output, it is usually referring to objects actually owned by SYS, that are accessed via public synonyms.

————————————————————
————————————————————
————————————————————

To Find Just The Children of An Oracle Database Object:

This script is very useful when you want to know the impact of dropping a view.

When an object is dropped, or becomes invalid, all the children of that object become invalid. ie. If a view becomes invalid, all the child procedures, functions, and packages that reference it also become invalid, however many layers deep.

This script only shows the most immediate child level.

———–

/*

By Rodger Lepinsky
Script to find all the child dependencies of an object.
If the object is dropped, or becomes invalid, all the children will become invalid.

Only searches one layer deep.
Successive generations of children are not displayed.

*/

accept ls_REF_name prompt “Enter an object to find references to: ” ;

Select
TYPE || ‘ ‘ ||
OWNER || ‘.’ || NAME || ‘ references ‘ ||
REFERENCED_TYPE || ‘ ‘ ||
REFERENCED_OWNER || ‘.’ || REFERENCED_NAME
as DEPENDENCIES
From all_dependencies
Where referenced_name = UPPER(LTRIM(RTRIM( ‘&ls_REF_name’ )))
AND (REFERENCED_OWNER <> ‘SYS’
AND REFERENCED_OWNER <> ‘SYSTEM’
AND REFERENCED_OWNER <> ‘PUBLIC’
)
AND (OWNER <> ‘SYS’
AND OWNER <> ‘SYSTEM’
AND OWNER <> ‘PUBLIC’
)
order by OWNER, name,
REFERENCED_TYPE ,
REFERENCED_OWNER ,
REFERENCED_name
/

————

APPS_VIS>@frefs_children
Enter an object to find references to: fnd_global

DEPENDENCIES
—————————————————————————————————-
PACKAGE BODY APPS.ADS_OPEN_PERIODS_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.ADS_USHR_JOBREQ_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.ADS_USHR_MEDLEAVE_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.AMS_ACT_MARKET_SEGMENTS_PVT references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.AMS_COMPETITOR_PRODUCTS_B_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.AMS_LISTSOURCETYPE_PVT references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.AMS_LIST_SRC_FIELDS_PKG references PACKAGE APPS.FND_GLOBAL
VIEW APPS.AMW_AUDIT_UNITS_V_ALL references PACKAGE APPS.FND_GLOBAL
…

PACKAGE BODY APPS.ZX_VTX_USER_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.ZX_ZXAPJAJL_XMLP_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.ZX_ZXARRECV_XMLP_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.ZX_ZXJGTAX_XMLP_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.ZX_ZXLOCREP_XMLP_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.ZX_ZXXATB_XMLP_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.ZX_ZXXINUTR_XMLP_PKG references PACKAGE APPS.FND_GLOBAL
VIEW MOBILEADMIN.CPV$CSM_REQ_LINES references PACKAGE APPS.FND_GLOBAL
VIEW MOBILEADMIN.CTM$CSM_REQ_LINES references PACKAGE APPS.FND_GLOBAL

14427 rows selected.

————————————————————
————————————————————
————————————————————

To Find Both Parent And Child Dependencies Of An Object:

This is the script I will usually use first. It can produce a lot of output.

It can also give you some insights. ie. PACKAGE APPS.FND_GLOBAL is required by hundreds of other triggers and views.  Over 14K in total.  Wow!

———–

/*

By Rodger Lepinsky
Script to find all dependencies of an object.
Both parents and children.

Only searches one layer in each direction.
Successive generations of parents or children are not displayed.

*/

accept ls_REF_name prompt “Enter an object to find references to: ” ;

Select
TYPE || ‘ ‘ ||
OWNER || ‘.’ || NAME || ‘ references ‘ ||
REFERENCED_TYPE || ‘ ‘ ||
REFERENCED_OWNER || ‘.’ || REFERENCED_NAME
as DEPENDENCIES
From all_dependencies
Where referenced_name = UPPER(LTRIM(RTRIM( ‘&ls_REF_name’ )))
or name = UPPER(LTRIM(RTRIM( ‘&ls_REF_name’ )))
AND (REFERENCED_OWNER <> ‘SYS’
AND REFERENCED_OWNER <> ‘SYSTEM’
AND REFERENCED_OWNER <> ‘PUBLIC’
)
AND (OWNER <> ‘SYS’
AND OWNER <> ‘SYSTEM’
AND OWNER <> ‘PUBLIC’
)
order by 1
/

————

@frefs_all
Enter an object to find references to: fnd_global

DEPENDENCIES
—————————————————————————————————-

…

PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_LANGUAGES
PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_LOOKUP_TYPES
PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_PROFILE_OPTIONS
PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_PROFILE_OPTION_VALUES
PACKAGE BODY APPS.FND_GLOBAL references SYNONYM APPS.FND_USER
PACKAGE BODY APPS.FND_GLOBAL references VIEW APPS.FND_APPLICATION_VL
PACKAGE BODY APPS.FND_GLOBAL references VIEW APPS.FND_RESPONSIBILITY_VL
PACKAGE BODY APPS.FND_GLOBAL references VIEW APPS.FND_USER_RESP_GROUPS

PACKAGE BODY APPS.FND_GRANTS_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.FND_GSM_UTIL references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.FND_HELP references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.FND_IMP_DEPENDENCY_PKG references PACKAGE APPS.FND_GLOBAL
PACKAGE BODY APPS.FND_IMP_PKG references PACKAGE APPS.FND_GLOBAL

…

VIEW APPS.ZX_PRODUCT_TYPES_V references PACKAGE APPS.FND_GLOBAL
VIEW APPS.ZX_REGIMES_V references PACKAGE APPS.FND_GLOBAL
VIEW APPS.ZX_SERVICE_OWNER_V references PACKAGE APPS.FND_GLOBAL
VIEW APPS.ZX_SIM_RULE_RESULTS_V references PACKAGE APPS.FND_GLOBAL
VIEW APPS.ZX_TRANSACTION_LINES_V references PACKAGE APPS.FND_GLOBAL
VIEW MOBILEADMIN.CPV$CSM_REQ_LINES references PACKAGE APPS.FND_GLOBAL
VIEW MOBILEADMIN.CTM$CSM_REQ_LINES references PACKAGE APPS.FND_GLOBAL

14456 rows selected.

————–

Also note that if an object is referencing a synonym, the underlying object type (ie table, view, package) is not displayed.

Hopefully you have a very meaningful naming convention so that the object type will instantly obvious. Otherwise, you will need to do more very inefficient nested loops of investigation.

Hope it’s useful.