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

在平時的工作環境中，總會有一些表會存在依賴關系，比如我們有三張表customer,使用者表subscriber,賬戶表account

其中客戶可以有多個使用者或者賬戶，subscriber表和account表中就存在外鍵customer_id指向了customer表。

這種情況下表的依賴關系就如下所示：

customer

            subscriber

            account

如果表中的層級關系更為複雜，如果能夠得到一個很清晰的依賴關系表。在做一些重要的操作時就能運籌帷幄。避免很多不必要的麻煩。

今天開發的人員私信給我，想讓我幫忙在測試環境運作一些腳本，本來這種工作都是需要按照流程的，我們聊了下，他說現在手頭有10個左右的腳本，都是些dml相關的操作，每個腳本會對應修改一個表的資料，但是每次執行的時候都會報外鍵不存在的錯誤，他想讓我來幫忙看看，是哪些表存在依賴關系，這個問題換我來，我也得知道腳本的依賴關系，即涉及到的表的依賴關系，我說我來幫你分析這個關系，還是按照流程來運作你的腳本吧，他半信半疑，我就運作了下面的腳本，得到了一個依賴關系清單。

使用shell腳本分析表依賴的層級關系腳本如下：

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,'

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;

drop table table_depency_rel;

EOF

exit

自己在反複模拟一些場景之後總結了如上的腳本。

我們來通過如下的方式運作腳本，檢視system下的表依賴關系。

ksh showdepency.sh

----- -------------------------------------------------- ......

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級了，這些如果人工來一個一個稽核，确實是很頭疼的工作。