OraFAQ Forum: Server Administration » Displaying session level parameters

Home » RDBMS Server » Server Administration » Displaying session level parameters (DB, any release)

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

Displaying session level parameters [message #592520]

Fri, 09 August 2013 06:40

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

Does anyone happen to know a way to show session parameters? Such as
CONSTRAINTS
USE_STORED_OUTLINES
ROW ARCHIVE VISIBILITY
CURRENT_SCHEMA
I can detect the CURRENT_SCHEMA with a query against the userenv context, but I can't find any of the others there. Could there be an issue with these values being stored in PGA, and therefore not visible though any regular views? I did find an article that showed a query against an x$ data structure which showed something for different settings of CONSTRAINTS, but I can't find it again.

Thankyou for any insight.

Report message to a moderator

Re: Displaying session level parameters [message #592532 is a reply to message #592520]

Fri, 09 August 2013 08:14

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Hi John,

Hope this helps.

For CONSTRAINTS, I tried with

select ksuseflg from x$ksuse where ksspaown=(select paddr from v$session where username = 'SYS');

I am not able to recollect properly, but in bits and pieces I remember that ksuseflg value could be interpreted to know the constraints status in session.

Found this interesting thing in OTN forum.

SQL> alter session set constraints=default;

Session altered.

SQL> select ksuseflg from x$ksuse where ksspaown=(select paddr from v$session where username = 'SYS');

  KSUSEFLG
----------
        65

SQL>
SQL> alter session set constraints=immediate;

Session altered.

SQL> select ksuseflg from x$ksuse where ksspaown=(select paddr from v$session where username = 'SYS');

  KSUSEFLG
----------
    262209

SQL> alter session set constraints=deferred;

Session altered.

SQL> select ksuseflg from x$ksuse where ksspaown=(select paddr from v$session where username = 'SYS');

  KSUSEFLG
----------
    131137

SQL> alter session set constraints=default;

Session altered.

SQL> select ksuseflg from x$ksuse where ksspaown=(select paddr from v$session where username = 'SYS');

  KSUSEFLG
----------
        65
Seems that there are two bits 0x00020000, 0x00040000 which mark the constraints status in session.

Here is the OTN thread

[Updated on: Sun, 02 March 2014 13:23] by Moderator

Report message to a moderator

Re: Displaying session level parameters [message #592551 is a reply to message #592532]

Fri, 09 August 2013 10:51

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

Senior Member
Account Moderator

Maybe it clearer like this:

SQL> select to_char(65,'XXXXXXXX') "DEFAULT",
  2         to_char(131137, 'XXXXXXXX') "DEFERRED",
  3         to_char(262209, 'XXXXXXXX') "IMMEDIATE"
  4  from dual;
DEFAULT   DEFERRED  IMMEDIATE
--------- --------- ---------
       41     20041     40041

(At least it is for me.)

Regards
Michel

Report message to a moderator

Re: Displaying session level parameters [message #592646 is a reply to message #592520]

Sat, 10 August 2013 04:42

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

Thanks for thoughts. I didn't think there was a way to get this information. For use_stored_outlines it has been a problem for me in the past, and I foresee problems with row archival.

Report message to a moderator

Previous Topic:	DBVerify issue
Next Topic:	ORA-00020: maximum number of processes (400) exceeded

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 23 00:53:22 CST 2025