Posts
DBA_TRIGGERS的字段的结尾有空格
参考:Damnit Oracle!
查看系统视图定义可知, TRIGGERING_EVENT 字段因为是拼出来的, 所以后面可能会有空格,
而 BASE_OBJECT_TYPE, ACTION_TYPE 字段的值为了(为什么?)等于该字段数据类型的定义长度, 有时后面也加上了空格
确实很奇怪
SQL> desc DBA_TRIGGERS
Name Null? Type
----------------------------------------------------------------------------- -------- ----------------------------------------------------
OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
SQL> select text from dba_views where view_name='DBA_TRIGGERS';
TEXT
--------------------------------------------------------------------------------
select trigusr.name, trigobj.name,
decode(t.type#, 0, 'BEFORE STATEMENT',
1, 'BEFORE EACH ROW',
2, 'AFTER STATEMENT',
3, 'AFTER EACH ROW',
4, 'INSTEAD OF',
'UNDEFINED'),
decode(t.insert$*100 + t.update$*10 + t.delete$,
100, 'INSERT',
010, 'UPDATE',
001, 'DELETE',
110, 'INSERT OR UPDATE',
101, 'INSERT OR DELETE',
011, 'UPDATE OR DELETE',
111, 'INSERT OR UPDATE OR DELETE', 'ERROR'),
tabusr.name,
decode(bitand(t.property, 1), 1, 'VIEW',
0, 'TABLE',
'UNDEFINED'),
tabobj.name, NULL,
'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname,
t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR'),
t.definition,
decode(bitand(t.property, 2), 2, 'CALL',
'PL/SQL '),
t.action#
from sys.obj$ trigobj, sys.obj$ tabobj, sys.trigger$ t,
sys.user$ tabusr, sys.user$ trigusr
where (trigobj.obj# = t.obj# and
tabobj.obj# = t.baseobject and
tabobj.owner# = tabusr.user# and
trigobj.owner# = trigusr.user# and
bitand(t.property, 63) < 8 )
union all
select trigusr.name, trigobj.name,
decode(t.type#, 0, 'BEFORE EVENT',
2, 'AFTER EVENT',
'UNDEFINED'),
decode(bitand(t.sys_evts, 1), 1, 'STARTUP ') ||
decode(bitand(t.sys_evts, 2), 2,
decode(sign(bitand(t.sys_evts, 1)), 1, 'OR SHUTDOWN ',
'SHUTDOWN ')) ||
decode(bitand(t.sys_evts, 4), 4,
decode(sign(bitand(t.sys_evts, 3)), 1, 'OR ERROR ',
'ERROR ')) ||
decode(bitand(t.sys_evts, 8), 8,
decode(sign(bitand(t.sys_evts, 7)), 1, 'OR LOGON ',
'LOGON ')) ||
decode(bitand(t.sys_evts, 16), 16,
decode(sign(bitand(t.sys_evts, 15)), 1, 'OR LOGOFF ',
'LOGOFF ')) ||
decode(bitand(t.sys_evts, 262176), 32,
decode(sign(bitand(t.sys_evts, 31)), 1, 'OR CREATE ',
'CREATE ')) ||
decode(bitand(t.sys_evts, 262208), 64,
decode(sign(bitand(t.sys_evts, 63)), 1, 'OR ALTER ',
'ALTER ')) ||
decode(bitand(t.sys_evts, 262272), 128,
decode(sign(bitand(t.sys_evts, 127)), 1, 'OR DROP ',
'DROP ')) ||
decode (bitand(t.sys_evts, 262400), 256,
decode(sign(bitand(t.sys_evts, 255)), 1, 'OR ANALYZE ',
'ANALYZE ')) ||
decode (bitand(t.sys_evts, 262656), 512,
decode(sign(bitand(t.sys_evts, 511)), 1, 'OR COMMENT ',
'COMMENT ')) ||
decode (bitand(t.sys_evts, 263168), 1024,
decode(sign(bitand(t.sys_evts, 1023)), 1, 'OR GRANT ',
'GRANT ')) ||
decode (bitand(t.sys_evts, 264192), 2048,
decode(sign(bitand(t.sys_evts, 2047)), 1, 'OR REVOKE ',
'REVOKE ')) ||
decode (bitand(t.sys_evts, 266240), 4096,
decode(sign(bitand(t.sys_evts, 4095)), 1, 'OR TRUNCATE ',
'TRUNCATE ')) ||
decode (bitand(t.sys_evts, 270336), 8192,
decode(sign(bitand(t.sys_evts, 8191)), 1, 'OR RENAME ',
'RENAME ')) ||
decode (bitand(t.sys_evts, 278528), 16384,
decode(sign(bitand(t.sys_evts, 16383)), 1, 'OR ASSOCIATE STATISTICS ',
'ASSOCIATE STATISTICS ')) ||
decode (bitand(t.sys_evts, 294912), 32768,
decode(sign(bitand(t.sys_evts, 32767)), 1, 'OR AUDIT ',
'AUDIT ')) ||
decode (bitand(t.sys_evts, 327680), 65536,
decode(sign(bitand(t.sys_evts, 65535)), 1,
'OR DISASSOCIATE STATISTICS ', 'DISASSOCIATE STATISTICS ')) ||
decode (bitand(t.sys_evts, 393216), 131072,
decode(sign(bitand(t.sys_evts, 131071)), 1, 'OR NOAUDIT ',
'NOAUDIT ')) ||
decode (bitand(t.sys_evts, 262144), 262144,
decode(sign(bitand(t.sys_evts, 31)), 1, 'OR DDL ',
'DDL ')) ||
decode (bitand(t.sys_evts, 8388608), 8388608,
decode(sign(bitand(t.sys_evts, 8388607)), 1, 'OR SUSPEND ',
'SUSPEND ')),
'SYS',
'DATABASE ',
NULL,
NULL,
'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname
|| decode(bitand(t.property,32),32,' PARENT AS ' || t.refprtname,NULL),
t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR'),
t.definition,
decode(bitand(t.property, 2), 2, 'CALL',
'PL/SQL '),
t.action#
from sys.obj$ trigobj, sys.trigger$ t, sys.user$ trigusr
where (trigobj.obj# = t.obj# and
trigobj.owner# = trigusr.user# and
bitand(t.property, 63) >= 8 and bitand(t.property, 63) < 16)
union all
select trigusr.name, trigobj.name,
decode(t.type#, 0, 'BEFORE EVENT',
2, 'AFTER EVENT',
'UNDEFINED'),
decode(bitand(t.sys_evts, 1), 1, 'STARTUP ') ||
decode(bitand(t.sys_evts, 2), 2,
decode(sign(bitand(t.sys_evts, 1)), 1, 'OR SHUTDOWN ',
'SHUTDOWN ')) ||
decode(bitand(t.sys_evts, 4), 4,
decode(sign(bitand(t.sys_evts, 3)), 1, 'OR ERROR ',
'ERROR ')) ||
decode(bitand(t.sys_evts, 8), 8,
decode(sign(bitand(t.sys_evts, 7)), 1, 'OR LOGON ',
'LOGON ')) ||
decode(bitand(t.sys_evts, 16), 16,
decode(sign(bitand(t.sys_evts, 15)), 1, 'OR LOGOFF ',
'LOGOFF ')) ||
decode(bitand(t.sys_evts, 262176), 32,
decode(sign(bitand(t.sys_evts, 31)), 1, 'OR CREATE ',
'CREATE ')) ||
decode(bitand(t.sys_evts, 262208), 64,
decode(sign(bitand(t.sys_evts, 63)), 1, 'OR ALTER ',
'ALTER ')) ||
decode(bitand(t.sys_evts, 262272), 128,
decode(sign(bitand(t.sys_evts, 127)), 1, 'OR DROP ',
'DROP ')) ||
decode (bitand(t.sys_evts, 262400), 256,
decode(sign(bitand(t.sys_evts, 255)), 1, 'OR ANALYZE ',
'ANALYZE ')) ||
decode (bitand(t.sys_evts, 262656), 512,
decode(sign(bitand(t.sys_evts, 511)), 1, 'OR COMMENT ',
'COMMENT ')) ||
decode (bitand(t.sys_evts, 263168), 1024,
decode(sign(bitand(t.sys_evts, 1023)), 1, 'OR GRANT ',
'GRANT ')) ||
decode (bitand(t.sys_evts, 264192), 2048,
decode(sign(bitand(t.sys_evts, 2047)), 1, 'OR REVOKE ',
'REVOKE ')) ||
decode (bitand(t.sys_evts, 266240), 4096,
decode(sign(bitand(t.sys_evts, 4095)), 1, 'OR TRUNCATE ',
'TRUNCATE ')) ||
decode (bitand(t.sys_evts, 270336), 8192,
decode(sign(bitand(t.sys_evts, 8191)), 1, 'OR RENAME ',
'RENAME ')) ||
decode (bitand(t.sys_evts, 278528), 16384,
decode(sign(bitand(t.sys_evts, 16383)), 1, 'OR ASSOCIATE STATISTICS ',
'ASSOCIATE STATISTICS ')) ||
decode (bitand(t.sys_evts, 294912), 32768,
decode(sign(bitand(t.sys_evts, 32767)), 1, 'OR AUDIT ',
'AUDIT ')) ||
decode (bitand(t.sys_evts, 327680), 65536,
decode(sign(bitand(t.sys_evts, 65535)), 1,
'OR DISASSOCIATE STATISTICS ', 'DISASSOCIATE STATISTICS ')) ||
decode (bitand(t.sys_evts, 393216), 131072,
decode(sign(bitand(t.sys_evts, 131071)), 1, 'OR NOAUDIT ',
'NOAUDIT ')) ||
decode (bitand(t.sys_evts, 262144), 262144,
decode(sign(bitand(t.sys_evts, 31)), 1, 'OR DDL ',
'DDL ')) ||
decode (bitand(t.sys_evts, 8388608), 8388608,
decode(sign(bitand(t.sys_evts, 8388607)), 1, 'OR SUSPEND ',
'SUSPEND ')),
tabusr.name,
'SCHEMA',
NULL,
NULL,
'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname,
t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR'),
t.definition,
decode(bitand(t.property, 2), 2, 'CALL',
'PL/SQL '),
t.action#
from sys.obj$ trigobj, sys.trigger$ t, sys.user$ tabusr, sys.user$ trigusr
where (trigobj.obj# = t.obj# and
trigobj.owner# = trigusr.user# and
bitand(t.property, 63) >= 16 and bitand(t.property, 63) < 32 and
tabusr.user# = t.baseobject)
union all
select trigusr.name, trigobj.name,
decode(t.type#, 0, 'BEFORE STATEMENT',
1, 'BEFORE EACH ROW',
2, 'AFTER STATEMENT',
3, 'AFTER EACH ROW',
4, 'INSTEAD OF',
'UNDEFINED'),
decode(t.insert$*100 + t.update$*10 + t.delete$,
100, 'INSERT',
010, 'UPDATE',
001, 'DELETE',
110, 'INSERT OR UPDATE',
101, 'INSERT OR DELETE',
011, 'UPDATE OR DELETE',
111, 'INSERT OR UPDATE OR DELETE', 'ERROR'),
tabusr.name,
decode(bitand(t.property, 1), 1, 'VIEW',
0, 'TABLE',
'UNDEFINED'),
tabobj.name, ntcol.name,
'REFERENCING NEW AS '||t.refnewname||' OLD AS '||t.refoldname ||
' PARENT AS ' || t.refprtname,
t.whenclause,decode(t.enabled, 0, 'DISABLED', 1, 'ENABLED', 'ERROR'),
t.definition,
decode(bitand(t.property, 2), 2, 'CALL',
'PL/SQL '),
t.action#
from sys.obj$ trigobj, sys.obj$ tabobj, sys.trigger$ t,
sys.user$ tabusr, sys.user$ trigusr, sys.viewtrcol$ ntcol
where (trigobj.obj# = t.obj# and
tabobj.obj# = t.baseobject and
tabobj.owner# = tabusr.user# and
trigobj.owner# = trigusr.user# and
t.nttrigcol = ntcol.intcol# and
t.nttrigatt = ntcol.attribute# and
t.baseobject = ntcol.obj# and
bitand(t.property, 63) >= 32)
SQL>
-fin-
No comments:
Post a Comment