How to get session parameters changed by the user in Oracle

oracleoracle-11g-r2trace

Oracle 11.2.0.3 .

I need to identify all "alter session" changed for a specific session.
There is some v$session_* where I able to get this information?

My objective is part of investigation what I doing about a problem of sessions hanging at compile time… from Forms Builder.

I already got a trace of the session and have some suspicious over a bug documented at Oracle Support… but I want more details about this session.

Best Answer

This one is provided by one of the oracle gurus

There are several Initialization Parameters that influence the Optimizer and that can be modified with ALTER SESSION. We can see whether certain sessions have settings different from the System-wide settings with a query like

QL> set lines 200
col name for a40
col username for a10
col value for a40
select a.sid,c.username,a.name,a.value 
from v$ses_optimizer_env a 
join v$sys_optimizer_env b on a.id=b.id 
join v$session c on a.sid=c.sid 
where a.value<>b.value
and c.username is not null
and c.username not in ('SYS','SYSTEM','DBSNMP') 
order by a.sid,a.name;

Related Solutions

Oracle Active Session Pool Questions

The Active Session Pool allows a limit to be place on the number of sessions that can be active on your instance. It is a part of the Resource Manager. The number possible is defined according to your current resource plan. The default plan allows an unlimited number of sessions. It is activated if resource management is performed in the instance. To find out if this is so then check the resource_manager_plan parameter:

show parameter resource_manager_plan

If there is a value then it is activated. You may see something like DEFAULT_PLAN as the VALUE. You can then query DBA_RSRC_PLAN_DIRECTIVES for the number of active sessions possible:

select GROUP_OR_SUBPLAN, ACTIVE_SESS_POOL_P1
  from DBA_RSRC_PLAN_DIRECTIVES
 where PLAN = 'DEFAULT_PLAN';

You'll get a few rows back relating to the number of groups/subplans that are part of this plan. NULL will indicate unlimited. I prefer using Enterprise Manager to query and amend this. To find it in EM go to the Server tab then under the Resource Manager heading is Plans.

More information on the Resource Manager can be found here.

Why does connecting to a database use tempspace

During the connect process Oracle builds information on what access you have. This remains static for the length of the connection. (If you need to access newly granted privileges, you need to reconnect.) Creating this data as temporary tables would seem the appropriate thing to do. This would require temporary tablespace and could trigger the problems you were encountering.

The lack of locks, does not mean there isn't in-flight information floating around. If the transaction inserting the data doesn't commit, then the database will need to track the block containing the row until the transaction is committed. You can check for this by opening two connections and seeing if both can see the information from the login trigger before doing a commit.

There are views which will show what locks exist, and a wide variety of other information which is useful in managing the database.

Best Answer

Related Solutions

Oracle Active Session Pool Questions

Why does connecting to a database use tempspace

Related Question