Oracle database user privileges

List Oracle Role Privileges

select
   grantee           role_name,
   privilege         privilege,
   admin_option      admin_grantable
from
   dba_sys_privs
where
   grantee='<ROLE NAME>'

Lookup sysdba users

SQL> SELECT * FROM v$pwfile_users;
USERNAME     SYSDB       SYSOP
------------------------------
SYS          TRUE         TRUE
TESTER       TRUE         TRUE
SQL>

Lookup user privileges and permissions

SQL> SELECT * FROM dba_role_privs WHERE grantee='ETRIALS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
ETRIALS                        CONNECT                        NO  YES
ETRIALS                        RESOURCE                       NO  YES

SQL> SELECT * FROM dba_sys_privs WHERE grantee='ETRIALS';  

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
ETRIALS                        CREATE VIEW                              NO
ETRIALS                        UNLIMITED TABLESPACE                     NO

SQL>

to see users quotas:

SQL> SELECT tablespace_name, username, bytes FROM DBA_TS_QUOTAS;
TABLESPACE    USERNAME       BYTES
----------    --------- ----------
SYSAUX        DMSYS         196608
SYSAUX        OLAPSYS     16252928
SYSAUX        WK_TEST     12582912
SYSAUX        SYSMAN      78577664
RMAN_TBSP     RMAN         8585216
SQL>

---

Oracle User Temporary Password Change

SQL> select password from dba_users where username='SCOTT';
PASSWORD
-----------------------------
F894844C34402B67

SQL> alter user scott identified by lion;
User altered.

SQL> connect scott/lion
Connected.

REM Do whatever you like...

SQL> connect system/manager
Connected.

SQL> alter user scott identified by values 'F894844C34402B67';
User altered.

SQL> connect scott/tiger
Connected.