RelationalDBDesign RelationalDBDesign


Creating Users   «Prev  Next»
Lesson 14Users in the data Dictionary
Objective View user information in the Data Dictionary

Users in the Oracle Data Dictionary

Several data dictionary views can be used to retrieve information about users. The following MouseOver shows some of the views related to what you have learned in this chapter.

  1. The DBA_USERS view returns one row for each user in the database. Use this view to find out about default and temporary tablespace assignments, profile assignments, account creation date, and password expiration date
  2. The DBA_SYS_PRIVS view returns information about system privileges that have been granted to a user.
  3. The DBA_TS_QUOTAS view returns information about a user's quotas on tablespaces in the database.

Viewing User Information
For the most part, it should be fairly obvious how to query these views. The column names explain their contents quite well.

Listing the privileges for a user

The following query may be used to list all the system privileges that have been granted to a user:
SELECT privilege
FROM dba_sys_privs
WHERE grantee='USERNAME';

Replace username with the name of the user who's privileges you are researching. Usernames should normally be specified in upper-case form. For example, use 'SYSTEM', not 'system'.

Listing a user's tablespace quotas

To determine a user's tablespace quotas and find out how much space has actually been used, you can use this query:
SELECT tablespace_name,
       bytes space_used,
       max_bytes quota
FROM dba_ts_quotas
WHERE username = 'USERNAME';

As with the previous query, the username should be upper-case. A quota of -1 indicates that the user has been given unlimited quota on a tablespace

Data Dictionary - Queries - Exercise

Click the Exercise link below to practice viewing user information.
Data Dictionary Queries - Exercise