user_conf.sql

-- user_conf.sql
-- Andy Barry
-- 20/02/06

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