Oracle active sql , explain plan ,wait , top sql

what sql is currently running
Running sql
Oracle active sql


s.sql

set linesize 200
set pagesize 200
column sid format a12
column username format a10
column terminal format a10
column sql_text format a40 wrap
Select ''''||s.sid||','||s.serial#||'''' SID
,s.status
,s.username
,s.terminal
,q.SQL_TEXT
,q.command_type
,s.sql_id
from
v$session s
,(select distinct
address
,sql_text
,command_type
from v$sql) q
where
q.address=s.sql_address
order by s.sid
/
prompt >>>>>>>>>>>>>>>>>>>>>>>>>>>-- run e.sql for explain plan

alter system kill session 'sid,serial#' IMMEDIATE;

=======
again active session

s1.sql


prompt
prompt Report of Current Active Sessions.

set linesize 150
set pagesize 100
set verify off

column sid_serial format A10 heading 'Sid,Serial'
column osuser format A10 heading 'OS USer'
column user_schema format A12 heading 'User/Schema'
column machine format A35 heading 'Machine/What'
column program format A37 heading 'Program'
column action format a15 heading 'Action'
column s_status format a1 heading 'S'
column wait_event format a30 heading 'Wait Event'
column lockwait format a1 heading 'L'
column sql_text format a50 heading 'sql text'
column unix_pid format a8
select /*+ RULE */ decode (jr.job,NULL,s.osuser,'JOB '||jr.job) osuser
,decode (s.username,s.schemaname,s.username,s.username||'/'||s.schemaname) user_schema,substr(sq.sql_text,1,50) sql_text
,s.sid||','||s.serial# as sid_serial
,p.spid as unix_pid
,substr (s.status,1,1) as s_status
,substr (s.lockwait,1,1) as lockwait
,aa.name as action
-- ,nvl (j.what,s.machine) as machine
,w.event as wait_event
-- ,nvl(s.program,p.program) as program
from v$session s
,sys.dba_jobs_running jr
,sys.dba_jobs j
,v$process p
,v$session_wait w
,sys.audit_actions aa
,sys.v$sql sq
where s.type <> 'BACKGROUND'
and s.status in ('ACTIVE','KILLED')
and nvl(s.program,p.program) not like '% (SNP%)'
and s.paddr = p.addr
and s.sid = jr.sid(+)
and jr.job = j.job(+)
and s.sid = w.sid (+)
and s.command = aa.action (+)
and sq.SQL_ID=s.sql_id
order by machine, s.osuser,s.username,s.program,s.sid
/

output ----

OS USer User/Schema sql text Sid,Serial UNIX_PID S L Action Wait Event
---------- ------------ -------------------------------------------------- ---------- -------- - - --------------- ------------------------------
oracle SYS select /*+ RULE */ decode (jr.job,NULL,s.osuser,'J 53,3705 20231 A SELECT SQL*Net message to client





s3.sql

set linesize 80 pagesize 60
clear columns
column txt format a620 heading 'USERNAME OSUSER SPID TERMINAL PROGRAM STATUS'
select rpad(vs.username,11) || ' ' || rpad(vs.osuser,14) || ' ' || rpad(vp.spid,6) || ' ' || rpad(nvl(vs.terminal,' '),11) || ' ' ||
rpad(nvl(nvl(vs.program,vs.module),' '),25) || ' ' || rpad(vs.status,8) || substr(sa.sql_text,1,560) txt
from v$session vs,v$process vp,v$sqlarea sa
where vs.username is not null
and vp.addr = vs.paddr
and vs.sql_address = sa.address(+)
and vs.sql_hash_value = sa.hash_value(+)
order by 1;
clear columns
set pagesize 24
set echo on

USERNAME OSUSER SPID TERMINAL PROGRAM STATUS
--------------------------------------------------------------------------------
SYS oracle 16765 pts/6 sqlplus@apt-amd-02 (TNS V ACTIVE
select rpad(vs.username,11) || ' ' || rpad(vs.osuser,14) || ' ' || rpad(vp.spi
d,6) || ' ' || rpad(nvl(vs.terminal,' '),11) || ' ' || rpad(nvl(nvl(vs.progra
m,vs.module),' '),25) || ' ' || rpad(vs.status,8) || substr(sa.sql_text,1,560)
txt from v$session vs,v$process vp,v$sqlarea sa where vs.username is not nu
ll and vp.addr = vs.paddr and vs.sql_address = sa.address(+) and vs.sql_ha
sh_value = sa.hash_value(+) order by 1

SYS oracle 17448 pts/17 sqlplus@apt-amd-02 (TNS V INACTIVE





p2.sql

set linesize 80 pagesize 60 recsep off timing off
clear columns
column username format a11
column osuser format a13
column "PROGRAM OR BANNER FORM" format a22 trunc
column "RunSec" format a6
column spid format a7
column terminal format a10
column server format a12
select vs.username,vs.osuser,vp.spid,vs.terminal, vs.module "PROGRAM OR BANNER FORM",to_char(vs.logon_time,'HH24:MI') "LOGON",
substr(to_char(value/100,'999.99'),2) "RunSec"
from v$session vs,v$process vp,v$sesstat st
where vs.username is not null
and vp.addr = vs.paddr
and vs.sid = st.sid
and statistic# = 12
order by vs.username;
select v1.terminal "SERVER",sessions,active "ACTIVE FORMS" from
(select terminal,count(*) sessions from v$session
where terminal is not null and osuser = 'SYSTEM'
group by terminal) v1,
(select terminal,count(*) active from v$session
where terminal is not null and osuser = 'SYSTEM'
and module != 'GUAGMNU' and length(module) = 7
group by terminal) v2
where v1.terminal = v2.terminal(+);
clear columns
set pagesize 24 timing on
set echo on showmode both


USERNAME OSUSER SPID TERMINAL PROGRAM OR BANNER FORM LOGON RunSec
----------- ------------- ------- ---------- ---------------------- ----- ------
SYS oracle 17448 pts/17 sqlplus@apt-amd-02 (TN 08:47 .00
SYS oracle 16765 pts/6 sqlplus@apt-amd-02 (TN 16:03 .00

==========


currently connected to the database and waits

set pagesize 50000;
set linesize 320;
set heading on;
set verify off;
set trimspool on;
clear screen;

column date_of_run format a30;
column open_time format a30;
column block_size_bytes format a20;

column ousername format a10 heading 'Oracle|User ID' justify left
column oosuser format a10 heading 'OS|User ID' justify left
column omachine format a10 heading 'Machine ID' justify left
column uspid format 999999 heading 'UNIX|Process|ID' justify right
column oserial# format 999999 heading 'Oracle|Serial|No' justify right
column osid format 999999 heading 'Oracle|Session|ID' justify right
column oprogram format a50 heading 'Program' justify left
column qsqltxt format a100 heading 'Current|Statement' justify left word
column ostatus format a8 heading 'Session|Status' justify left
column qrows format 999999 heading 'Rows|Processed' justify right
column nname format a30 heading 'Statistic|Name' justify left
column svalue format 9999999999 heading 'Statistic|Value' justify right
column avg_lastd format 9999999999 heading 'Average|Last Day' justify right
column lst_exect format 9999999999 heading 'Last|Eexec Time' justify right
col qsqltxt format a50
break on ousername on oosuser on omachine on uspid on oserial# on osid on oprogram on qsqltxt on ostatus on qrows noduplicates


prompt
prompt *******************************************************
prompt IF THIS SCRIPT DOES NOT RETURN ANYTHING, RUN IT AGAIN.
prompt THE TARGET QUERY MAY NOT BE RUNNING THE MOMENT YOU TRY.
prompt *******************************************************

PROMPT
ACCEPT KNOWN_PARAMETER_NAME CHAR PROMPT 'ENTER PARAMETER NAME [1]: ' DEFAULT 1
ACCEPT KNOWN_PARAMETER_VALUE CHAR PROMPT 'ENTER PARAMETER VALUE [1]: ' DEFAULT 1
PROMPT

select d.name DATABASE,
to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN,
to_char(open_time, 'DD-MON-YYYY HH24:MI:SS') OPEN_TIME,
value BLOCK_SIZE_BYTES
from v$database d,
v$parameter p,
v$thread t
where p.name = 'db_block_size';

select
o.username ousername
,o.osuser oosuser
,o.machine omachine
,lpad(u.spid,7) uspid
,o.serial# oserial#
,o.sid osid
-- ,o.program oprogram
-- ,substr(q.sql_text,1,50) qsqltxt
,q.sql_id
,o.status ostatus
,q.rows_processed qrows
,n.name nname
,s.value svalue
from
v$process u
,v$session o
,v$sqlarea q
,v$sesstat s
,v$statname n
where
u.addr=o.paddr
and o.sql_address=q.address(+)
and o.sql_hash_value=q.hash_value(+)
and o.sid=s.sid
and n.statistic#=s.statistic#
and n.name in (
'CPU used by this session'
,'physical reads'
,'physical writes'
,'session pga memory max'
,'session uga memory max'
)
and &KNOWN_PARAMETER_NAME=&KNOWN_PARAMETER_VALUE
order by 1,2,3;



select sql_text from v$sqlarea where sql_id='&sql_id';
Enter value for sql_id: ggzz1vwf6rg79



============
for explain plan
e.sql

set pagesize 32000
set linesize 200
column sid format a10
column username format a10
column terminal format a10
column sql_text format a90 wrap
column OPERATION format a30
column OBJECT format a30
break on SID
Select ''''||s.sid||','||s.serial#||'''' SID,
CASE
WHEN (E.OPERATION like '%STATEMENT') then Q.SQL_TEXT
else LPAD(' ',E.DEPTH*2,' ')||RTRIM(REPLACE(E.OPERATION||'/'||E.OPTIONS||'/'||E.OBJECT_OWNER||'.'||E.OBJECT_NAME,'//','/')||'/'||to_char(E.PARTITION_START)||'-'||to_char(E.PARTITION_STOP),'/-.')
END SQL_TEXT
--,E.OBJECT_NODE
--,E.OPTIMIZER
--,E.ID
--,E.PARENT_ID
--,E.DEPTH
--,E.COST
--,E.CARDINALITY
--,E.BYTES
--,E.OTHER_TAG
--,E.PARTITION_START
--,E.PARTITION_STOP
--,E.PARTITION_ID
--,E.OTHER
--,E.DISTRIBUTION
,E.CPU_COST
,E.IO_COST
From v$sql_plan E, v$sql Q, V$SESSION S
Where E.address=Q.address(+)
and E.hash_value=Q.hash_value(+)
and E.child_number=Q.child_number(+)
and E.address=S.sql_address
order by s.sid,e.ID
/

prompt >>>>>>>>>>>>>>>>>>> w.sql for long running processes


e1.sql

set head off
set lines 200
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

el.sql

-- execution plan of the last executed statement for that session
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

or

set head off
set lines 200
select * from TABLE(dbms_xplan.display_cursor(null, null, 'ALL ALLSTATS LAST'));




ew.sql

set linesize 120
set pagesize 500
select * from TABLE(dbms_xplan.display_awr('&sql_id'));



====
for Wait info

-bash-3.00$ cat w.sql

col p1 format a10
col p2 format a10
col p3 format a10
col USERNAME format a15
col SERIAL format a10
col sid format a10
col STATUS format a10
col MESSAGE format a70

set linesize 200
set pagesize 200
column sid format 9999
column DONE_PCT format a8
column MINUTES_REM format 99999
select SID,to_char(ROUND((SOFAR*100)/TOTALWORK,2))||' %' DONE_PCT
,trunc(TIME_REMAINING/60) MINUTES_REM
,trunc(ELAPSED_SECONDS/60) MINUTES_ELAPSED
,MESSAGE
from V$SESSION_LONGOPS
order by start_time
/


FOR Process
p.sql


set feedback off
set pause off
set pagesize 22
set linesize 100
ttitle center 'TRANSACTION STATUS' skip 5
btitle center 'DBA SCRIPTS'
col START_TIME format a20 heading 'START TIME'
col USERNAME format a14 heading USER
col NAME format a15 heading ACTION
col USED_UBLK format 9,999,999,999 heading 'ROLLBACK BLOCKS'
col USED_UREC format 9,999,999,999 heading 'ROLLBACK RECORDS'
col SPID format a12 heading 'UNIX PROCESS'
select
to_char(to_date(a.START_TIME,'MM/DD/YY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') START_TIME,
b.USERNAME,
c.NAME,
a.USED_UBLK,
a.USED_UREC,
d.SPID
from
V$TRANSACTION a,
V$SESSION b,
SYS.AUDIT_ACTIONS c,
V$PROCESS d
where
a.SES_ADDR=b.SADDR and
b.COMMAND=c.ACTION and
d.ADDR=b.PADDR
order
by 1,2
/
ttitle off
btitle off
set feedback on
set pause on
============
top sql

t.sql

set trimspool on
--set linesize 300
set linesize 200
set verify off
set trimspool on
set feedback off

spool dba_topsql.log

prompt
prompt Report of Possible SQL to Tune.

column gets_per_exe format 999,999,990
column disk_reads format 999,999,990
column buffer_gets format 999,999,990
column executions format 999,999,990
column sorts format 999,990
column hash_value format 99999999990
column sql_id format a14
column sql_text format a50 word_wrapped
column username format a13
column cpu_time format 999,990.00 heading 'CPU(s)'
column elapsed_time format 999,990.00 heading 'Elapsed(s)'

select u.username
, v.disk_reads
, v.buffer_gets
, v.executions
-- , v.sorts
, v.buffer_gets/v.executions as gets_per_exe
, (v.cpu_time/1000000) as cpu_time
, (v.elapsed_time/1000000) as elapsed_time
-- , v.address
-- , v.hash_value
, v.sql_id
, substr (v.sql_text,1,70) as sql_text
-- , a.name as action_type
from v$sqlarea v
, dba_users u
, audit_actions a
where v.parsing_schema_id = u.user_id
and v.command_type = a.action
-- and u.username not in ('SYS','SYSTEM')
and v.buffer_gets >=
( select min (v2.buffer_gets)
from
( select v3.buffer_gets
from v$sqlarea v3
order by buffer_gets desc
) v2
where rownum < 20
)
order by v.buffer_gets desc
/
spool off
set feedback 6

=============
for wait info
-bash-3.00$ cat w1.sql

SET LINESIZE 250
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20

SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
d.spid AS process_id,
a.wait_class,
a.seconds_in_wait,
a.state,
a.blocking_session,
a.blocking_session_status,
a.module,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
AND a.status = 'ACTIVE'
ORDER BY 1,2;

SET PAGESIZE 14

==

hplan.sql

undefine PLAN_HASH_VALUE
-- accept PLAN_HASH_VALUE prompt 'Please enter PLAN_HASH_VALUE to show Statistics for: '
accept sql_id prompt 'Please enter sql_id to show Statistics for: '
col iowait_delta format 9999999.99 heading iowaitdelta(ms)
col iowait_total format 9999999.99 heading iowaittotal(ms)
col ELAPSED_TIME_TOTAL format 9999999.99 heading elapsdtimetotal(ms)
col ELAPSED_TIME_DELTA format 9999999.99 heading elapsdtimedelta(ms)
col PLAN_HASH_VALUE heading plan_hashvalue
col CONCURRENCY_WAIT_TOTAL format 9999999.99 heading concwaittotal(ms)
col CONCURRENCY_WAIT_delta format 9999999.99 heading concwaitdelta(ms)
col CLUSTER_WAIT_DELTA format 9999999.99 heading clustwaitdelta(ms)
col CLUSTER_WAIT_TOTAL format 9999999.99 heading clustwaittotal(ms)
col APWAIT_TOTAL format 9999 heading applwaittimetotal(micro)
col APWAIT_DELTA format 9999 heading applwaittimedelta(micro)
col PLSEXEC_TIME_TOTAL format 9999 heading plsqlexectimetotal(micro)
col PLSEXEC_TIME_DELTA format 9999 heading plsqlexectimedelta(micro)
col JAVAEXEC_TIME_DELTA format 9999 heading javaexectimedelta(micro)
col JAVAEXEC_TIME_TOTAL format 9999 heading javaexectimetotal(micro)
col optimizer_cost format 9999 heading optcostcol optimizer_mode format a10 heading optimmode
col kept_versions format 999 heading keptvers
col invalidations_total format 999 heading invalidtot
col invalidations_delta format 999 heading invaliddlt
col parse_calls_total format 99999 heading parsecallstotal
col parse_calls_delta format 99999 heading parsecallsdelta
col executions_total format 999999 heading exectotal
col executions_delta format 999999 heading execdelta
col fetches_total format 9999999 heading fetchestotal
col fetches_delta format 9999999 heading fetchesdelta
col end_of_fetch_count_total format 9999 heading endoffetchcalltotal
col end_of_fetch_count_delta format 9999 heading endoffetchcalldelta
col buffer_gets_total format 99999999 heading buffergetstotal
col buffer_gets_delta format 99999999 heading buffergetsdelta
col disk_reads_total format 999999 heading diskreadstotal
col disk_reads_delta format 9999999 heading diskreadsdelta
col rows_processed_total format 9999999 heading rowsprocessedtotal
col rows_processed_delta format 9999999 heading rowsprocesseddelta
col rows_ex format 999999 heading rowsexeccol snap_id format 99999 heading snapid
col ela_ex format 9999999.99 heading elapsedperexecution
col cwt_ex format 9999999.99 heading cwtperexecution
col instance_number format 99 heading inID

select sql_id, plan_hash_value,dba_hist_sqlstat.snap_id,
to_char(dba_hist_snapshot.BEGIN_INTERVAL_TIME,'dd-mm_hh24:mi') snap_beg,dba_hist_sqlstat.instance_number,invalidations_delta,
parse_calls_delta,executions_delta,fetches_delta,buffer_gets_delta,
disk_reads_delta,rows_processed_delta,elapsed_time_delta/1000 elapsed_time_delta,iowait_delta/1000 iowait_delta,clwait_delta/1000 cluster_wait_delta,ccwait_delta/1000 concurrency_wait_delta,optimizer_mode, optimizer_cost,
substr(optimizer_mode,1,3) opt,
case when executions_delta = 0 then NULL
when rows_processed_delta = 0 then NULL
else(rows_processed_delta/executions_delta) end rows_ex,
case when executions_delta = 0 then NULL
when clwait_delta = 0 then NULL
else(clwait_delta/executions_delta)/1000 end cwt_ex,
case when executions_delta = 0 then NULL
when elapsed_time_delta = 0 then NULL
else(elapsed_time_delta/executions_delta)/1000 end ela_ex
from dba_hist_sqlstat, dba_hist_snapshot
where sql_id='&sql_id'
-- and plan_hash_value='and PLAN_HASH_VALUE'
and dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id
and dba_hist_sqlstat.instance_number=dba_hist_snapshot.instance_number
order by dba_hist_sqlstat.instance_number,dba_hist_sqlstat.snap_id
/

Oracle Used space in Meg by segment type

set pagesize 50000
set line 80

col "Total Used Meg" format 999,999,990
col "Data part" format 999,999,990
col "Index part" format 999,999,990
col "LOB part" format 999,999,990
col "RBS part" format 999,999,990
tti 'Used space in Meg by segment type'

select sum(bytes)/1024/1024 "Total Used",sum( decode( substr(segment_type,1,5), 'TABLE', bytes/1024/1024, 0)) "Data part",
sum( decode( substr(segment_type,1,5), 'INDEX', bytes/1024/1024, 0)) "Index part",
sum( decode( substr(segment_type,1,3), 'LOB', bytes/1024/1024, 0)) "LOB part",
sum( decode(segment_type,'ROLLBACK', bytes/1024/1024, 0)) "RBS part",
sum( decode(segment_type,'TEMPORARY', bytes/1024/1024, 0)) "TEMP part"
from sys.dba_segments
/

tti off

tti "Total database size"

select sum(bytes)/1024/1024 "Total DB size in Meg" from sys.v_$datafile
/
tti off



SQL> @used_space.sql

Tue Sep 13 page 1
Used space in Meg by segment type

Total Used Data part Index part LOB part RBS part TEMP part
---------- ------------ ------------ ------------ ------------ ----------
2060.75 1,218 490 274 0 0


Tue Sep 13 page 1
Total database size

Total DB size in Meg
--------------------
2728.75

Oracle object are accessed

col object for A34
col owner for A16 trunc
col type for A12 head Obj|type
col accessed for 9999 head "Accessed|by # users"

select A.owner,A.object,A.TYPE,count(*) accessed
from v$ACCESS A
where A.owner not in ('SYS','SYSTEM')
and A.TYPE = 'TABLE'
group by A.owner,A.object,A.TYPE
order by accessed
/



1 select A.owner,A.object,A.TYPE,count(*) accessed
2 from v$ACCESS A
3 where 1=1
4 -- and A.owner not in ('SYS','SYSTEM')
5 and A.TYPE = 'TABLE'
6 group by A.owner,A.object,A.TYPE
7* order by accessed
SQL> /

Obj Accessed
OWNER OBJECT type by # users
---------------- ---------------------------------- ------------ ----------
SYSTEM AQ$_QUEUE_TABLES TABLE 1
SYS EXPACT$ TABLE 1
SCHEDULER$_LIGHTWEIGHT_JOB TABLE 1
WRI$_ADV_DEF_PARAMETERS TABLE 1
WRI$_SQLSET_STATEMENTS TABLE 1
IDL_UB2$ TABLE 1
COL_USAGE$ TABLE 1
WRI$_ADV_REC_ACTIONS TABLE 1
KOTAD$ TABLE 1
DAM_CONFIG_PARAM$ TABLE 1
WRI$_ADV_TASKS TABLE 1
IDL_UB1$ TABLE 1
WRI$_ADV_DEFINITIONS TABLE 1
WRI$_ADV_ACTIONS TABLE 1

Oracle users' table and index on tablespace

set pagesize 60
set echo off
spool users_obj_tab.txt
column tablespace_name format a20
column owner format a20
column objects format a20
break on owner on tablespace_name
select substr(owner,1,20) owner,substr(tablespace_name,1,32) tablespace_name,count(*)||decode(count(*),1,' table',' tables') objects
from sys.dba_tables
group by substr(owner,1,20),substr(tablespace_name,1,32)
union
select substr(owner,1,20) owner,substr(tablespace_name,1,32) tablespace_name, count(*)||decode(count(*),1,' index',' indexes') objects
from sys.dba_indexes
group by substr(owner,1,20),substr(tablespace_name,1,32)
/
spool off




OWNER TABLESPACE_NAME OBJECTS
-------------------- -------------------- --------------------
ABC USERS 1 table
ANUJ USERS 2 indexes
4 tables
ANUJREP USERS 1 index
1 table
ANUJTEST ANUJTEST 1 table
APEX_030200 SYSAUX 1101 indexes
356 tables
4 tables
APEX_040000 TSAPEXU 1362 indexes
422 tables
4 tables
APPQOSSYS SYSAUX 2 tables
CTXSYS SYSAUX 34 tables
58 indexes
13 tables
DBSNMP SYSAUX 11 indexes
21 tables
2 indexes
4 tables
EXFSYS SYSAUX 20 tables
40 indexes
1 index
27 tables

Oracle table PRIVILEGE info

tab_priv.sql


TABLE PRIVILEGE
TABLE PRIVILEGES
tab priv

==================================

-- TABLE_PRIVILEGES

-- Grants on objects for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee:





Accept owner prompt 'Enter owner Name or all..'
col grantee format a15
col owner format a15
col table_name format a25
col grantor format a15
col select_priv format a1 Heading 's|e|l'
col insert_priv format a1 Heading 'i|n|s'
col delete_priv format a1 Heading 'D|E|L'
col update_priv format a1 Heading 'U|P|D'
col references_priv format a1 Heading 'R|E|F'
col alter_priv format a1 Heading 'A|l|t'
col index_priv format a1 heading 'I|N|D'
col created format a11 heading 'Grnted on:'
break on owner skip 4 on table_name skip 1 on report
set linesize 130
btitle skip 1 center-
'Y=granted,N=not Granted,G=granted with grant option,'-
's=Granted on specific column,A=granted on A11 olumns'-

select owner,TABLE_NAME,GRANTEE,GRANTOR,CREATED,SELECT_PRIV,INSERT_PRIV,DELETE_PRIV,UPDATE_PRIV,REFERENCES_PRIV,ALTER_PRIV,INDEX_PRIV
from TABLE_PRIVILEGES
where owner not in ('SYS')
and owner=upper('&owner')
order by owner,table_name,grantor,grantee
/



SQL> @tab_priv
Enter owner Name or all..SYSTEM
old 4: and owner=upper('&owner')
new 4: and owner=upper('SYSTEM')

s i D U R A I
e n E P E l N
OWNER TABLE_NAME GRANTEE GRANTOR Grnted on: l s L D F t D
--------------- ------------------------- --------------- --------------- ----------- - - - - - - -
SYSTEM DEF$_AQCALL SYS SYSTEM G N N N N N N

DEF$_CALLDEST SYS SYSTEM G N N N N N N

DEF$_DESTINATION SYS SYSTEM G N N N N N N

DEF$_ERROR SYS SYSTEM G N N N N N N

HELP PUBLIC SYSTEM Y N N N N N N

MVIEW$_ADV_INDEX PUBLIC SYSTEM Y A Y A N N N

MVIEW$_ADV_OWB PUBLIC SYSTEM Y A Y A N N N

MVIEW$_ADV_PARTITION PUBLIC SYSTEM Y A Y A N N N

OL$ PUBLIC SYSTEM Y A Y A N N N

OL$HINTS PUBLIC SYSTEM Y A Y A N N N

OL$NODES PUBLIC SYSTEM Y A Y A N N N

PRODUCT_PRIVS PUBLIC SYSTEM Y N N N N N N

REPCAT$_REPPROP SYS SYSTEM G N N N N N N

REPCAT$_REPSCHEMA SYS SYSTEM G N N N N N N



Y=granted,N=not Granted,G=granted with grant option,s=Granted on specific column,A=granted on A11 olumns

14 rows selected.



SELECT grantor, table_name, privilege FROM user_tab_privs_recd;
/

set linesize 121
col select_priv format a10
col insert_priv format a10
col update_priv format a10
col delete_priv format a10

SELECT table_name, grantee,
MAX(DECODE(privilege, 'SELECT', 'SELECT')) AS select_priv,
MAX(DECODE(privilege, 'DELETE', 'DELETE')) AS delete_priv,
MAX(DECODE(privilege, 'UPDATE', 'UPDATE')) AS update_priv,
MAX(DECODE(privilege, 'INSERT', 'INSERT')) AS insert_priv
FROM dba_tab_privs
WHERE grantee IN ( SELECT role FROM dba_roles)
GROUP BY table_name, grantee;