Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


set commands of SQL*Plus

Posted on 2002-03-25
Medium Priority
Last Modified: 2007-11-27
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.

Question by:ravs2001
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
LVL 22

Accepted Solution

DrSQL earned 300 total points
ID: 6894099

   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!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question