Oracle DB 11.2.0.4 中SYS.ALL_CONSTRAINTS的定义 和 SYS.ALL_CONS_COLUMNS的定义
来源:互联网 发布:淘宝上卖什么最赚钱 编辑:程序博客网 时间:2024/06/05 19:44
SYS.ALL_CONSTRAINTS的定义如下:
CREATE OR REPLACE VIEW ALL_CONSTRAINTS(owner, constraint_name, constraint_type, table_name, search_condition, r_owner, r_constraint_name, delete_rule, status, deferrable, deferred, validated, generated, bad, rely, last_change, index_owner, index_name, invalid, view_related)ASselect ou.name, oc.name, decode(c.type#, 1, 'C', 2, 'P', 3, 'U', 4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F', 10, 'F', 11, 'F', 13, 'F', '?'), o.name, c.condition, ru.name, rc.name, decode(c.type#, 4, decode(c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'), NULL), decode(c.type#, 5, 'ENABLED', decode(c.enabled, NULL, 'DISABLED', 'ENABLED')), decode(bitand(c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'), decode(bitand(c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'), decode(bitand(c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'), decode(bitand(c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'), decode(bitand(c.defer,16),16, 'BAD', null), decode(bitand(c.defer,32),32, 'RELY', null), c.mtime, decode(c.type#, 2, ui.name, 3, ui.name, null), decode(c.type#, 2, oi.name, 3, oi.name, null), decode(bitand(c.defer, 256), 256, decode(c.type#, 4, case when (bitand(c.defer, 128) = 128 or o.status in (3, 5) or ro.status in (3, 5)) then 'INVALID' else null end, case when (bitand(c.defer, 128) = 128 or o.status in (3, 5)) then 'INVALID' else null end ), null), decode(bitand(c.defer, 256), 256, 'DEPEND ON VIEW', null)from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru, sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c, sys.obj$ oi, sys.user$ uiwhere oc.owner# = ou.user# and oc.con# = c.con# and c.obj# = o.obj# and c.type# != 8 and (c.type# < 14 or c.type# > 17) /* don't include supplog cons */ and (c.type# != 12) /* don't include log group cons */ and c.rcon# = rc.con#(+) and c.enabled = oi.obj#(+) and oi.owner# = ui.user#(+) and rc.owner# = ru.user#(+) and c.robj# = ro.obj#(+) and (o.owner# = userenv('SCHEMAID') or o.obj# in (select obj# from sys.objauth$ where grantee# in ( select kzsrorol from x$kzsro ) ) or /* user has system privileges */ exists (select null from v$enabledprivs where priv_number in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */) ) )
SYS.ALL_CONS_COLUMNS的定义如下:
CREATE OR REPLACE VIEW ALL_CONS_COLUMNS(owner, constraint_name, table_name, column_name, position)ASselect u.name, c.name, o.name, decode(ac.name, null, col.name, ac.name), cc.pos#from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd, sys."_CURRENT_EDITION_OBJ" o, sys.attrcol$ acwhere c.owner# = u.user# and c.con# = cd.con# and (cd.type# < 14 or cd.type# > 17) /* don't include supplog cons */ and (cd.type# != 12) /* don't include log group cons */ and cd.con# = cc.con# and cc.obj# = col.obj# and cc.intcol# = col.intcol# and cc.obj# = o.obj# and (c.owner# = userenv('SCHEMAID') or cd.obj# in (select obj# from sys.objauth$ where grantee# in ( select kzsrorol from x$kzsro ) ) or /* user has system privileges */ exists (select null from v$enabledprivs where priv_number in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */) ) ) and col.obj# = ac.obj#(+) and col.intcol# = ac.intcol#(+)
0 0
- Oracle DB 11.2.0.4 中SYS.ALL_CONSTRAINTS的定义 和 SYS.ALL_CONS_COLUMNS的定义
- Oracle DB 12.1.0.2 中SYS.ALL_CONSTRAINTS的定义 和 SYS.ALL_CONS_COLUMNS的定义
- Oracle中sys和system的区别
- Oracle中sys和system的区别小结Oracle中sys和system的区别小结
- Oracle数据库中sys和system两个用户的区别
- Oracle中sys和system两个用户的区别
- Oracle数据库中sys和system两个用户的区别
- oracle中sys用户和system用户的区别
- oracle中sys和system的默认密码
- oracle中sys和system用户权限的区别
- oracle中sys和system的区别比较
- oracle中sys用户和system用户的区别
- Oracle数据库中system和sys的区别
- oracle中sys用户和system用户的区别
- Oracle中sys用户和system用户的区别
- oracle中sys用户和system用户的区别
- /sys/devices/ 下的设备name就是bios中定义的HID
- 关于udev,sys,proc,tmpfs一些定义的问题
- Windows Server Web Deployment Tools Example
- Objective-C调用Swift
- 看对象是如何调用类成员变量的
- 类定义的
- Simple Paypal Integration with Salesforce (Step by Step)
- Oracle DB 11.2.0.4 中SYS.ALL_CONSTRAINTS的定义 和 SYS.ALL_CONS_COLUMNS的定义
- [LeetCode] Excel Sheet Column Number
- sqlserver检查死锁的sql
- ArcGIS创建发布地图服务
- Mongodb2.8的版本升级(从2.4升级到2.8)
- 协同过滤之ItemCF(c++实现)
- Swift语言IOS8开发战记1.Button与Alert
- zookeeper原理
- js源码收集