Oracle Role info

role info



from web ,,,,,


SET LINESIZE 300 PAGESIZE 50000 FEEDBACK OFF ECHO OFF VERIFY OFF SHOWMODE OFF TRIMSPOOL ON
COLUMN grantee_name FORMAT A30 HEADING 'User Name'
COLUMN role_path FORMAT A50 HEADING 'Role Hierarchy'
COLUMN priv_obj_name FORMAT A60 HEADING 'Privilege/Object Name'
COLUMN sort_order1 NOPRINT
COLUMN priv_path_names NOPRINT
COLUMN insert_priv FORMAT A2 HEADING 'IN'
COLUMN update_priv FORMAT A2 HEADING 'UP'
COLUMN delete_priv FORMAT A2 HEADING 'DE'
COLUMN select_priv FORMAT A2 HEADING 'SE'
COLUMN alter_priv FORMAT A2 HEADING 'AL'
COLUMN execute_priv FORMAT A2 HEADING 'EX'
COLUMN other_priv FORMAT A2 HEADING 'OT'


BREAK ON grantee_name NODUPLICATE SKIP 1 ON role_path NODUPLICATE

SELECT 1 sort_order1,
urm.priv_path_names priv_path_names,
CASE WHEN urm.root_user_id != urm.priv_path THEN SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')-1)
ELSE urm.priv_path_names
END grantee_name,
CASE WHEN urm.root_user_id = urm.priv_path THEN 'Direct'
ELSE 'Role '||REPLACE(urm.priv_path_names, SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')))
END role_path,
spm.name priv_obj_name,
NULL insert_priv,
NULL update_priv,
NULL delete_priv,
NULL select_priv,
NULL alter_priv,
NULL execute_priv,
NULL other_priv
FROM system_privilege_map spm,
sysauth$ sa,
( SELECT CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, 1, INSTR(urm2.priv_path, '.')-1)
ELSE urm2.priv_path
END root_user_id,
CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, INSTR(urm2.priv_path, '.', -1)+1)
ELSE urm2.priv_path
END role_id,
urm2.priv_path,
urm2.priv_path_names
FROM ( SELECT TO_CHAR(u.user#) priv_path,
u.name priv_path_names
FROM user$ u
UNION
SELECT sam.priv_path priv_path,
sam.priv_path_names priv_path_names
FROM (SELECT sa.grantee#,
sa.privilege#,
LTRIM(SYS_CONNECT_BY_PATH(sa.grantee#, '.'), '.')||'.'||sa.privilege# priv_path,
LTRIM(SYS_CONNECT_BY_PATH(u1.name, '.'), '.')||'.'||u2.name priv_path_names
FROM sysauth$ sa,
user$ u1,
user$ u2
WHERE sa.privilege# > 0
AND u1.user# = sa.grantee#
AND u2.user# = sa.privilege#
CONNECT BY sa.grantee# = PRIOR sa.privilege#
ORDER SIBLINGS BY sa.privilege#
) sam
) urm2
) urm
WHERE urm.root_user_id IN ( SELECT usq.user#
FROM user$ usq
WHERE usq.type# = 1
AND usq.name NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','CTXSYS','PERFSTAT')
)
AND sa.grantee# = urm.role_id
AND sa.privilege# < 0
AND sa.privilege# = spm.privilege
UNION
SELECT 2 sort_order1,
urm.priv_path_names priv_path_names,
CASE WHEN urm.root_user_id != urm.priv_path THEN SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')-1)
ELSE urm.priv_path_names
END grantee_name,
CASE WHEN urm.root_user_id = urm.priv_path THEN 'Direct'
ELSE 'Role '||REPLACE(urm.priv_path_names, SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')))
END role_path,
u.name||'.'||o.name priv_obj_name,
MAX(DECODE(tpm.name, 'INSERT', 'X', NULL)) insert_priv,
MAX(DECODE(tpm.name, 'UPDATE', 'X', NULL)) update_priv,
MAX(DECODE(tpm.name, 'DELETE', 'X', NULL)) delete_priv,
MAX(DECODE(tpm.name, 'SELECT', 'X', NULL)) select_priv,
MAX(DECODE(tpm.name, 'ALTER', 'X', NULL)) alter_priv,
MAX(DECODE(tpm.name, 'EXECUTE', 'X', NULL)) execute_priv,
MAX(DECODE(tpm.name, 'INSERT', NULL, 'UPDATE', NULL, 'DELETE', NULL,
'SELECT', NULL, 'ALTER', NULL, 'EXECUTE', NULL,
'X' )) other_priv
FROM objauth$ oa,
obj$ o,
user$ u,
table_privilege_map tpm,
( SELECT CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, 1, INSTR(urm2.priv_path, '.')-1)
ELSE urm2.priv_path
END root_user_id,
CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, INSTR(urm2.priv_path, '.', -1)+1)
ELSE urm2.priv_path
END role_id,
urm2.priv_path,
urm2.priv_path_names
FROM ( SELECT TO_CHAR(u.user#) priv_path,
u.name priv_path_names
FROM user$ u
UNION
SELECT sam.priv_path priv_path,
sam.priv_path_names priv_path_names
FROM (SELECT sa.grantee#,
sa.privilege#,
LTRIM(SYS_CONNECT_BY_PATH(sa.grantee#, '.'), '.')||'.'||sa.privilege# priv_path,
LTRIM(SYS_CONNECT_BY_PATH(u1.name, '.'), '.')||'.'||u2.name priv_path_names
FROM sysauth$ sa,
user$ u1,
user$ u2
WHERE sa.privilege# > 0
AND u1.user# = sa.grantee#
AND u2.user# = sa.privilege#
CONNECT BY sa.grantee# = PRIOR sa.privilege#
ORDER SIBLINGS BY sa.privilege#
) sam
) urm2
) urm
WHERE urm.root_user_id IN ( SELECT usq.user#
FROM user$ usq
WHERE usq.type# = 1
AND usq.name NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','CTXSYS','PERFSTAT')
)
AND oa.grantee# = urm.role_id
AND oa.obj# = o.obj#
AND o.owner# != 59
AND o.owner# = u.user#
AND oa.privilege# = tpm.privilege
GROUP BY 2, urm.root_user_id, urm.priv_path, urm.priv_path_names, u.name, o.name
ORDER BY priv_path_names, sort_order1, priv_obj_name


SPOOL allprivs.lst

/



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




XYZ Direct UNLIMITED TABLESPACE
ABC.X X
Role CONNECT CREATE SESSION
Role RESOURCE CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE

Oracle Response Time Components

Oracle wait event
Oracle wait


@response.sql

SET LINESIZE 132 PAGESIZE 100 FEEDBACK OFF
COLUMN time_sum NOPRINT
COLUMN total_time NOPRINT
COLUMN wait_class FORMAT A15 HEADING 'Class'
COLUMN event FORMAT A40 HEADING 'Event'
COLUMN wait_seconds FORMAT 999,999,999,999.99 HEADING 'Wait Seconds'
COLUMN wait_pct FORMAT 99.999999 HEADING '% of T' NOPRINT
COLUMN nonidle_pct FORMAT 99.999999 HEADING '% of R'

BREAK ON wait_class SKIP PAGE
COMPUTE SUM OF wait_seconds nonidle_pct ON wait_class

TTITLE LEFT "*** Response Time Components by Class ***"

SELECT vswc.time_waited time_sum,
ven.wait_class,
vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
instime.time_waited total_time,
(ROUND((vse.time_waited_micro/1000000),6)/(instime.time_waited/100))*100 wait_pct,
TO_NUMBER(DECODE(vswc.wait_class, 'Idle', NULL, 'Network', NULL,
(ROUND((vse.time_waited_micro/1000000),6)/
((instime.time_waited - idletime.time_waited)/100))*100)) nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
UNION
SELECT vss.value time_sum,
'CPU Utilization' wait_class,
NULL,
vss.value/100 wait_seconds,
instime.time_waited total_time,
(ROUND((vss.value/100),6)/(instime.time_waited/100))*100 wait_pct,
((ROUND((vss.value/100),6)/((instime.time_waited - idletime.time_waited)/100))*100) nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE vss.name = 'CPU used when call started'
ORDER BY time_sum, wait_class, wait_seconds DESC
/

CLEAR BREAK
CLEAR COMPUTE

TTITLE LEFT "*** Response Time Components by Time ***"

SELECT vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
(ROUND((vse.time_waited_micro/1000000),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
AND vswc.wait_class NOT IN ('Idle', 'Network')
UNION
SELECT 'CPU Utilization' wait_class,
vss.value/100 wait_seconds,
(ROUND((vss.value/100),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE vss.name = 'CPU used when call started'
ORDER BY wait_seconds DESC
/












SQL> @responce

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Network SQL*Net more data from client .03
SQL*Net more data to client .01
SQL*Net message to client .01
*************** ------------------- ----------
sum .05

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Configuration checkpoint completed .43 .013184
log buffer space .10 .003156
undo segment extension .02 .000676
enq: SQ - contention .00 .000024
latch: redo writing .00 .000004
*************** ------------------- ----------
sum .56 .017044

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Administrative JS coord start wait .86 .026148
JS kgl get object wait .10 .003051
*************** ------------------- ----------
sum .96 .029199

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Commit log file sync 39.11 1.192503
*************** ------------------- ----------
sum 39.11 1.192503

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Other job scheduler coordinator slave wait 14.21 .433321
enq: CR - block range reuse ckpt 4.26 .129956
control file heartbeat 4.00 .121970
enq: CF - contention 2.95 .090048
ADR block file read 2.47 .075283
rdbms ipc reply 2.24 .068444
ARCH wait for process start 3 2.00 .061011
ADR block file write 1.84 .056206
enq: JS - queue lock .34 .010249
reliable message .17 .005181
latch: enqueue hash chains .17 .005119
kksfbc child completion .10 .003079
asynch descriptor resize .09 .002878
LGWR wait for redo copy .08 .002580
ADR file lock .08 .002286
SGA: allocation forcing component growth .07 .002012
enq: PR - contention .05 .001598
CRS call completion .04 .001141
latch: call allocation .02 .000728
Streams AQ: qmn coordinator waiting for .01 .000354
slave to start

latch free .01 .000204
latch: object queue header operation .01 .000200
instance state change .00 .000098
latch: cache buffers lru chain .00 .000030
latch: redo allocation .00 .000005
ARCH wait for archivelog lock .00 .000004
*************** ------------------- ----------
sum 35.22 1.073983

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Concurrency os thread startup 91.94 2.803294
library cache load lock 9.20 .280573
latch: shared pool 3.48 .106173
cursor: pin S wait on X 1.18 .035971
row cache lock .33 .009924
resmgr:internal state change .10 .003065
buffer busy waits .01 .000277
latch: row cache objects .00 .000131
latch: cache buffers chains .00 .000042
library cache: mutex X .00 .000036
*************** ------------------- ----------
sum 106.24 3.239486

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Scheduler resmgr:cpu quantum 121.26 3.697244
*************** ------------------- ----------
sum 121.26 3.697244

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
CPU Utilization 145.04 4.422423
*************** ------------------- ----------
sum 145.04 4.422423

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
User I/O db file sequential read 316.38 9.646716
db file scattered read 61.88 1.886868
Data file init write 31.37 .956459
local write wait 4.91 .149644
db file parallel read 3.21 .097827
read by other session 2.78 .084886
utl_file I/O 2.10 .064176
Disk file operations I/O 1.86 .056811
Parameter File I/O 1.58 .048154
direct path sync .86 .026246
db file single write .60 .018347
direct path read .34 .010384
Disk file Mirror/Media Repair Write .15 .004546
direct path write .05 .001602
direct path write temp .01 .000394
*************** ------------------- ----------
sum 428.09 13.053059

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Application enq: TX - row lock contention 490.69 14.961517
SQL*Net break/reset to client .73 .022267
enq: RO - fast object reuse .10 .002963
*************** ------------------- ----------
sum 491.51 14.986747

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
System I/O control file parallel write 1,144.43 34.894774
db file async I/O submit 427.99 13.049917
log file parallel write 254.10 7.747749
log file sequential read 58.29 1.777309
control file sequential read 6.41 .195364
Log archive I/O 1.26 .038356
recovery read .34 .010387
RMAN backup & recovery I/O .16 .004989
log file single write .12 .003611
control file single write .02 .000723
*************** ------------------- ----------
sum 1,893.12 57.723180

*** Response Time Components by Class ***
Class Event Wait Seconds % of R
--------------- ---------------------------------------- ------------------- ----------
Idle rdbms ipc message 1,665,247.86
DIAG idle wait 208,746.17
SQL*Net message from client 104,462.03
Streams AQ: qmn slave idle wait 104,419.46
pmon timer 104,385.85
shared server idle wait 104,382.54
Streams AQ: qmn coordinator idle wait 104,371.62
dispatcher timer 104,356.51
Streams AQ: waiting for time management 104,325.21
or cleanup tasks

smon timer 104,168.34
Space Manager: slave idle wait 103,983.65
jobq slave wait 35,947.71
VKRM Idle 14,399.80
class slave wait 299.96
PL/SQL lock timer 2.00
*************** ------------------- ----------
sum 2,863,498.73

*** Response Time Components by Time ***
Event Wait Seconds % of R
---------------------------------------- ------------------- ----------
control file parallel write 1,144.43 34.894242
enq: TX - row lock contention 490.69 14.961289
db file async I/O submit 427.99 13.049718
db file sequential read 316.38 9.646569
log file parallel write 254.10 7.747631
CPU Utilization 145.09 4.423880
resmgr:cpu quantum 121.26 3.697187
os thread startup 91.94 2.803251
db file scattered read 61.88 1.886839
log file sequential read 58.29 1.777282
log file sync 39.11 1.192485
Data file init write 31.37 .956444
job scheduler coordinator slave wait 14.21 .433315
library cache load lock 9.20 .280569
control file sequential read 6.41 .195361
local write wait 4.91 .149641
enq: CR - block range reuse ckpt 4.26 .129954
control file heartbeat 4.00 .121968
latch: shared pool 3.48 .106171
db file parallel read 3.21 .097825
enq: CF - contention 2.95 .090046
read by other session 2.78 .084885
ADR block file read 2.47 .075282
rdbms ipc reply 2.24 .068443
utl_file I/O 2.10 .064175
ARCH wait for process start 3 2.00 .061010
Disk file operations I/O 1.86 .056810
ADR block file write 1.84 .056205
Parameter File I/O 1.58 .048153
Log archive I/O 1.26 .038356
cursor: pin S wait on X 1.18 .035971
direct path sync .86 .026246
JS coord start wait .86 .026147
SQL*Net break/reset to client .73 .022267
db file single write .60 .018347
checkpoint completed .43 .013183
recovery read .34 .010387
direct path read .34 .010383
enq: JS - queue lock .34 .010249
row cache lock .33 .009923
reliable message .17 .005181
latch: enqueue hash chains .17 .005119
RMAN backup & recovery I/O .16 .004989
Disk file Mirror/Media Repair Write .15 .004546
log file single write .12 .003611
log buffer space .10 .003156
kksfbc child completion .10 .003079
resmgr:internal state change .10 .003065
JS kgl get object wait .10 .003051
enq: RO - fast object reuse .10 .002963
asynch descriptor resize .09 .002878
LGWR wait for redo copy .08 .002580
ADR file lock .08 .002286
SGA: allocation forcing component growth .07 .002011
direct path write .05 .001602
enq: PR - contention .05 .001598
CRS call completion .04 .001141
latch: call allocation .02 .000728
control file single write .02 .000723
undo segment extension .02 .000676
direct path write temp .01 .000394
Streams AQ: qmn coordinator waiting for .01 .000354
slave to start

buffer busy waits .01 .000277
latch free .01 .000204
latch: object queue header operation .01 .000200
latch: row cache objects .00 .000131
instance state change .00 .000098
latch: cache buffers chains .00 .000042
library cache: mutex X .00 .000036
latch: cache buffers lru chain .00 .000030
enq: SQ - contention .00 .000024
latch: redo allocation .00 .000005
ARCH wait for archivelog lock .00 .000004
latch: redo writing .00 .000004

Oracle Role info

Oracle role list




CREATE OR REPLACE PROCEDURE LIST_USER_ROLE_PRIVS(UNAME VARCHAR2)
IS
U1 VARCHAR2(100):=UNAME;

CURSOR one IS SELECT u1.name USERNAME ,U2.NAME ROLENAME ,SUBSTR(SPM.NAME,1,27) PRIVILEGE
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2,SYS.USER$ U1, SYS.USER$ U2,SYS.SYSTEM_PRIVILEGE_MAP SPM
WHERE SA1.GRANTEE# = U1.USER#
AND SA1.PRIVILEGE# = U2.USER#
AND U2.USER# = SA2.GRANTEE# (+)
AND SA2.PRIVILEGE# = SPM.PRIVILEGE (+) AND
(U1.NAME IN
(SELECT GRANTEE FROM DBA_ROLE_PRIVS connect by prior
granted_role=GRANTEE start with GRANTEE IN
(SELECT NAME FROM USER$ WHERE user# in
(select privilege# from sysauth$ t1, user$ t2
where t1.grantee#=t2.user# and t2.name=U1)
)
UNION
SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS connect by prior
granted_role=GRANTEE start with GRANTEE IN
(SELECT NAME FROM USER$ WHERE user# in
(select privilege# from sysauth$ t1, user$ t2
where t1.grantee#=t2.user# and t2.name=U1)
)
)
OR U1.NAME=U1
)
ORDER BY U2.USER#, U2.NAME ;

CURSOR two IS select u.name username,spm.name privilege
from user$ u, sysauth$ s, system_privilege_map spm
where u.user#=s.grantee# and s.privilege#=spm.privilege
AND U.NAME=U1
ORDER BY 1,2;

BEGIN
dbms_output.put_line(rpad('USERNAME',30,' ')||rPAD('ROLENAME',21,' ')||rPAD('PRIVILEGE',21,' '));
DBMS_OUTPUT.PUT_LINE('----------------------------- -------------------- --------------------');
for y in one loop
dbms_output.put_line(rPAD(y.USERNAME,30,' ')||rPAD(y.ROLENAME,21,' ')||rPAD(y.PRIVILEGE,21,' '));
end loop;
for x in two loop
dbms_output.put_line(rPAD(x.username,51,' ')||rPAD(x.privilege,21,' '));
end loop;

END;
/


undef username
set verify on






SQL> set serveroutput on
SQL> exec LIST_USER_ROLE_PRIVS('SCOTT');

USERNAME ROLENAME PRIVILEGE
----------------------------- -------------------- --------------------
SCOTT CONNECT CREATE SESSION
SCOTT RESOURCE CREATE INDEXTYPE
SCOTT RESOURCE CREATE OPERATOR
SCOTT RESOURCE CREATE TYPE
SCOTT RESOURCE CREATE TABLE
SCOTT RESOURCE CREATE PROCEDURE
SCOTT RESOURCE CREATE SEQUENCE
SCOTT RESOURCE CREATE CLUSTER
SCOTT RESOURCE CREATE TRIGGER
SCOTT DBA CREATE USER
SCOTT DBA UNLIMITED TABLESPACE
SCOTT DBA DROP TABLESPACE
SCOTT DBA MANAGE TABLESPACE
SCOTT DBA ALTER TABLESPACE
SCOTT DBA CREATE TABLESPACE
SCOTT DBA RESTRICTED SESSION
SCOTT DBA ALTER SESSION
SCOTT DBA CREATE SESSION
SCOTT DBA AUDIT SYSTEM
SCOTT DBA ALTER SYSTEM

Oracle Users' System Privileges

Oracle Users' System Privileges
Oracle role PRIVILEGE

Oracle Role


set linesize 200
col PRIVILEGE format a30
col ROLENAME format a20



-- CREATE OR REPLACE VIEW DBA_USER_PRIVS (USERNAME, ROLENAME, PRIVILEGE) AS
SELECT DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME), SUBSTR(U2.NAME,1,20), SUBSTR(SPM.NAME,1,27)
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2, SYS.USER$ U1,
SYS.USER$ U2, SYS.SYSTEM_PRIVILEGE_MAP SPM
WHERE SA1.GRANTEE# = U1.USER#
AND SA1.PRIVILEGE# = U2.USER#
AND U2.USER# = SA2.GRANTEE#
AND SA2.PRIVILEGE# = SPM.PRIVILEGE
UNION
SELECT U.NAME, NULL, SUBSTR(SPM.NAME,1,27)
FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, SYS.SYSAUTH$ SA, SYS.USER$ U
WHERE SA.GRANTEE#=U.USER#
AND SA.PRIVILEGE#=SPM.PRIVILEGE
/


USERNAME ROLENAME PRIVILEGE
------------------------------ -------------------- ------------------------------
VIHAAN RESOURCE CREATE INDEXTYPE
VIHAAN RESOURCE CREATE OPERATOR
VIHAAN RESOURCE CREATE PROCEDURE
VIHAAN RESOURCE CREATE SEQUENCE
VIHAAN RESOURCE CREATE TABLE
VIHAAN RESOURCE CREATE TRIGGER
VIHAAN RESOURCE CREATE TYPE
VIHAAN UNLIMITED TABLESPACE
WMSYS CONNECT CREATE SESSION

Oracle Corruption mater note ID metalink id 1088018.1

Master Note for Handling Oracle Database Corruption Issues [ID 1088018.1]