SELECT u.name, o.name, a.name, a.position,
CASE WHEN (a.user_type_num IS NULL) THEN
DECODE(a.type_number,
0, null,
1, decode(a.flags, 512, 'NVARCHAR2', 'VARCHAR2'),
2, decode(a.flags, 512, 'FLOAT', 'NUMBER'),
3, 'NATIVE INTEGER',
8, 'LONG',
9, decode(a.flags, 512, 'NCHAR VARYING', 'VARCHAR'),
11, 'ROWID',
12, 'DATE',
23, 'RAW',
24, 'LONG RAW',
29, 'BINARY_INTEGER',
69, 'ROWID',
96, decode(a.flags, 512, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
102, 'REF CURSOR',
104, 'UROWID',
105, 'MLSLABEL',
106, 'MLSLABEL',
110, 'REF',
111, 'REF',
112, decode(a.flags, 512, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, 'OBJECT',
122, 'TABLE',
123, 'VARRAY',
178, 'TIME',
179, 'TIME WITH TIME ZONE',
180, 'TIMESTAMP',
181, 'TIMESTAMP WITH TIME ZONE',
231, 'TIMESTAMP WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR TO MONTH',
183, 'INTERVAL DAY TO SECOND',
250, 'PL/SQL RECORD',
251, 'PL/SQL TABLE',
252, 'PL/SQL BOOLEAN',
'UNDEFINED')
ELSE t_u.name ||'.'|| t_o.name END,
DECODE(bitand(a.flags, 2+4+64+128+256+1024+2048+8192+16384+32768
+65536+131072+262144+524288+1048576),
2,'JOB_NAME',4,'JOB_OWNER',
64, 'JOB_START', 128, 'WINDOW_START',
256, 'WINDOW_END', 1024, 'JOB_SUBNAME',
2048, 'EVENT_MESSAGE', 8192, 'JOB_SCHEDULED_START',
16384, 'CHAIN_ID', 32768, 'CREDENTIAL_OWNER',
65536, 'CREDENTIAL_NAME', 131072, 'DESTINATION_OWNER',
262144, 'DESTINATION_NAME', 524288, 'JOB_DEST_ID',
1048576, 'LOG_ID', ''),
dbms_scheduler.get_varchar2_value(a.value), a.value,
DECODE(BITAND(a.flags,1),0,'FALSE',1,'TRUE')
FROM obj$ o, user$ u, sys.scheduler$_program_argument a, obj$ t_o, user$ t_u
WHERE a.oid = o.obj# AND u.user# = o.owner#
AND a.user_type_num = t_o.obj#(+) AND t_o.owner# = t_u.user#(+)
SELECT U.NAME
, O.NAME
, A.NAME
, A.POSITION
,
CASE WHEN (A.USER_TYPE_NUM IS NULL) THEN
DECODE(A.TYPE_NUMBER
,
0
, NULL
,
1
, DECODE(A.FLAGS
, 512
, 'NVARCHAR2'
, 'VARCHAR2')
,
2
, DECODE(A.FLAGS
, 512
, 'FLOAT'
, 'NUMBER')
,
3
, 'NATIVE INTEGER'
,
8
, 'LONG'
,
9
, DECODE(A.FLAGS
, 512
, 'NCHAR VARYING'
, 'VARCHAR')
,
11
, 'ROWID'
,
12
, 'DATE'
,
23
, 'RAW'
,
24
, 'LONG RAW'
,
29
, 'BINARY_INTEGER'
,
69
, 'ROWID'
,
96
, DECODE(A.FLAGS
, 512
, 'NCHAR'
, 'CHAR')
,
100
, 'BINARY_FLOAT'
,
101
, 'BINARY_DOUBLE'
,
102
, 'REF CURSOR'
,
104
, 'UROWID'
,
105
, 'MLSLABEL'
,
106
, 'MLSLABEL'
,
110
, 'REF'
,
111
, 'REF'
,
112
, DECODE(A.FLAGS
, 512
, 'NCLOB'
, 'CLOB')
,
113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
,
121
, 'OBJECT'
,
122
, 'TABLE'
,
123
, 'VARRAY'
,
178
, 'TIME'
,
179
, 'TIME WITH TIME ZONE'
,
180
, 'TIMESTAMP'
,
181
, 'TIMESTAMP WITH TIME ZONE'
,
231
, 'TIMESTAMP WITH LOCAL TIME ZONE'
,
182
, 'INTERVAL YEAR TO MONTH'
,
183
, 'INTERVAL DAY TO SECOND'
,
250
, 'PL/SQL RECORD'
,
251
, 'PL/SQL TABLE'
,
252
, 'PL/SQL BOOLEAN'
,
'UNDEFINED')
ELSE T_U.NAME ||'.'|| T_O.NAME END
,
DECODE(BITAND(A.FLAGS
, 2+4+64+128+256+1024+2048+8192+16384+32768
+65536+131072+262144+524288+1048576)
,
2
, 'JOB_NAME'
, 4
, 'JOB_OWNER'
,
64
, 'JOB_START'
, 128
, 'WINDOW_START'
,
256
, 'WINDOW_END'
, 1024
, 'JOB_SUBNAME'
,
2048
, 'EVENT_MESSAGE'
, 8192
, 'JOB_SCHEDULED_START'
,
16384
, 'CHAIN_ID'
, 32768
, 'CREDENTIAL_OWNER'
,
65536
, 'CREDENTIAL_NAME'
, 131072
, 'DESTINATION_OWNER'
,
262144
, 'DESTINATION_NAME'
, 524288
, 'JOB_DEST_ID'
,
1048576
, 'LOG_ID'
, '')
,
DBMS_SCHEDULER.GET_VARCHAR2_VALUE(A.VALUE)
, A.VALUE
,
DECODE(BITAND(A.FLAGS
, 1)
, 0
, 'FALSE'
, 1
, 'TRUE')
FROM OBJ$ O
, USER$ U
, SYS.SCHEDULER$_PROGRAM_ARGUMENT A
, OBJ$ T_O
, USER$ T_U
WHERE A.OID = O.OBJ#
AND U.USER# = O.OWNER#
AND A.USER_TYPE_NUM = T_O.OBJ#(+)
AND T_O.OWNER# = T_U.USER#(+)
|
|
|