set commands of SQL*Plus

Posted on 2002-03-25
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
1 Comment
LVL 22

Accepted Solution

DrSQL earned 75 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
statspack purge automate 7 49
null value 15 92
Oracle - Create Procedure with Paramater 16 56
Article by: Swadhin
From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

943 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now