set commands of SQL*Plus

SQL> set autotrace on; OR
SQL> set autotrace on explain statistics;

SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> set autotrace traceonly;

SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report

How do I remove the above errors so that I can see the output. When we get exception, to
see next levels of exceptions it is advised to turn tracing on. Is this the same one.

appinfo is not part of options ---> environment.
set appinfo on;
set appinfo off;
set appinfo 'truce';
Enables and disables the automatic registration of command files. The app_text argument allows
you to specify the registration text to use when no command file is being run.
Commands are working but what is the purpose and in what way I can know it.

buffer is not part of options ---> environment.
BUFFER {buffer_name | SQL}    Allows you to switch between buffers.
Can there be more than one buffer in SQL*Plus and if so how do we create them and switch.

set cmdsep on
set cmdsep ^;
desc dept ^ desc emp WORKING. But,
select count(rowid) from dept ^ select count(rowid) from emp;

Only the second statement gives a result. Why so and what all statements are covered in
the possibility of multiple statements on the same line.

CONCAT {OFF|ON|concat} Specifies the character that you use to terminate a SQL*Plus user
variable name. The default is a period. What does this mean and how to check that a
different character other than default is working or not.

COPYCOMMIT batch_count  Specifies the number of batches that SQL*Plus will write during the
execution of a COPY command before executing a COMMIT. How to specify number of batches.
We will transfer only one table/view at a time.

COPYTYPECHECK {OFF|ON}  Allows one to suppress type checking when the copy command is being
used. What is the meaning of typechecking.

DEFINE {OFF|ON|prefix}   Specifies the prefix character used to identify user variables and
also allows you to enable or disable the recognition of those variables. Default prefix
character is the ampersand(&). How can one be able to understand such stuff. & is the
substitution variable and is disabled with set define off OR set define "!".

FEEDBACK {OFF|ON|row_threshold}  Controls whether you see feedback after issuing  a SQL
statement. I did not see any diff. in feeback from changing default value 6 to 1.

FLAGGER {OFF|ENTRY|INTERMEDIATE|FULL}  Specifies the level of FLIPS flagging that you need.
After using all options also, I cannot understand what it is.

HEADSEP heading_separator  Specifies the separator character used to divide a column heading
into two or more lines. The default value is a vertical bar(|). Even if I use long column
aliases, they are cut short but not separated into lines with |.

LOBOFFSET offset is not part of options ---> environment.

LOGSOURCE logpath  Specifies the directory containing your archived redo log files. Default is
"". I managed to set as "D:\Oracle\oradata\smartweb\archive", but what is the significance.

LONG long_length Specifies the number of characters that will be displayed when a long value
is selected. The default is 80. Linesize setting dominates. If linesize 80, then whether long
value is 80/100, the display is the same.

LONGCHUNKSIZE size Specifies the chunk size that SQL*Plus uses when retreiving a long value.
Same doubt for this as well.

NULL null_text Specifies the text to display in place of a null column value. I used:
set NULL "null" and it is working but null is displayed to the left of the column values
where ever blank used to be there earlier.

SCAN {OFF|ON}  Enables or disables scanning for SQL*Plus user variables. How to see the effect
of scanning and otherwise.

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


   First of all, you need to create a plan_table.  You can create a system-wide one (using a public synonym) or you can create one in the schema where you will use this feature.  The create command is in utlxplan.sql in your <home>\rdbms\admin directory.

   The rest is answered in the SQL*PLus users guide for each of the commands/options you list:

for 8i:

for 9i:

Good luck!

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
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.

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.