管理用户（三）--显示用户信息

 

1、显示用户信息

（1）显示当前会话用户

10:16:18 SQL> show user;

USER is "SYS"

（2）显示特权用户

10:17:07 SQL> conn sys/oracle as sysdba;

Connected.

10:17:09 SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP

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

SYS                            TRUE  TRUE

（3)显示用户信息

10:17:22 SQL> col username format a20

10:18:32 SQL> col default_tablespace for a20

10:18:45 SQL> col temporary_tablespace for a20

10:18:59 SQL> select username,default_tablespace ,temporary_tablespace

10:19:22   2    from dba_users;

USERNAME             DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE

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

OPS$ORACLE           USERS                TEMP

SCOTT                USERS                TEMGRP01

HR                   USERS                TEMGRP01

TEST                 USERS                TEMGRP01

TSMSYS               USERS                TEMGRP01

BI                   USERS                TEMGRP01

PM                   USERS                TEMGRP01

MDDATA               USERS                TEMGRP01

IX                   USERS                TEMGRP01

SH                   USERS                TEMGRP01

DIP                  USERS                TEMGRP01

OE                   USERS                TEMGRP01

DBSNMP               SYSAUX               TEMGRP01

SYSMAN               SYSAUX               TEMGRP01

MDSYS                SYSAUX               TEMGRP01

ORDSYS               SYSAUX               TEMGRP01

EXFSYS               SYSAUX               TEMGRP01

USERNAME             DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE

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

DMSYS                SYSAUX               TEMGRP01

WMSYS                SYSAUX               TEMGRP01

CTXSYS               SYSAUX               TEMGRP01

ANONYMOUS            SYSAUX               TEMGRP01

XDB                  SYSAUX               TEMGRP01

ORDPLUGINS           SYSAUX               TEMGRP01

SI_INFORMTN_SCHEMA   SYSAUX               TEMGRP01

OLAPSYS              SYSAUX               TEMGRP01

MGMT_VIEW            SYSTEM               TEMGRP01

SYS                  SYSTEM               TEMGRP01

SYSTEM               SYSTEM               TEMGRP01

OUTLN                SYSTEM               TEMGRP01

29 rows selected.

（4）显示用户表空间配额

10:21:14 SQL> select tablespace_name,bytes,max_bytes

10:21:15   2    from dba_ts_quotas

10:21:27   3      where username='SCOTT';

no rows selected

10:21:44 SQL> select tablespace_name,bytes,max_bytes

10:21:56   2    from dba_ts_quotas

10:21:58   3      where username='SYS';

no rows selected

10:22:16 SQL> select tablespace_name,bytes,max_bytes

10:22:25   2    from dba_ts_quotas ;

TABLESPACE_NAME                     BYTES  MAX_BYTES

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

SYSAUX                           16318464         -1

SYSAUX                             262144  209715200

SYSAUX                           53936128         -1