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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Suggested Solutions

Title # Comments Views Activity
Repeat query 13 72
How to Gracefuly recover in Racle stored procedure 1 45
Database Design Dilemma 6 65
oracle differnce between two timestamps 5 38
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

735 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