DBA_USERS view tips

Oracle Tips by Burleson Consulting

DBA_USERS

<< Return to the index

Oracle 11g makes the following comments about the DBA_USERS table: "Information about all users of the database"

Related notes on DBA_USERS:

Column description of the DBA_USERS view:

USERNAME

Description of DBA_USERS.USERNAME: "Name of the user"

USER_ID

Description of DBA_USERS.USER_ID: "ID number of the user"

PASSWORD

Description of DBA_USERS.PASSWORD: "Encrypted password"

ACCOUNT_STATUS

No comments exist for the ACCOUNT_STATUS column of the DBA_USERS table.

LOCK_DATE

No comments exist for the LOCK_DATE column of the DBA_USERS table.

EXPIRY_DATE

No comments exist for the EXPIRY_DATE column of the DBA_USERS table.

DEFAULT_TABLESPACE

Description of DBA_USERS.DEFAULT_TABLESPACE: "Default tablespace for data"

TEMPORARY_TABLESPACE

Description of DBA_USERS.TEMPORARY_TABLESPACE: "Default tablespace for temporary tables"

CREATED

Description of DBA_USERS.CREATED: "User creation date"

PROFILE

Description of DBA_USERS.PROFILE: "User resource profile name"

INITIAL_RSRC_CONSUMER_GROUP

Description of DBA_USERS.INITIAL_RSRC_CONSUMER_GROUP: "User's initial consumer group"

EXTERNAL_NAME

Description of DBA_USERS.EXTERNAL_NAME: "User external name"

PASSWORD_VERSIONS

Description of DBA_USERS.PASSWORD_VERSIONS: "Versions of encrypted passwords"

EDITIONS_ENABLED

Description of DBA_USERS.EDITIONS_ENABLED: "Whether editions are enabled for this user"

DBA_USERS View Source

Oracle 11g's data dictionary defines the DBA_USERS view using the following source query:

select u.name, u.user#,
decode(u.password, 'GLOBAL', u.password,
'EXTERNAL', u.password,
NULL),
m.status,
decode(u.astatus, 4, u.ltime,
5, u.ltime,
6, u.ltime,
8, u.ltime,
9, u.ltime,
10, u.ltime, to_date(NULL)),
decode(u.astatus,
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(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(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL),
decode(bitand(u.spare1, 16),
16, '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#
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

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books