Oracle User Info

User Quick info



SET VERIFY OFF FEEDBACK OFF PAGES 10000 LIN 80 RECSEP OFF ECHO OFF ARRAY 5
CL COL
SPOOL output.lst
COL username NOPRINT
COL external_name NOPRINT
COL default_tablespace FOR A16 HEADING "Default TS"
COL temporary_tablespace FOR A16 HEADING "Temporary TS"
COL account_status FOR A16
COL password FOR A16
COL profile FOR A16
COL grantee FOR A16 NOPRINT
COL owner FOR A16
COL table_name FOR A16
COL grantor FOR A16
PROMPT
PROMPT ============================= DBA_USERS ===============================
SELECT *
FROM dba_users WHERE username = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted roles ===========================
SELECT * FROM dba_role_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted system privileges ===============
SELECT * FROM dba_sys_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted object privileges ===============
COL privilege FOR A20 WORD_WRAPPED
SELECT * FROM dba_tab_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted column privileges ===============
SELECT *
FROM dba_col_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Tablespace quotas =======================
SELECT tablespace_name,
DECODE(max_bytes ,-1,'UNLIMITED',TO_CHAR(max_bytes/1048576,9999.9 )||' Mb') "Quota"
FROM dba_ts_quotas
WHERE username = UPPER('&&1')
/
PROMPT
PROMPT ============================= Database objects ========================
SELECT object_type, COUNT(*) FROM dba_objects WHERE owner = UPPER('&&1')
GROUP BY object_type
/
PROMPT
PROMPT ============================= End of report ===========================
SPOOL OFF
UNDEFINE USER
ed output.lst
PROMPT Output is spooled in output.lst
SET FEEDBACK ON RECSEP WR
SET PAGES 24
CL COL