Mostrando Recursivamente as dependencias ou o reverso: quais objetos são dependes de...

Em alguns casos, você quer saber quais são as dependências de um objeto, por exemplo:
- O que acontece se recompilar o objeto em questão
- Objetos que evitam a compilação do objeto em questão (exemplo: data-dictionary e objeto no esquema errado)

Uma outra questão pode ser que os objetos são dependentes de um objeto específico (dependências reversa), por exemplo:
- Objetos que serão invalidados caso eu dropar objeto em questão.
- Qual é a dependência em cascata para este objeto específico.

Como a view dba_dependencies não tem as informações necessárias podemos utilizar as procedures abaixo (Lembre que para compilar, é necessário grant nas views dba_dependencies e dba_objects)


-- Mostrar dependencias de um objeto:

create or replace procedure denilson.recur_object(v_owner in varchar2,
v_object_name varchar2,
v_object_type varchar2,
level number default 0) is
cursor c_depen is
select referenced_owner, referenced_name, referenced_type
from sys.dba_dependencies
where dba_dependencies.owner = v_owner
and dba_dependencies.name = v_object_name
and dba_dependencies.type = v_object_type;
i integer;
object_id dba_objects.object_id%type;
datum varchar2(25);
time_stamp dba_objects.timestamp%type;
v_status dba_objects.status%type;
begin
select object_id,
to_char(last_ddl_time, 'dd-mon-yyyy hh24:mi:ss'),
timestamp,
status
into object_id, datum, time_stamp, v_status
from sys.dba_objects
where dba_objects.owner = v_owner
and dba_objects.object_name = v_object_name
and dba_objects.object_type = v_object_type;
dbms_output.put_line('Level: ' || ' ' || level || ' ' || object_id || ' ' ||
v_owner || ' ' || v_object_name || ' ' ||
v_object_type || ' ' || datum || ' ' || time_stamp || ' ' ||
v_status);
for c_rec in c_depen loop
recur_object(c_rec.referenced_owner,
c_rec.referenced_name,
c_rec.referenced_type,
level + 1);
end loop;
exception
when no_data_found then
dbms_output.put_line('No record found for: ' || ' ' || v_owner || ' ' ||
v_object_name || ' ' || v_object_type);
end;
/

-- Testando:
sql> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';sql> set linesize 256sql> set trimspool onsql> set serveroutput onsql> exec dbms_output.enable(1000000);

exec recur_object(upper('&owner'),upper('&object_name'),upper('&object_type'));


-- -- Mostrar dependencias para um objeto:

create or replace procedure denilson.recur_object_reverso(v_owner in varchar2,
v_object_name varchar2,
v_object_type varchar2,
level number default 0) is
cursor c_depen_reverse is
select owner, name, type
from dba_dependencies
where dba_dependencies.referenced_owner = v_owner
and dba_dependencies.referenced_name = v_object_name
and dba_dependencies.referenced_type = v_object_type;

i integer;
object_id dba_objects.object_id%type;
datum varchar2(25);
time_stamp dba_objects.timestamp%type;
v_status dba_objects.status%type;
begin
select object_id,
to_char(last_ddl_time, 'dd-mon-yyyy hh24:mi:ss'),
timestamp,
status
into object_id, datum, time_stamp, v_status
from dba_objects
where dba_objects.owner = v_owner
and dba_objects.object_name = v_object_name
and dba_objects.object_type = v_object_type;

dbms_output.put_line('Level: ' || ' ' || level || ' ' || object_id || ' ' ||
v_owner || ' ' || v_object_name || ' ' ||
v_object_type || ' ' || datum || ' ' || time_stamp || ' ' ||
v_status);

for c_rec in c_depen_reverse loop
recur_object_reverse(c_rec.owner, c_rec.name, c_rec.type, level + 1);

end loop;
exception
when no_data_found then
dbms_output.put_line('No record found for: ' || ' ' || v_owner || ' ' ||
v_object_name || ' ' || v_object_type);
end;
/

-- Testando:sql> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';sql> set linesize 256sql> set trimspool onsql> set serveroutput onsql> exec dbms_output.enable(1000000);sql> exec recur_object_reverse(upper('&owner'),upper('&object_name'),upper('&object_type'));

-- Mostra todas as dependencias recursivamente no schema:

create or replace procedure recur_owner(v_owner in varchar2) is
cursor c_obj is
select object_name, object_type
from dba_objects
where dba_objects.owner = v_owner
order by object_name, object_type;
begin
for c_rec in c_obj loop
dbms_output.put_line('.');
recur_object(v_owner, c_rec.object_name, c_rec.object_type);
end loop;
end;
/

-- Testando:
sql> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';sql> set linesize 256sql> set trimspool onsql> set serveroutput onsql> exec dbms_output.enable(1000000);sql> exec recur_owner(upper('&owner'));