VIEW: SYS.DBA_USERS

Source

View Text - Preformatted

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

View Text - HTML Formatted

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