Home » RDBMS Server » Server Administration » Displaying session level parameters (DB, any release)
Displaying session level parameters [message #592520] Fri, 09 August 2013 06:40 Go to next message
John Watson
Messages: 8922
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.
Re: Displaying session level parameters [message #592532 is a reply to message #592520] Fri, 09 August 2013 08:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Displaying session level parameters [message #592646 is a reply to message #592520] Sat, 10 August 2013 04:42 Go to previous message
John Watson
Messages: 8922
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.
Previous Topic: DBVerify issue
Next Topic: ORA-00020: maximum number of processes (400) exceeded
Goto Forum:
  


Current Time: Thu Mar 28 12:32:33 CDT 2024