Compilation Impact: Object Dependencies

Hi all!
It’s not necessarily the DBA function, but how often someone of business came and ask you wich is the impact on recompiling one or other procedure?
It probably happen because the DBA usually make some magic and have a better understanding about objects relationship. It happens specially in cases there is no code governance…

So, you don’t have to handle all responsability and can switch some of that with developer, through DBA_DEPENDENCIES view.

The undertstanding is easy: The depended objects and the refered objects. If ou change the refered, all depended will be impacted by.

GREPORADB> @dependencies
Enter value for owner: GREPORA
Enter value for obj_name: TABLE_EXAMPLE
OWNER              Name                                TYPE       DEPE REFERENCED REFERENCED_OWNER   REFERENCED_NAME                         
------------------ ----------------------------------- ---------- ---- ---------- ------------------ -----------------------------------     
GREPORA            TOTALANSWEREDQUESTIONS              FUNCTION   HARD TABLE      GREPORA            TABLE_EXAMPLE                        
GREPORA            USERRESPONSESTATUS                  FUNCTION   HARD TABLE      GREPORA            TABLE_EXAMPLE                        
GREPORA            VW_INPROGRESSFEEDBACKOPTS           VIEW       HARD TABLE      GREPORA            TABLE_EXAMPLE                        
GREPORA            EVENTSTARTDT                        FUNCTION   HARD TABLE      GREPORA            TABLE_EXAMPLE                        

Nice, hãn?

## @dependencies
col owner for a18
col name for a35
col type for a10
col referenced_owner for a18
col referenced_name for a35
col referenced_type for a10
select owner,name,type,dependency_type,referenced_type,referenced_owner,referenced_name from dba_dependencies
where referenced_owner like upper('%&owner%') and referenced_name like upper('%&OBJ_NAME%');

See ya!

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.