Oracle database: how to display the parameters such as "AUTOTRACE"?

Hi Experts:

    From some tutorial I found that we can should be able to tune the behavior of Oracle database in a certain session by using "SET" command.

    For example, "set autotrace traceonly explain;".

    Before changing the parameter with the "SET" command, I think I need to look at its original value first, can you tell me how to do that?

    Thank you!
huangs3Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Alter session commands are different than sqlPlus set commands.

autotrace is a sql*plus command and optimizer is a database session command.

To show current sql*plus settings:
show all

To show current session settings (set with alter session):
select name, value from v$parameter where ismodified = 'MODIFIED';
0
 
slightwv (䄆 Netminder) Commented:
exit sql*plus and log back in. Unless there is a glogin.sql or local login.sql, it will reset everything back to the defaults.

Most sqlplus parameters are 'off' by default so in this case: set autotrace off
0
 
huangs3Author Commented:
Hi, slightwv:
    I understand that log off and log in can discard those change, but I just want to know the general command that can be used to check the current state of the session.

    Besides the command I of just "SET" command, I also see some "ALTER SESSION SET" commands, such as "ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1", I also want to know how to check the values of the parameters such as "OPTIMIZER_MODE".
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
FYI:  even if you use alter session to set the parameter back to the original value, it still shows 'MODIFIED' in v$parameter.

Play around with it.  You'll figure it out.
0
 
huangs3Author Commented:
Hi slightwv:
> To show current session settings (set with alter session):
> select name, value from v$parameter where ismodified = 'MODIFIED';
I got an error message of ORA-00942: table or view does not exist
Does that mean I don't have the privilege? However I do have privilege to run the alter session command.
0
 
huangs3Author Commented:
> FYI:  even if you use alter session to set the parameter back to the original value, it still
> shows 'MODIFIED' in v$parameter.
Understand.
0
 
slightwv (䄆 Netminder) Commented:
>>Does that mean I don't have the privilege?

Yes.

I can't locate any other common view that has this information.  If I find one later I'll let you know.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.