通過shell分析表依賴的層級關係

通過shell分析表依賴的層級關係

在平時的工作環境中，總會有一些表會存在依賴關係，比如我們有三張表customer,使用者表subscriber,賬戶表account
其中客戶可以有多個使用者或者賬戶，subscriber表和account表中就存在外鍵customer_id指向了customer表。
 這種情況下表的依賴關係就如下所示：
customer
            subscriber
            account
如果表中的層級關係更為複雜，如果能夠得到一個很清晰的依賴關係表。在做一些重要的操作時就能運籌帷幄。避免很多不必要的麻煩。
 今天開發的人員私信給我，想讓我幫忙在測試環境運行一些指令碼，本來這種工作都是需要按照流程的，我們聊了下，他說現在手頭有10個左右的指令碼，都是些dml相關的操作，每個指令碼會對應修改一個表的資料，但是每次執行的時候都會報外鍵不存在的錯誤，他想讓我來幫忙看看，是哪些表存在依賴關係，這個問題換我來，我也得知道指令碼的依賴關係，即涉及到的表的依賴關係，我說我來幫你分析這個關係，還是按照流程來運行你的指令碼吧，他半信半疑，我就運行了下面的指令碼，得到了一個依賴關係列表。

 使用shell指令碼分析表依賴的層級關係指令碼如下：

sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOF
 set pages 1000
 set echo off
 set feedback off
 create table table_depency_rel as
 (
 select 
          p.table_name parent_table_name , 
          p.owner p_owner, 
          c.table_name child_table_name , 
          c.owner c_owner 
      from user_constraints p, user_constraints c 
    where p.constraint_type IN ('P','U') AND 
          c.constraint_type = 'R' AND 
          p.constraint_name = c.r_constraint_name
          group by p.table_name,p.owner, c.table_name, c.owner
 )
 ;
 --alter table table_depency_rel modify(p_owner null);
 alter table table_depency_rel modify(parent_table_name null);
 insert into table_depency_rel
 (
 select null,null,parent_table_name,p_owner from table_depency_rel
 where parent_table_name not in (select child_table_name from table_depency_rel group by child_table_name)group by parent_table_name,p_owner
 );
 set echo on
 set feedback on
 col table_node format a50
 col level_code format a5
 select decode(level,1,'<--'||level,level)||'-' level_code , lpad('-',level*3-1,'--')||'||'||t.child_table_name||'('||level||')' table_node from table_depency_rel t
 connect by prior t.child_table_name  =t.parent_table_name
 start with t.child_table_name in(select child_table_name from table_depency_rel where parent_table_name is null group by child_table_name)
 --order by parent_table_name desc;

set feedback off
 set echo off
 drop table table_depency_rel;

EOF
 exit

自己在反覆類比一些情境之後總結了如上的指令碼。
 我們來通過如下的方式運行指令碼，查看system下的表依賴關係。
ksh showdepency.sh
 ----- -------------------------------------------------- ......

<--1- --||DE_CRI_PARAMS(1)

2-    -----||DE_CRI_ALLOWED_OPS(2)

2-    -----||DE_CRI_ITEMS(2)

3-    --------||DE_CRI_CONNECTIONS(3)

3-    --------||DE_CRI_POOL(3)

4-    -----------||DE_CONDITIONS(4)

5-    --------------||DE_DP_COMPONENTS(5)

6-    -----------------||DE_CONTRIBUTE_LIST(6)

6-    -----------------||DE_EXTRA_DPC_FIELDS(6)

6-    -----------------||DE_TIERS(6)

7-    --------------------||DE_STEPS(7)

可以很清晰的看到顯示的層級關係，有1，2，3，4  其中1是根節點，2，3，4是依賴表，4依賴3，3依賴2，依此類推。運行指令碼的時候是從第1級開始，逐次類推。

可以看到有些表的依賴都到第7級了，這些如果人工來一個一個審核，確實是很頭疼的工作。

本文永久更新連結地址：