To become an AWESOME DBA, you need to grow your script collection.
The script I am attaching here, puts together everything we talked about. No need for GUI, just use sqlplus, which is available everywhere. In one second you get all the info you need at the
press of a button!
/*
Script name: user.sql
Example: @user.sql DROBETE
Created By Diana
Robete
Copyright: @2016 dbaparadise.com
*/
set define '&'
set verify off
set feedback off
define _username=&1
col username for A10
col status for A10
col default_role for a7
col grantee for A20
col privilege for A30
col expiry for A15
col def_tbs for A20
col temp_tbs for A20
set linesize 180
set pagesize 100
Prompt
Prompt User info for &_username
select username, account_status status, expiry_date expiry, created, default_tablespace def_tbs, temporary_tablespace temp_tbs, profile
--last_login --awesome 12c feature only
--common --12c only
from dba_users where
upper(username)=upper('&_username');
Prompt
Prompt User Quota
select tablespace_name, bytes/1024/1024/1024 GB_USED_BY_USER, max_bytes/1024/1024/1024 GB_USER_QUOTA
from dba_ts_quotas where
upper(username)=upper('&_username');
Prompt
Prompt User Permissions
break on typ on grantee
select
'ROLE PRIVS' type,
grantee grantee,
granted_role privilege,
admin_option ad,
'--' table_name,
'--' column_name,
'--' owner
from dba_role_privs
where grantee=upper('&_username')
union
select 'SYSTEM PRIVS' type,
grantee grantee,
privilege privilege,
admin_option ad,
'--' table_name,
'--' column_name,
'--' owner
from dba_sys_privs
where grantee=upper('&_username')
union
select 'TABLE PRIVS' type,
grantee grantee,
privilege privilege,
grantable ad,
table_name table_name,
'--' column_name,
owner owner
from dba_tab_privs
where grantee=upper('&_username')
union
select 'COLUMN' type,
grantee grantee,
privilege privilege,
grantable ad,
table_name table_name,
column_name column_name,
owner owner
from dba_col_privs
where grantee=upper('&_username')
order by 1;
Prompt
Prompt Audit information
select * from dba_obj_audit_opts where upper(owner)=upper('&_username') order by object_name,object_type ;
select * from dba_stmt_audit_opts where upper(user_name)=upper('&_username') order by audit_option;
