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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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".
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';

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
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.
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.
slightwv (䄆 Netminder) Commented:
>>Does that mean I don't have the privilege?


I can't locate any other common view that has this information.  If I find one later I'll let you know.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.