Hi folks, (portugues mais abaixo)After some time I decided to continue posting at this blog even having a channel on youtube. My channel was created to share some stuff like tips, experiences, tutorials about Databases (specially Oracle), Linux and new techologies all in portuguese language.If you are interested in this stuff, please go to https://www.youtube.com/channel/UCDncbo0hpBTEYMjZNZwsfmw and subscribe. Today, I'm glad to share with you a query to get objects dependencies in Oracle Databases. It's very useful when you have to change some object and want to see if some others objects have to be updated too to avoid a broken application. Here I'm using a hierarchical query to get object depedency by level: SELECT LPAD('-',level) || owner || '.' || name ||'('|| type || ')' DEPENDENCYFROM dba_dependencies CONNECT BY PRIOR owner = referenced_owner AND prior name = referenced_name AND prior type = referenced_type START WITH referenced_owner = '&owner'AND referenced_name = '&object'AND owner IS NOT NULL;You have to provide OWNER and OBJECT you are going to change. The depedencies will be tree-like displayed as below:SYS@TEST > / Enter value for owner: HREnter value for object: JOB_HISTORY-HR.ADD_JOB_HISTORY(PROCEDURE) -HR.UPDATE_JOB_HISTORY(TRIGGER)2 rows selected.Output above means UPDATE_JOB_HISTORY trigger depends on ADD_JOB_HISTORY procedure that depends on JOB_HISTORY table (provided object). Let's check:SYS@TEST > select text from dba_source where name='UPDATE_JOB_HISTORY';TRIGGER update_job_history AFTER UPDATE OF job_id, department_id ON employees FOR EACH ROWBEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate, :old.job_id, :old.department_id);END;7 rows selected.SYS@TEST > select text from dba_source where name='ADD_JOB_HISTORY';PROCEDURE add_job_history ( p_emp_id job_history.employee_id%type , p_start_date job_history.start_date%type , p_end_date job_history.end_date%type , p_job_id job_history.job_id%type , p_department_id job_history.department_id%type )ISBEGIN INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);END add_job_history;13 rows selected.I hope this helps you, as DBA or DEVELOPER, to avoid broke things up when changing objects.Til next post.Eduardo Valentim-------------------------------------------------- Portugues --------------------------------------------------Fala pessoal,Depois de algum tempo decidi continuar postando nesse blog mesmo tendo um canal no youtube. Meu canal foi criado para compartilhar algumas coisas como dicas, experiencias, tutoriais sobre bancos de dados (principalmente Oracle), Linux e novas tecnologias tudo em portugues. Se estiver interessado nesse tipo de coisa, por favor acesse https://www.youtube.com/channel/UCDncbo0hpBTEYMjZNZwsfmw e assine o canal. Hoje estou feliz de trazer pra voce uma consulta para obter as dependencias de objetos no banco de dados Oracle. E muito util quando voce tem que alterar algum objeto e quer ver se outros objetos tambem precisam ser atualizados para evitar que a aplicacao "quebre". Aqui estou usando uma consulta hierarquica para obter o nivel de dependencia dos objetos: SELECT LPAD('-',level) || owner || '.' || name ||'('|| type || ')' DEPENDENCYFROM dba_dependencies CONNECT BY PRIOR owner = referenced_owner AND prior name = referenced_name AND prior type = referenced_type START WITH referenced_owner = '&owner'AND referenced_name = '&object'AND owner IS NOT NULL;Voce precisa fornecer o OWNER e o OBJECT que voce modificara. As dependencias aparecerao no estilo arvore conforme abaixo:SYS@TEST > / Enter value for owner: HREnter value for object: JOB_HISTORY-HR.ADD_JOB_HISTORY(PROCEDURE) -HR.UPDATE_JOB_HISTORY(TRIGGER)2 rows selected.A saida acima significa que a trigger UPDATE_JOB_HISTORY depende da procedure ADD_JOB_HISTORY que depende da tabela JOB_HISTORY (objeto informado). Vamos verificar:SYS@TEST > select text from dba_source where name='UPDATE_JOB_HISTORY';TRIGGER update_job_history AFTER UPDATE OF job_id,