Oracle7 Server Administrator's Guide

Listing Information About Database Users and Profiles

The data dictionary stores information about every user and profile, including the following:

all users in a database

each user's default tablespace for tables, clusters, and indexes

each user's tablespace for temporary segments

each user's space quotas, if any

each user's assigned profile and resource limits

the cost assigned to each applicable system resource

each current session's memory usage

The following data dictionary views may be of interest when you work with database users and profiles:

ALL_USERS

USER_USERS

DBA_USERS

USER_TS_QUOTAS

DBA_TS_QUOTAS

USER_RESOURCE_LIMITS

DBA_PROFILES

RESOURCE_COST

V$SESSION

V$SESSTAT

V$STATNAME

See Also: See the Oracle7 Server Reference for detailed information about each view.

Listing Information about Users and Profiles: Examples

The examples in this section assume a database in which the following statements have been executed:

CREATE PROFILE clerk LIMIT

   SESSIONS_PER_USER 1

   IDLE_TIME 30

   CONNECT_TIME 600;

CREATE USER jfee

   IDENTIFIED BY wildcat

   DEFAULT TABLESPACE users

   TEMPORARY TABLESPACE temp_ts

   QUOTA 500K ON users

   PROFILE clerk;

CREATE USER tsmith

   IDENTIFIED BY bedrock

   DEFAULT TABLESPACE users

   TEMPORARY TABLESPACE temp_ts

   QUOTA unlimited ON users;

Listing All Users and Associated Information

The following query lists all users defined in the database:

SELECT * FROM sys.dba_users;

USERNA USER_ID PASSWORD         DEFAUL TEMPOR  CREATED   PROFILE

------ -------- ---------------- ------ ------  --------- --------

SYS          % 522D06CDE017CF93 SYSTEM SYSTEM  31-JUL-90 PUBLIC...

SYSTEM       % 9B30B3EB7A7EE46A SYSTEM SYSTEM  31-JUL-90 PUBLIC...

JFEE         % DEE4F647381D62C4 USERS  TEMP_TS 12-SEP-90 CLERK

TSMITH       % 4791F162172E7834 USERS  TEMP_TS 12-SEP-90 PUBLIC...

All passwords are encrypted to preserve security.

Listing Users' Roles

The following query lists, for each user, the roles granted to that user, and indicates whether each role is granted with the ADMIN OPTION and is a default role:

SELECT * FROM sys.dba_role_privs where grantee = 'JFEE';

GRANTEE                    GRANTED_ROLE              ADM DEF

-------------------------- ------------------------- --- ---

JFEE                       CLERK                     YES YES

JFEE                       PAYROLL                   NO  NO

JFEE                       WEEKLY_ADMIN              NO  NO

Listing All Tablespace Quotas

The following query lists all tablespace quotas specifically assigned to each user:

SELECT * FROM sys.dba_ts_quotas;

TABLESPACE USERNAME        BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS

---------- ---------- ---------- ---------- ---------- ----------

SYSTEM     SYSTEM              0          0          0          0

SYSTEM     JFEE                0     512000          0        250

SYSTEM     TSMITH              0         -1          0         -1

When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column. Unlimited quotas are indicated by "-1".

Listing All Profiles and Assigned Limits

The following query lists all profiles in the database and associated settings for each limit in each profile:

SELECT * FROM sys.dba_profiles

   ORDER BY profile;

PROFILE          RESOURCE_NAME                    LIMIT

---------------  -------------------------------- --------------

CLERK            COMPOSITE_LIMIT                  UNLIMITED

CLERK            SESSIONS_PER_USER                1

CLERK            CPU_PER_SESSION                  UNLIMITED

CLERK            CPU_PER_CALL                     UNLIMITED

CLERK            LOGICAL_READS_PER_SESSION        UNLIMITED

CLERK            LOGICAL_READS_PER_CALL           UNLIMITED

CLERK            IDLE_TIME                        30

CLERK            CONNECT_TIME                     600

CLERK            PRIVATE_SGA                      UNLIMITED

DEFAULT          COMPOSITE_LIMIT                  UNLIMITED

DEFAULT          SESSIONS_PER_USER                UNLIMITED

DEFAULT          CPU_PER_SESSION                  UNLIMITED

DEFAULT          CPU_PER_CALL                     UNLIMITED

DEFAULT          LOGICAL_READS_PER_SESSION        UNLIMITED

DEFAULT          LOGICAL_READS_PER_CALL           UNLIMITED

DEFAULT          IDLE_TIME                        UNLIMITED

DEFAULT          CONNECT_TIME                     UNLIMITED

DEFAULT          PRIVATE_SGA                      UNLIMITED

Viewing Memory Use Per User Session

The following query lists all current sessions, showing the Oracle user and current memory use per session:

SELECT username, value || 'bytes' "Current session memory"

   FROM v$session sess, v$sesstat stat, v$statname name

WHERE sess.sid = stat.sid

   AND stat.statistic# = name.statistic#

   AND name.name = 'session memory';

The amount of space indicated in "Current session memory" is allocated in the shared pool for each session connected through the multi-threaded server. You can limit the amount of memory allocated per user with the PRIVATE_SGA resource limit.

To see the maximum memory ever allocated to each session since the instance started, replace 'session memory' in the query above with 'max session memory'.