SQL*Plus Set Commands in Oracle

I will explain SQL*Plus Set Commands in Oracle in this post.

 

SQL*Plus Set Commands

When you use the SQL*Plus tool, you need to use SET Commands to suppress the output from the script.

SET Commands are used to put into shape in terms of line size, page size and etc..

When you use the SET commands, result of sql scripts are being readable.

 

 

 

 

Set Commands in Oracle

You can display all of the set commands running the help set command as follows.

SQL> help set

SET
---

Sets a system variable to alter the SQL*Plus environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page

SET system_variable value

where system_variable and value represent one of the following clauses:

APPI[NFO]{OFF|ON|text} NEWP[AGE] {1|n|NONE}
ARRAY[SIZE] {15|n} NULL text
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} NUMF[ORMAT] format
AUTOP[RINT] {OFF|ON} NUM[WIDTH] {10|n}
AUTORECOVERY {OFF|ON} PAGES[IZE] {14|n}
AUTOT[RACE] {OFF|ON|TRACE[ONLY]} PAU[SE] {OFF|ON|text}
[EXP[LAIN]] [STAT[ISTICS]] RECSEP {WR[APPED]|EA[CH]|OFF}
BLO[CKTERMINATOR] {.|c|ON|OFF} RECSEPCHAR {_|c}
CMDS[EP] {;|c|OFF|ON} ROWPREF[ETCH] {n}
COLINVI[SIBLE] {OFF|ON} SERVEROUT[PUT] {ON|OFF}
COLSEP {_|text} [SIZE {n | UNLIMITED}]|
CON[CAT] {.|c|ON|OFF} [FOR[MAT] {WRA[PPED]|
COPYC[OMMIT] {0|n} WOR[D_WRAPPED]|TRU[NCATED]}]
COPYTYPECHECK {ON|OFF} SHIFT[INOUT] {VIS[IBLE] |
DEF[INE] {&|c|ON|OFF} INV[ISIBLE]}
DESCRIBE [DEPTH {1|n|ALL}] SHOW[MODE] {OFF|ON}
[LINENUM {OFF|ON}] [INDENT {OFF|ON}] SQLBL[ANKLINES] {OFF|ON}
ECHO {OFF|ON} SQLC[ASE] {MIX[ED] |
EDITF[ILE] file_name[.ext] LO[WER] | UP[PER]}
EMB[EDDED] {OFF|ON} SQLCO[NTINUE] {> | text}
ERRORL[OGGING] {ON|OFF} SQLN[UMBER] {ON|OFF}
[TABLE [schema.]tablename] SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
[TRUNCATE] [IDENTIFIER identifier] SQLPRE[FIX] {#|c}
ESC[APE] {\|c|OFF|ON} SQLP[ROMPT] {SQL>|text}
ESCCHAR {@|?|%|$|OFF} SQLT[ERMINATOR] {;|c|ON|OFF}
EXITC[OMMIT] {ON|OFF} STATEMENTC[ACHE] {n}
FEED[BACK] {6|n|ON|OFF|ONLY}[SQL_ID] SUF[FIX] {SQL|text}
FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} TAB {ON|OFF}
FLU[SH] {ON|OFF} TERM[OUT] {ON|OFF}
HEA[DING] {ON|OFF} TI[ME] {OFF|ON}
HEADS[EP] {||c|ON|OFF} TIMI[NG] {OFF|ON}
HIST[ORY] {ON|OFF|N} TRIM[OUT] {ON|OFF}
INSTANCE [instance_path|LOCAL] TRIMS[POOL] {OFF|ON}
LIN[ESIZE] {80|n|WINDOW} UND[ERLINE] {-|c|ON|OFF}
LOBOF[FSET] {1|n} VER[IFY] {ON|OFF}
LOBPREF[ETCH] {n} WRA[P] {ON|OFF}
LOGSOURCE [pathname] XQUERY {BASEURI text|
LONG {80|n} ORDERING{UNORDERED|
LONGC[HUNKSIZE] {80|n} ORDERED|DEFAULT}|
MARK[UP] {HTML html_option|CSV csv_option} NODE{BYVALUE|BYREFERENCE|
html_option; DEFAULT}|CONTEXT text}
{ON|OFF} [HEAD text] [BODY text]
[TABLE {ON|OFF}] [ENTMAP {ON|OFF}]
[SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
csv_option:
{ON|OFF} [DELIM[ITER] {c}]
[QUOTE {ON|OFF}]


SQL>

 

 

 

Set timing on : This command is used to show the response time of SQL Codes or script results. The most used SQL*plus set command I have used.

 

SQL> set timing on
SQL> 
SQL> select count(*) from dba_tables;

COUNT(*)
----------
115904

Elapsed: 00:00:03.74
SQL>

 

 

Set line or linesize : This command is used to determine how long character lines in command line as follows. Default line size is 80 character in sqlplus.

 

SQL> select instance_name,host_name,version,status from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
VERSION STATUS
----------------- ------------
MSDB1
msdbadm01.deveci.local
19.0.0.0.0 OPEN


SQL> 
SQL> set lines 300 
SQL> 
SQL> r
1* select instance_name,host_name,version,status from v$instance

INSTANCE_NAME HOST_NAME VERSION STATUS
---------------- ---------------------------------------------------------------- ----------------- ------------
MSDB1 msdbadm01.deveci.local 19.0.0.0.0 OPEN

SQL> 
SQL> 
SQL> set linesize 200
SQL> 
SQL> r
1* select instance_name,host_name,version,status from v$instance

INSTANCE_NAME HOST_NAME VERSION STATUS
---------------- ---------------------------------------------------------------- ----------------- ------------
MSDB1 msdbadm01.deveci.local 19.0.0.0.0 OPEN

SQL>

 

 

 

set pages  or pagesize : This command is used to sets the number of lines in each page of output. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

SQL> set pages 300
SQL> 
SQL> select instance_name,host_name,version,status from v$instance;

INSTANCE_NAME HOST_NAME VERSION STATUS
---------------- ---------------------------------------------------------------- ----------------- ------------
MSDB1 msdbadm01.deveci.local 19.0.0.0.0 OPEN

SQL>

 

 

AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}:  Autocommit command is used to commit after each SQL command or PL/SQL block. If it is ON , commit is done automatically. If it is OFF, you should run COMMIT manually.

 

SET AUTORECOVERY [ON | OFF]: IF this command is set ON, RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.

 

SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] : This command is very important when you review performance of any sql. Because this command displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE, DELETE or MERGE) as follows.

 

SQL> set autotrace on
SQL> 
SQL> select instance_name,host_name,version,status from v$instance;

INSTANCE_NAME HOST_NAME VERSION STATUS
---------------- ---------------------------------------------------------------- ----------------- ------------
MSDB1 msdbadm01.deveci.local 19.0.0.0.0 OPEN


Execution Plan
----------------------------------------------------------
Plan hash value: 4175978637

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 59 | 0 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 1 | 59 | 0 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 1 | 48 | 0 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN| | 1 | | 0 (0)| 00:00:01 |
| 4 | FIXED TABLE FULL | X$QUIESCE | 1 | | 0 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1 | | 0 (0)| 00:00:01 |
| 6 | FIXED TABLE FULL | X$KJIDT | 1 | | 0 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 1 | 48 | 0 (0)| 00:00:01 |
|* 8 | FIXED TABLE FULL | X$KSUXSINST | 1 | 48 | 0 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 1 | 11 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KVIT | 1 | 11 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - filter("KS"."INST_ID"=USERENV('INSTANCE'))
10 - filter("KVITTAG"='kcbwst')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
831 bytes sent via SQL*Net to client
423 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

 

 

Other Commands are as follows.

 

System VariableDescription

SET APPI[NFO]{ON | OFF | text}

Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.

SET ARRAY[SIZE] {15 | n}

Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time.

SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}

Controls when Oracle Database commits pending changes to the database.

SET AUTOP[RINT] {ON | OFF}

Sets the automatic printing of bind variables.

SET AUTORECOVERY [ON | OFF]

ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.

SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE, DELETE or MERGE).

SET BLO[CKTERMINATOR] {. | c | ON | OFF}

Sets the non-alphanumeric character used to end PL/SQL blocks to c.

SET CMDS[EP] {; | c | ON | OFF}

Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.

SET COLSEP { | text}

Sets the text to be printed between selected columns.

SET CON[CAT] {. | c | ON | OFF}

Sets the character you can use to terminate a substitution variable reference if you wish to immediately follow the variable with a character that SQL*Plus would otherwise interpret as a part of the substitution variable name.

SET COPYC[OMMIT] {0 | n}

Controls the number of batches after which the COPY command commits changes to the database.

SET COPYTYPECHECK {ON | OFF}

Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.

SET DEF[INE] {& | c | ON | OFF}

Sets the character used to prefix variables to c.

SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]

Sets the depth of the level to which you can recursively describe an object.

SET ECHO {ON | OFF}

Controls whether the START command lists each command in a script as the command is executed.

SET EDITF[ILE] file_name[.ext]

Sets the default filename for the EDIT command.

SET EMB[EDDED] {ON | OFF}

Controls where on a page each report begins.

SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]

Enables recording of SQL, PL/SQL and SQL*Plus errors to an error log table which you can query later.

SET ESC[APE] {\ | c | ON | OFF}

Defines the character you enter as the escape character.

SET ESCCHAR {@ | ? | % | $ | OFF}

Specifies a special character to escape in a filename. Prevents character translation causing an error.

SET EXITC[OMMIT] {ON | OFF}

Specifies whether the default EXIT behavior is COMMIT or ROLLBACK.

SET FEED[BACK] {6 | n | ON | OFF}

Displays the number of records returned by a query when a query selects at least n records.

SET FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL}

Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.

SET FLU[SH] {ON | OFF}

Controls when output is sent to the user’s display device.

SET HEA[DING] {ON | OFF}

Controls printing of column headings in reports.

SET HEADS[EP] { | c | ON | OFF}

Defines the character you enter as the heading separator character.

SET INSTANCE [instance_path | LOCAL]

Changes the default instance for your session to the specified instance path.

SET LIN[ESIZE] {80 | n}

Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.

SET LOBOF[FSET] {1 | n}

Sets the starting position from which BLOB, BFILE, CLOB and NCLOB data is retrieved and displayed.

SET LOGSOURCE [pathname]

Specifies the location from which archive logs are retrieved during recovery.

SET LONG {80 | n}

Sets maximum width (in bytes) for displaying LONG, BLOB, BFILE, CLOB, NCLOB and XMLType values; and for copying LONG values.

SET LONGC[HUNKSIZE] {80 | n}

Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, BLOB, BFILE, CLOB, NCLOB or XMLType value.

SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]

Outputs HTML marked up text.

SET NEWP[AGE] {1 | n | NONE}

Sets the number of blank lines to be printed from the top of each page to the top title.

SET NULL text

Sets the text that represents a null value in the result of a SQL SELECT command.

SET NUMF[ORMAT] format

Sets the default format for displaying numbers.

SET NUM[WIDTH] {10 | n}

Sets the default width for displaying numbers.

SET PAGES[IZE] {14 | n}

Sets the number of lines in each page.

SET PAU[SE] {ON | OFF | text}

Enables you to control scrolling of your terminal when running reports.

SET RECSEP {WR[APPED] | EA[CH] | OFF}

RECSEP tells SQL*Plus where to make the record separation.

SET RECSEPCHAR { | c}

Display or print record separators.

SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]

Controls whether to display the output (that is, DBMS_OUTPUT PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.

SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]}

Enables correct alignment for terminals that display shift characters.

SET SHOW[MODE] {ON | OFF}

Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET.

SET SQLBL[ANKLINES] {ON | OFF}

Controls whether SQL*Plus puts blank lines within a SQL command or script.

SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}

Converts the case of SQL commands and PL/SQL blocks just prior to execution.

SET SQLCO[NTINUE] {> | text}

Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).

SET SQLN[UMBER] {ON | OFF}

Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block.

SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}

Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z].

SET SQLPRE[FIX] {# | c}

Sets the SQL*Plus prefix character.

SET SQLP[ROMPT] {SQL> | text}

Sets the SQL*Plus command prompt.

SET SQLT[ERMINATOR] {; | c | ON | OFF}

Sets the character used to end and execute SQL commands to c.

SET SUF[FIX] {SQL | text}

Sets the default file that SQL*Plus uses in commands that refer to scripts.

SET TAB {ON | OFF}

Determines how SQL*Plus formats white space in terminal output.

SET TERM[OUT] {ON | OFF}

Controls the display of output generated by commands executed from a script.

SET TI[ME] {ON | OFF}

Controls the display of the current time.

SET TIMI[NG] {ON | OFF}

Controls the display of timing statistics.

SET TRIM[OUT] {ON | OFF}

Determines whether SQL*Plus puts trailing blanks at the end of each displayed line.

SET TRIMS[POOL] {ON | OFF}

Determines whether SQL*Plus puts trailing blanks at the end of each spooled line.

SET UND[ERLINE] { | c | ON | OFF}

Sets the character used to underline column headings in SQL*Plus reports to c.

SET VER[IFY] {ON | OFF}

Controls whether SQL*Plus lists the text of a SQL statement or PL/SQL command before and after SQL*Plus replaces substitution variables with values.

SET WRA[P] {ON | OFF}

Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width.

SET XMLOPT[IMIZATIONCHECK] [ON|OFF]

Specifies that only fully optimized XML queries and DML operations are executed. Only to assist in developing and debugging, not for production.

SET XQUERY BASEURI {text}

Defines the base URI to use. This is useful to change the prefix of the file to access when writing generic XQuery expressions.

SET XQUERY ORDERING {UNORDERED | ORDERED | DEFAULT}

Controls the ordering of results from an XQuery.

SET XQUERY NODE {BYVALUE | BYREFERENCE | DEFAULT}

Sets the preservation mode for notes created or returned.

SET XQUERY CONTEXT {text}

Specifies an XQuery context item which can be either a node or a value.

 

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database

 1,936 views last month,  4 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply