VIEW: SYS.USER_USERS

Source

View Text - Preformatted

select u.name, u.user#,
       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.ptime, '', to_date(NULL),
                decode(p.limit#, 2147483647, to_date(NULL),
                 decode(p.limit#, 0,
                   decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
                     dp.limit#/86400),
                   u.ptime + p.limit#/86400)))),
       dts.name, tts.name, u.ctime,
       nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
       u.ext_username,
       decode(bitand(u.spare1, 2080),
              32, 'Y', 2048, 'Y', 2080, 'Y',
                  'N'),
       decode(bitand(u.spare1, 128), 128, 'YES', 'NO'),
       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.user_astatus_map m,
     profile$ p, profile$ dp
where u.datats# = dts.ts#
  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.user# = userenv('SCHEMAID')
  and u.resource$ = p.profile#
  and dp.profile# = 0
  and dp.type# = 1
  and dp.resource# = 1
  and p.type# = 1
  and p.resource# = 1

View Text - HTML Formatted

SELECT U.NAME
, U.USER#
, 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.PTIME
, ''
, TO_DATE(NULL)
, DECODE(P.LIMIT#
, 2147483647
, TO_DATE(NULL)
, DECODE(P.LIMIT#
, 0
, DECODE(DP.LIMIT#
, 2147483647
, TO_DATE(NULL)
, U.PTIME + DP.LIMIT#/86400)
, U.PTIME + P.LIMIT#/86400))))
, DTS.NAME
, TTS.NAME
, U.CTIME
, NVL(CGM.CONSUMER_GROUP
, 'DEFAULT_CONSUMER_GROUP')
, U.EXT_USERNAME
, DECODE(BITAND(U.SPARE1
, 2080)
, 32
, 'Y'
, 2048
, 'Y'
, 2080
, 'Y'
, 'N')
, DECODE(BITAND(U.SPARE1
, 128)
, 128
, 'YES'
, 'NO')
, 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.USER_ASTATUS_MAP M
, PROFILE$ P
, PROFILE$ DP WHERE U.DATATS# = DTS.TS#
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.USER# = USERENV('SCHEMAID')
AND U.RESOURCE$ = P.PROFILE#
AND DP.PROFILE# = 0
AND DP.TYPE# = 1
AND DP.RESOURCE# = 1
AND P.TYPE# = 1
AND P.RESOURCE# = 1