| Session Parameters [message #59473] |
Mon, 24 November 2003 18:57  |
Sujit Sarkar
Messages: 40
Registered: September 2003
|
Member |
|
|
Hi Friends,
I have a Problem.
Suppose there is a normal user having CONNECT and RESOURCE Role and he cannot access V$PARAMETER dictionary view because of lack of Privilege.
Now If he has modified some init.ora Parameters in the Session Level
(ALTER SESSION ).
Is there any dictionary view that he can access to see what all Parameters have been modified by Him in that Currect Session.
Thanx in Advance
Sujit
|
|
|
|
| Re: Session Parameters [message #59483 is a reply to message #59473] |
Wed, 26 November 2003 01:42  |
Frank Naude
Messages: 4580
Registered: April 1998
|
Senior Member |
|
|
Hi,
Only SYSTEM level changes (alter system set...) and some SESSION level changes (alter session set…) are recorded in the V$PARAMETER view. The confusing part is that the Oracle 9i Reference describes V$PARAMETER as "initialization parameters that are currently in effect for the session"!
Look at this example:
SQL> select name, value from sys.v_$parameter
2 where name in ('nls_date_format', 'sql_trace', 'timed_statistics');
NAME VALUE
------------------------------ ------------------------------
timed_statistics TRUE
nls_date_format DD-MON-RRRR
sql_trace FALSE
SQL> alter session set nls_date_format = 'DD-Mon-YYYY HH24:MI:SS';
Session altered.
SQL> alter session set sql_trace = TRUE;
Session altered.
SQL> alter session set timed_statistics = FALSE;
Session altered.
SQL> select name, value from sys.v_$parameter
2 where name in ('nls_date_format', 'sql_trace', 'timed_statistics');
NAME VALUE
------------------------------ ------------------------------
timed_statistics FALSE (YES, CHANGED)
nls_date_format DD-MON-RRRR (NOT CHANGED!)
sql_trace FALSE (NOT CHANGED!)
Fortunately, some session level parameters (like NLS_DATE_FORMAT) are listed in the NLS_SESSION_PARAMETERS view. In addition to that, one can use the DBMS_SYSTEM.READ_EV procedure to retrieve the EVENT settings for the current session.
Best regards.
Frank
|
|
|
|
| Re: Session Parameters [message #59492 is a reply to message #59483] |
Wed, 26 November 2003 09:39 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Frank, you are right. Only 'some' parameters( actually those that use the dynamic parameter interface) are reflected in V$parameter and show session level changes.
Since sql_trace is identified as an event,it has to be queried through the dbms_system.read_ev procedure. I believe in 10G, they have modified sql_trace to be able to work with this interface and hence see the changes in v_$parameter.
Some of the parameters I have tried ,that do get reflected in v$parameter, when session modified are
sort_area_size
timed_statistics
optimizer_goal
hash_area_size
optimizer_index_caching
query_rewrite_enabled
etc
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
