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.
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
The DBA_SYS_PRIVS view returns information about system privileges that have been granted to a user.
The DBA_TS_QUOTAS view returns information about a user's quotas on tablespaces in the database.
Oracle Data Dictionary Concepts
The following static data dictionary views are new in the release of Oracle 12c:
-- ALL_CLUSTERING_DIMENSIONS, DBA_CLUSTERING_DIMENSIONS, and
USER_CLUSTERING_DIMENSIONS
This initialization parameter was introduced in Oracle Database 11g Release 2 (11.2.0.4), but is available for the first time in Oracle Database 12c with Oracle Database 12c Release 1 (12.1.0.2).
-- ALL_CLUSTERING_JOINS, DBA_CLUSTERING_JOINS, and
USER_CLUSTERING_JOINS
-- ALL_CLUSTERING_KEYS, DBA_CLUSTERING_KEYS, and
USER_CLUSTERING_KEYS
-- ALL_CLUSTERING_TABLES, DBA_CLUSTERING_TABLES, and
USER_CLUSTERING_TABLES
-- ALL_JSON_COLUMNS, DBA_JSON_COLUMNS, and USER_JSON_COLUMNS
-- ALL_ZONEMAP_MEASURES, DBA_ZONEMAP_MEASURES, and
USER_ZONEMAP_MEASURES
-- ALL_ZONEMAPS, DBA_ZONEMAPS, and USER_ZONEMAPS
-- DBA_HIST_IM_SEG_STAT
-- DBA_HIST_IM_SEG_STAT_OBJ
-- DBA_PDB_SAVED_STATES
-- DBA_ROLLING_UNSUPPORTED
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
