OraFAQ Forum: Security » Grant ROLE to user takes longer time

Home » RDBMS Server » Security » Grant ROLE to user takes longer time (Oracle 12c, windows 2012)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Grant ROLE to user takes longer time [message #648664 is a reply to message #648661]

Mon, 29 February 2016 02:12

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Trace the session.

Report message to a moderator

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.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Grant ROLE to user takes longer time [message #648671 is a reply to message #648669]

Mon, 29 February 2016 04:21

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The raw trace with WAIT events.

Report message to a moderator

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

Report message to a moderator

Re: Grant ROLE to user takes longer time [message #648674 is a reply to message #648672]

Mon, 29 February 2016 05:37

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Here is the Raw trace file with the Wait Events.

Where?

Report message to a moderator

Re: Grant ROLE to user takes longer time [message #648676 is a reply to message #648674]

Mon, 29 February 2016 06:06

muktha_22
Messages: 527
Registered: December 2009

Senior Member

Hi Michel,

Sorry by mistaken it was sent without the attachment.

Here it is.

Regards
Muktha

Attachment: Isslapps.txt
(Size: 1.93KB, Downloaded 2326 times)

Report message to a moderator

Re: Grant ROLE to user takes longer time [message #648677 is a reply to message #648676]

Mon, 29 February 2016 06:08

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Incomplete file.

Report message to a moderator

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.

Code: [Select all] [Show/ hide]

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

Attachment: Tkrpof_with wait events.txt
(Size: 20.99KB, Downloaded 2605 times)

Report message to a moderator

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: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Grant ROLE to user takes longer time [message #648738 is a reply to message #648736]

Wed, 02 March 2016 01:45

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I used the below query to find out the lock of the object.

Just query V$LOCK.

Report message to a moderator

Previous Topic:	Verify sec_protocol_error_further_action
Next Topic:	Role-based Sensitive Column Masking by VPD (ORA-28112)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 07:58:24 CDT 2025