SQL> desc dba_users
 Name                                      Null?    Type
 ---------------------------- -------- -----------------
 USERNAME                     NOT NULL VARCHAR2(30)      用户名
 USER_ID                      NOT NULL NUMBER            用户ID
 PASSWORD                              VARCHAR2(30)      用户密码                                                                                    
 ACCOUNT_STATUS               NOT NULL VARCHAR2(32)      用户状态：
                                                         OPEN                               
                                                         EXPIRED                            
                                                         EXPIRED(GRACE)                     
                                                         LOCKED(TIMED)                      
                                                         LOCKED                             
                                                         EXPIRED & LOCKED(TIMED)            
                                                         EXPIRED(GRACE) & LOCKED(TIMED)     
                                                         EXPIRED & LOCKED                   
                                                         EXPIRED(GRACE) & LOCKED    
 LOCK_DATE                             DATE              如果帐户状态被锁定，帐户被锁定。
 EXPIRY_DATE                           DATE              帐户到期日
 DEFAULT_TABLESPACE           NOT NULL VARCHAR2(30)      默认表空间
 TEMPORARY_TABLESPACE         NOT NULL VARCHAR2(30)      默认临时表空间
 CREATED                      NOT NULL DATE              用户创建时间
 PROFILE                      NOT NULL VARCHAR2(30)      用户资源配置文件名
 INITIAL_RSRC_CONSUMER_GROUP           VARCHAR2(30)      对用户的初始资源消费者组
 EXTERNAL_NAME                         VARCHAR2(4000)    用户的外部名称
 PASSWORD_VERSIONS                     VARCHAR2(8)       显示版本的密码列表的哈希值（也被称为“验证”）的账户存在。
                                                         password_versions列值包括10g，如果老不区分大小写的Oracle 11g和哈希存在如果SHA-1哈希存在。
                                                         值得注意的是，上述一个或两个验证者可以为任何给定的账户存在。
 EDITIONS_ENABLED                      VARCHAR2(1)       指出是否版已经对相应的用户启用（Y）或没有（N）
 AUTHENTICATION_TYPE                   VARCHAR2(8)       指示用户的身份验证机制：
                                                         EXTERNAL - CREATE USER user1 IDENTIFIED EXTERNALLY;    
                                                         GLOBAL - CREATE USER user2 IDENTIFIED GLOBALLY;        
                                                         PASSWORD - CREATE USER user3 IDENTIFIED BY user3;      

查看用户是否被锁以及被锁的时间和过期时间
SQL> select USERNAME,USER_ID,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users where ACCOUNT_STATUS<>'OPEN';

USERNAME                          USER_ID ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA
------------------------------ ---------- -------------------------------- --------- ---------
ORACLE_OCM                             21 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
XS$NULL                        2147483638 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
MDDATA                                 64 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
DIP                                    14 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
APEX_PUBLIC_USER                       75 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
SPATIAL_CSW_ADMIN_USR                  69 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
SPATIAL_WFS_ADMIN_USR                  66 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
FLOWS_FILES                            74 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
MDSYS                                  57 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
ORDSYS                                 53 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
EXFSYS                                 42 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17

USERNAME                          USER_ID ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA
------------------------------ ---------- -------------------------------- --------- ---------
DBSNMP                                 30 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
WMSYS                                  32 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
APPQOSSYS                              31 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
APEX_030200                            77 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
ORDDATA                                54 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
CTXSYS                                 43 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
ANONYMOUS                              46 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
SYSMAN                                 71 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
XDB                                    45 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
ORDPLUGINS                             55 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
SI_INFORMTN_SCHEMA                     56 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17

USERNAME                          USER_ID ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA
------------------------------ ---------- -------------------------------- --------- ---------
OLAPSYS                                60 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
OUTLN                                   9 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17
MGMT_VIEW                              73 EXPIRED & LOCKED                 21-MAY-17 21-MAY-17

25 rows selected.

SQL>

查看用户所属默认表空间
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME='SCOTT';

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT                          USERS                          TEMP

查看用户的创建时间以及profile名称。
SQL> select username,created,profile from dba_users;

USERNAME                       CREATED   PROFILE
------------------------------ --------- ------------------------------
SYS                            21-MAY-17 DEFAULT
SYSTEM                         21-MAY-17 DEFAULT
SCOTT                          22-MAY-17 DEFAULT
OUTLN                          21-MAY-17 DEFAULT
MGMT_VIEW                      21-MAY-17 DEFAULT
FLOWS_FILES                    21-MAY-17 DEFAULT
MDSYS                          21-MAY-17 DEFAULT
ORDSYS                         21-MAY-17 DEFAULT
EXFSYS                         21-MAY-17 DEFAULT
DBSNMP                         21-MAY-17 DEFAULT
WMSYS                          21-MAY-17 DEFAULT

SQL> col EXTERNAL_NAME for a20
SQL>  select INITIAL_RSRC_CONSUMER_GROUP,EXTERNAL_NAME,PASSWORD_VERSIONS,EDITIONS_ENABLED,AUTHENTICATION_TYPE from dba_users where username='SCOTT';

INITIAL_RSRC_CONSUMER_GROUP    EXTERNAL_NAME        PASSWORD E AUTHENTI
------------------------------ -------------------- -------- - --------
DEFAULT_CONSUMER_GROUP                              10G 11G  N PASSWORD