Hi,
I am trying to find the list of dependents for all objects in a schema. I know how to find it for a single objects but I am messing something up when I join it with user_objects table.
for example,
--table with 2 dependent views on it.
create table t_1 as select user as name from dual;
create view v_1 as select * from t_1;
create view v_11 as select * from v_1;
--table with no dependents
create table t_2 as select user as name from dual;
Now, for a single object, I can list all the dependents on the object.
for example,
SELECT
level,
referenced_owner,
referenced_name,
referenced_type
FROM
dba_dependencies
START WITH owner = user
AND name = 'V_11'
AND type = 'VIEW' CONNECT BY owner = PRIOR referenced_owner
AND name = PRIOR referenced_name
AND type = PRIOR referenced_type;
So far so good.
I am now trying to list all objects under this user and do a left join and show any dependent objects they might have. That isn't working.
What I have tried is
SELECT
u.object_name,
u.object_type,
level,
referenced_owner,
referenced_name,
referenced_type
FROM
user_objects u
LEFT JOIN dba_dependencies d ON ( d.owner = user
AND u.object_name = d.name
AND u.object_type = d.type )
START WITH owner = user
AND name = u.object_name
AND type = u.object_type CONNECT BY owner = PRIOR referenced_owner
AND name = PRIOR referenced_name
AND type = PRIOR referenced_type;
OBJECT_NAM OBJECT_TYP LEVEL REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE
---------- ---------- ------ -------------------- -------------------- --------------------
V_1 VIEW 1 TEST_USER T_1 TABLE
V_11 VIEW 1 TEST_USER V_1 VIEW
V_1 VIEW 2 TEST_USER T_1 TABLE
The tables weren't listed even though I am doing a left join. What am I doing wrong?
Edit: Fixed the code format.