Query - User Information

USER INFORMATION

set lines 100 pages 999

set verify off

set feedback off

undefine user

accept userid prompt 'Enter username:'

select username,default_tablespace,temporary_tablespace from dba_users where username = '&userid'

/

select tablespace_name,decode(max_bytes,-1,'unlimited',ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA" from dba_ts_quotas where  username = upper('&&userid')

/

select granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "ROLE" from dba_role_privs where grantee = upper('&&userid')

/

select privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "PRIV" from dba_sys_privs where  grantee = upper('&&userid')

/

undefine user

set verify on

set feedback on