Using oracle hierarchical query to print the dependency map of tables in oracle based on dba_constraints view

Dear Readers,

I was assigned a project, where the data migration involved detecting the dependency among the tables in one schema (the dependency come from other schemas which granted direct references privilege as well). There are plenty of queries in oracle community and other blogs to plot it. But I wanted to write one for myself with complete understanding on what I am approaching and a way compare the outcome. So the query is robust and result is reliable.

Query to plot the relation:

with c as

(

select *

from dba_constraints

where constraint_type in ('P','R')

)

,rel as

(

select c.owner parown

,c.table_name partab

,cd.owner depown

,cd.table_name deptab

from c,

c cd

where c.CONSTRAINT_NAME(+)=cd.R_CONSTRAINT_NAME

and (cd.owner=&&own

or c.owner=&&own)

)

select depown

,deptab

,parown int_parentown

,partab int_parenttab

,RPAD('.', (level-1)*2, '.') ||depown||'~'||deptab AS tree

,level

,CONNECT_BY_ROOT (depown||'~'||deptab) AS root_id

,LTRIM(SYS_CONNECT_BY_PATH((depown||'~'||deptab), '->'),'->') AS path

,CONNECT_BY_ISLEAF AS leaf

,CONNECT_BY_ISCYCLE AS cycle

from rel

START WITH (parown is null

)

CONNECT BY NOCYCLE (parown||'~'||partab) = prior(depown||'~'||deptab)

ORDER SIBLINGS BY (parown||'~'||partab)

;

Now the test bed setup:

create table otnum

as

select rownum rn

,object_type

from (

select object_type

from dba_objects

group by object_type

order by 1);

alter table otnum add constraint otnum_pk primary key (rn);

create table objnum

as

select rownum rn

,a.owner

,a.object_name

,b.rn objtid

from dba_objects a

,otnum b

where b.object_type=a.object_type

order by 2;

alter table objnum add constraint otnum_rk foreign key (objtid) references otnum(rn);

alter table objnum add constraint objnum_pk primary key (rn);

create table tabnum

as

select rownum rn

,a.owner

,a.table_name

,b.rn objid

,a.num_rows

,a.last_analyzed

from dba_tables a

,objnum b

,otnum c

where a.owner=b.owner

and a.table_name=b.object_name

and c.object_type='TABLE'

and c.rn=b.objtid;

alter table tabnum add constraint tabnum_rk foreign key (objid) references objnum(rn);

alter table tabnum add constraint tabnum_pk primary key (rn);

create table system.idxnum

as

select rownum rn

,a.rn tabid

,a.owner table_owner

,a.table_name

,b.owner

,b.index_name

,c.rn objid

,b.last_analyzed

from tabnum a

,dba_indexes b

,objnum c

,otnum d

where b.table_owner=a.owner

and b.table_name=a.table_name

and b.owner=c.owner

and b.index_name=c.object_name

and d.object_type='INDEX'

and d.rn=c.objtid;

grant references on opdb1_public_user.tabnum to system;

grant references on opdb1_public_user.objnum to system;

alter table system.idxnum add constraint idxnum_rk foreign key (tabid) references opdb1_public_user.tabnum(rn);

alter table system.idxnum add constraint idxnum_rk2 foreign key (objid) references opdb1_public_user.objnum(rn);

alter table system.idxnum add constraint idxnum_pk primary key (rn);

The relationship map as seen from Oracle Data modeller tool:

Now our query result:

Query explanation:

Step 1) Dump all the constraints of type primary key and referential keys. Since our search is specific to one schema and map all its dependency

with c as

(

select *

from dba_constraints

where constraint_type in ('P','R')

)

Step 2) Use the result from the step (1) and arrive at the parent-child relationship among the tables from our candidate schema. Note here is where we are using our schema name. You also may notice we are using an outer join here to print all the P & R constraints from our candidate schema.

,rel as

(

select c.owner parown

,c.table_name partab

,cd.owner depown

,cd.table_name deptab

from c,

c cd

where c.CONSTRAINT_NAME(+)=cd.R_CONSTRAINT_NAME

and (cd.owner=&&own

or c.owner=&&own)

)

Step 3) Use the hierarchical query to print the parent child mapping.

This is where the trick is, we start with a table which doesnt have any parents (like employees with no manager, ideally they are the boss then :) and then we go down further right, so same principle).

We start with "depown,deptab" having no "parown,partab" (which is parent owner and parent table) and then connect with them using prior cycle.

select depown

,deptab

,parown int_parentown

,partab int_parenttab

,RPAD('.', (level-1)*2, '.') ||depown||'~'||deptab AS tree

,level

,CONNECT_BY_ROOT (depown||'~'||deptab) AS root_id

,LTRIM(SYS_CONNECT_BY_PATH((depown||'~'||deptab), '->'),'->') AS path

,CONNECT_BY_ISLEAF AS leaf

,CONNECT_BY_ISCYCLE AS cycle

from rel

START WITH (parown is null

)

CONNECT BY NOCYCLE (parown||'~'||partab) = prior(depown||'~'||deptab)

ORDER SIBLINGS BY (parown||'~'||partab)

; 

Hope this helps someone!

Thank you!