查看外键父子关系

select uc.constraint_name, uc.owner child_owner, uc.table_name child_table, ucc.column_name child_column
,ucp.owner parent_owner, ucp.table_name parent_table, ucp.column_name parent_column
from dba_constraints uc, dba_cons_columns ucc, dba_cons_columns ucp
where uc.owner = 'TRSDATA'
and uc.table_name = 'TABLEB'
and ucc.constraint_name = uc.constraint_name
and ucp.constraint_name = uc.r_constraint_name

 

注意，以上脚本运行很慢，可能需要拆散重写。

 

 

select 'alter table '||uc.owner||'.'||uc.table_name||' disable constraint '|| uc.constraint_name||';'
from dba_cons_columns ucp,  dba_constraints uc,dba_cons_columns ucc
where ucp.constraint_name = uc.r_constraint_name
and ucc.constraint_name = uc.constraint_name
and UCP.TABLE_NAME in ('CSPAFUNS',
'FBPACRPM',
'FBPACUSM',
'FBPADTCM',
'FBPAIMPM')

 

缩小范围可以快一点，可以拼disable constraint语句。

 

 

 

create or replace view user_cons_detail

(constraint_name, child_table, child_column, parent_table, parent_column)

as

 

select oc.NAME, o.NAME , cl.NAME ,oo.NAME, cl2.NAME

-- oc.CON#,oc.NAME ,o.OBJ# ,o.NAME ,'@@' sss, cc.COL# , cl.NAME, '@@' ss, oo.OBJ#,oo.NAME, cl2.NAME

from sys.con$ oc ,sys.cdef$ c, sys.obj$ o, sys.user$ u, sys.con$ rc , sys.obj$ oo, sys.ccol$ cc , sys.col$ cl, sys.ccol$ cc2 , sys.col$ cl2

where oc.CON# = c.CON#

and o.OBJ# = c.OBJ#

and u.USER# = o.OWNER#

and u.NAME = 'CMBMODEL'

and oo.OBJ# = c.ROBJ#

and rc.CON# = c.RCON#

and cc.CON# = c.CON#

and cl.COL# = cc.COL#

and cl.OBJ# = c.OBJ#

--

and cc2.CON# = rc.CON#

and cl2.COL# = cc2.COL#

and cl2.OBJ# = oo.OBJ#

 

 

 

 

 

create or replace view user_cons_detail

(constraint_name, child_table, child_column, parent_table, parent_column)

as

 

select oc.NAME constraint_name, o.NAME child_table , cl.NAME child_column,oo.NAME parent_table, cl2.NAME parent_column

-- oc.CON#,oc.NAME ,o.OBJ# ,o.NAME ,'@@' sss, cc.COL# , cl.NAME, '@@' ss, oo.OBJ#,oo.NAME, cl2.NAME

from sys.con$ oc ,sys.cdef$ c, sys.obj$ o, sys.user$ u, sys.con$ rc , sys.obj$ oo, sys.ccol$ cc , sys.col$ cl, sys.ccol$ cc2 , sys.col$ cl2

where oc.CON# = c.CON#

and o.OBJ# = c.OBJ#

and u.USER# = o.OWNER#

--and u.NAME = 'CMBMODEL'

and oo.NAME = 'PM_CMP_MODEL'

and cl2.NAME = 'DB_TABLE'

and oo.OBJ# = c.ROBJ#

and rc.CON# = c.RCON#

and cc.CON# = c.CON#

and cl.COL# = cc.COL#

and cl.OBJ# = c.OBJ#

--

and cc2.CON# = rc.CON#

and cl2.COL# = cc2.COL#

and cl2.OBJ# = oo.OBJ#