IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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 Variable | Description |
|---|---|
| Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package. | |
| Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time. | |
| Controls when Oracle Database commits pending changes to the database. | |
| Sets the automatic printing of bind variables. | |
| 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]] | |
| Sets the non-alphanumeric character used to end PL/SQL blocks to c. | |
| Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c. | |
| Sets the text to be printed between selected columns. | |
| 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. | |
| Controls the number of batches after which the COPY command commits changes to the database. | |
| Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command. | |
| 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. |
| Controls whether the START command lists each command in a script as the command is executed. | |
| Sets the default filename for the EDIT command. | |
| 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. |
| Defines the character you enter as the escape character. | |
| Specifies a special character to escape in a filename. Prevents character translation causing an error. | |
| Specifies whether the default EXIT behavior is COMMIT or ROLLBACK. | |
| Displays the number of records returned by a query when a query selects at least n records. | |
| Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard. | |
| Controls when output is sent to the user’s display device. | |
| Controls printing of column headings in reports. | |
| Defines the character you enter as the heading separator character. | |
| Changes the default instance for your session to the specified instance path. | |
| Sets the total number of characters that SQL*Plus displays on one line before beginning a new line. | |
| Sets the starting position from which BLOB, BFILE, CLOB and NCLOB data is retrieved and displayed. | |
| Specifies the location from which archive logs are retrieved during recovery. | |
| Sets maximum width (in bytes) for displaying LONG, BLOB, BFILE, CLOB, NCLOB and XMLType values; and for copying LONG values. | |
| Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, BLOB, BFILE, CLOB, NCLOB or XMLType value. | |
| Outputs HTML marked up text. | |
| Sets the number of blank lines to be printed from the top of each page to the top title. | |
| Sets the text that represents a null value in the result of a SQL SELECT command. | |
| Sets the default format for displaying numbers. | |
| Sets the default width for displaying numbers. | |
| Sets the number of lines in each page. | |
| Enables you to control scrolling of your terminal when running reports. | |
| RECSEP tells SQL*Plus where to make the record separation. | |
| Display or print record separators. | |
| Controls whether to display the output (that is, DBMS_OUTPUT PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus. | |
| Enables correct alignment for terminals that display shift characters. | |
| Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET. | |
| Controls whether SQL*Plus puts blank lines within a SQL command or script. | |
| Converts the case of SQL commands and PL/SQL blocks just prior to execution. | |
| Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–). | |
| Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block. | |
| Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z]. | |
| Sets the SQL*Plus prefix character. | |
| Sets the SQL*Plus command prompt. | |
| Sets the character used to end and execute SQL commands to c. | |
| Sets the default file that SQL*Plus uses in commands that refer to scripts. | |
| Determines how SQL*Plus formats white space in terminal output. | |
| Controls the display of output generated by commands executed from a script. | |
| Controls the display of the current time. | |
| Controls the display of timing statistics. | |
| Determines whether SQL*Plus puts trailing blanks at the end of each displayed line. | |
| Determines whether SQL*Plus puts trailing blanks at the end of each spooled line. | |
| Sets the character used to underline column headings in SQL*Plus reports to c. | |
| 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. | |
| Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width. | |
| Specifies that only fully optimized XML queries and DML operations are executed. Only to assist in developing and debugging, not for production. | |
| Defines the base URI to use. This is useful to change the prefix of the file to access when writing generic XQuery expressions. | |
| Controls the ordering of results from an XQuery. | |
| Sets the preservation mode for notes created or returned. | |
| 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
One comment
Pingback: oracle sqlplus login command - findadata