select u.name, u.user#,
decode(u.password, 'GLOBAL', u.password,
'EXTERNAL', u.password,
NULL),
m.status,
decode(mod(u.astatus, 16), 4, u.ltime,
5, u.ltime,
6, u.ltime,
8, u.ltime,
9, u.ltime,
10, u.ltime, to_date(NULL)),
decode(mod(u.astatus, 16),
1, u.exptime,
2, u.exptime,
5, u.exptime,
6, u.exptime,
9, u.exptime,
10, u.exptime,
decode(u.password, 'GLOBAL', to_date(NULL),
'EXTERNAL', to_date(NULL),
decode(u.ptime, '', to_date(NULL),
decode(pr.limit#, 2147483647, to_date(NULL),
decode(pr.limit#, 0,
decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
dp.limit#/86400),
u.ptime + pr.limit#/86400))))),
dts.name, tts.name, u.ctime, p.name,
nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
u.ext_username,
decode(
REGEXP_INSTR(
NVL2(u.password, u.password, ' '),
'^ $'
),
0,
decode(length(u.password), 16, '10G ', NULL),
''
) ||
decode(
REGEXP_INSTR(
REGEXP_REPLACE(
NVL2(u.spare4, u.spare4, ' '),
'S:000000000000000000000000000000000000000000000000000000000000',
'not_a_verifier'
),
'S:'
),
0, '', '11G '
) ||
decode(
REGEXP_INSTR(
NVL2(u.spare4, u.spare4, ' '),
'T:'
),
0, '', '12C '
) ||
decode(
REGEXP_INSTR(
REGEXP_REPLACE(
NVL2(u.spare4, u.spare4, ' '),
'H:00000000000000000000000000000000',
'not_a_verifier'
),
'H:'
),
0, '', 'HTTP '
),
decode(bitand(u.spare1, 16),
16, 'Y',
'N'),
decode(u.password, 'GLOBAL', 'GLOBAL',
'EXTERNAL', 'EXTERNAL',
'PASSWORD'),
decode(bitand(u.spare1, 2080),
32, 'Y', 2048, 'Y', 2080, 'Y',
'N'),
decode(bitand(u.spare1, 128), 128, 'YES', 'NO'),
from_tz(to_timestamp(to_char(u.spare6, 'DD-MON-YYYY HH24:MI:SS'),
'DD-MON-YYYY HH24:MI:SS'), '0:00')
at time zone sessiontimezone,
decode(bitand(u.spare1, 256), 256, 'Y', 'N')
from sys.user$ u left outer join sys.resource_group_mapping$ cgm
on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
cgm.value = u.name),
sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
where u.datats# = dts.ts#
and u.resource$ = p.profile#
and u.tempts# = tts.ts#
and ((u.astatus = m.status#) or
(u.astatus = (m.status# + 16 - BITAND(m.status#, 16))))
and u.type# = 1
and u.resource$ = pr.profile#
and dp.profile# = 0
and dp.type#=1
and dp.resource#=1
and pr.type# = 1
and pr.resource# = 1
SELECT U.NAME
, U.USER#
,
DECODE(U.PASSWORD
, 'GLOBAL'
, U.PASSWORD
,
'EXTERNAL'
, U.PASSWORD
,
NULL)
,
M.STATUS
,
DECODE(MOD(U.ASTATUS
, 16)
, 4
, U.LTIME
,
5
, U.LTIME
,
6
, U.LTIME
,
8
, U.LTIME
,
9
, U.LTIME
,
10
, U.LTIME
, TO_DATE(NULL))
,
DECODE(MOD(U.ASTATUS
, 16)
,
1
, U.EXPTIME
,
2
, U.EXPTIME
,
5
, U.EXPTIME
,
6
, U.EXPTIME
,
9
, U.EXPTIME
,
10
, U.EXPTIME
,
DECODE(U.PASSWORD
, 'GLOBAL'
, TO_DATE(NULL)
,
'EXTERNAL'
, TO_DATE(NULL)
,
DECODE(U.PTIME
, ''
, TO_DATE(NULL)
,
DECODE(PR.LIMIT#
, 2147483647
, TO_DATE(NULL)
,
DECODE(PR.LIMIT#
, 0
,
DECODE(DP.LIMIT#
, 2147483647
, TO_DATE(NULL)
, U.PTIME +
DP.LIMIT#/86400)
,
U.PTIME + PR.LIMIT#/86400)))))
,
DTS.NAME
, TTS.NAME
, U.CTIME
, P.NAME
,
NVL(CGM.CONSUMER_GROUP
, 'DEFAULT_CONSUMER_GROUP')
,
U.EXT_USERNAME
,
DECODE(
REGEXP_INSTR(
NVL2(U.PASSWORD
, U.PASSWORD
, ' ')
,
'^ $'
)
,
0
,
DECODE(LENGTH(U.PASSWORD)
, 16
, '10G '
, NULL)
,
''
) ||
DECODE(
REGEXP_INSTR(
REGEXP_REPLACE(
NVL2(U.SPARE4
, U.SPARE4
, ' ')
,
'S:000000000000000000000000000000000000000000000000000000000000'
,
'NOT_A_VERIFIER'
)
,
'S:'
)
,
0
, ''
, '11G '
) ||
DECODE(
REGEXP_INSTR(
NVL2(U.SPARE4
, U.SPARE4
, ' ')
,
'T:'
)
,
0
, ''
, '12C '
) ||
DECODE(
REGEXP_INSTR(
REGEXP_REPLACE(
NVL2(U.SPARE4
, U.SPARE4
, ' ')
,
'H:00000000000000000000000000000000'
,
'NOT_A_VERIFIER'
)
,
'H:'
)
,
0
, ''
, 'HTTP '
)
,
DECODE(BITAND(U.SPARE1
, 16)
,
16
, 'Y'
,
'N')
,
DECODE(U.PASSWORD
, 'GLOBAL'
, 'GLOBAL'
,
'EXTERNAL'
, 'EXTERNAL'
,
'PASSWORD')
,
DECODE(BITAND(U.SPARE1
, 2080)
,
32
, 'Y'
, 2048
, 'Y'
, 2080
, 'Y'
,
'N')
,
DECODE(BITAND(U.SPARE1
, 128)
, 128
, 'YES'
, 'NO')
,
FROM_TZ(TO_TIMESTAMP(TO_CHAR(U.SPARE6
, 'DD-MON-YYYY HH24:MI:SS')
,
'DD-MON-YYYY HH24:MI:SS')
, '0:00')
AT TIME ZONE SESSIONTIMEZONE
,
DECODE(BITAND(U.SPARE1
, 256)
, 256
, 'Y'
, 'N')
FROM SYS.USER$ U LEFT OUTER JOIN SYS.RESOURCE_GROUP_MAPPING$ CGM
ON (CGM.ATTRIBUTE = 'ORACLE_USER'
AND CGM.STATUS = 'ACTIVE' AND
CGM.VALUE = U.NAME)
,
SYS.TS$ DTS
, SYS.TS$ TTS
, SYS.PROFNAME$ P
,
SYS.USER_ASTATUS_MAP M
, SYS.PROFILE$ PR
, SYS.PROFILE$ DP
WHERE U.DATATS# = DTS.TS#
AND U.RESOURCE$ = P.PROFILE#
AND U.TEMPTS# = TTS.TS#
AND ((U.ASTATUS = M.STATUS#) OR
(U.ASTATUS = (M.STATUS# + 16 - BITAND(M.STATUS#
, 16))))
AND U.TYPE# = 1
AND U.RESOURCE$ = PR.PROFILE#
AND DP.PROFILE# = 0
AND DP.TYPE#=1
AND DP.RESOURCE#=1
AND PR.TYPE# = 1
AND PR.RESOURCE# = 1
|
|
|