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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

839 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