| Grant ROLE to user takes longer time [message #648661] |
Mon, 29 February 2016 01:50  |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi All,
Having a problem of granting role.
I am trying to grant a role to an user, which takes long time and not finishing.
I tried with Locally and Remotely.
Found no error in ALERT LOG FILE.
No SYNTAX error
Kindly help.
Regards
Muktha
|
|
|
|
|
|
| Re: Grant ROLE to user takes longer time [message #648669 is a reply to message #648664] |
Mon, 29 February 2016 04:16  |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi Michel,
Here is the Trace File Output. I couldn't see any issues, except the Library Cache Miss during the parsing stage.
Kindly guide me.
SQL ID: 0qshmpqwz6mmz Plan Hash: 0
grant <role> to <user>;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
********************************************************************************
SQL ID: 5hrvvu1r771m5 Plan Hash: 415205717
SELECT VALUE$
FROM
SYS.PROPS$ WHERE NAME = 'OGG_TRIGGER_OPTIMIZATION'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS FULL PROPS$ (cr=3 pr=0 pw=0 time=34 us cost=2 size=28 card=1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Regards
Muktha
|
|
|
|
|
|
| Re: Grant ROLE to user takes longer time [message #648672 is a reply to message #648671] |
Mon, 29 February 2016 05:16 |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi Michel,
Here is the Raw trace file with the Wait Events.
I have used the method of 'EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);"
Thanks and Regards
Muktha
|
|
|
|
|
|
|
|
|
|
| Re: Grant ROLE to user takes longer time [message #648719 is a reply to message #648677] |
Tue, 01 March 2016 23:58 |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi Michel,
Last time I traced another session through SYS Login, but this time I traced the own session as below.
ALTER SESSION SET SQL_TRACE TRUE;
BEGIN
DBMS_SESSION.session_trace_enable (waits => TRUE,
binds => FALSE,
plan_stat => 'all_executions'
);
END;
Here is the trace output file with wait event details.
Regards
Muktha
|
|
|
|
| Re: Grant ROLE to user takes longer time [message #648722 is a reply to message #648719] |
Wed, 02 March 2016 00:11 |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
WAIT #1895312376: nam='enq: CB - role operation' ela= 198092906 name|mode=1128398854 0=0 0=0 obj#=-1 tim=2403565707281
Someone is holding the master CBAC (Code Based Access Control) lock due to new method to grant roles to PL/SQL units. Check V$LOCK.
ERROR #1895312376:err=3113 tim=2403565707442
ORA-03113 -> you have a trace file.
Check it, then check MOS/Metalink.
[Updated on: Wed, 02 March 2016 00:13] Report message to a moderator |
|
|
|
| Re: Grant ROLE to user takes longer time [message #648736 is a reply to message #648722] |
Wed, 02 March 2016 01:42 |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi Michel,
FYI,
I tried with another roles to another users, but couldn't get succeed.
Nothing I could found in the v$lock.
I used the below query to find out the lock of the object.
SELECT p.username pu,
s.username su,
s.status stat,
s.sid ssid,
s.serial# sser,
lpad(p.spid,7) spid,
substr(sa.sql_text,1,540) txt
FROM v$process p, v$session s, v$sqlarea sa
WHERE p.addr=s.paddr
AND s.username is not null
AND s.sql_address=sa.address(+)
AND s.sql_hash_value=sa.hash_value(+)
and s.sid in (select sid from v$lock)
ORDER BY 1,2,7
Sorry to say that I am unaware of the new feature CBAC of 12c version.
Will take time to read that.
Help me.
Regards
Muktha
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
