SQL Command Line SET Commands

To view all the settings, enter the following at the SQL prompt

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 18 21:24:27 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: fhrd/fhrd@orcl

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SHOW ALL
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
exitcommit ON
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 1102000100
repfooter OFF and is NULL
repheader OFF and is NULL
securedcol is OFF
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 11.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "FHRD"
verify ON
wrap : lines will be wrapped
errorlogging is OFF



SQL> SET PAGESIZE 200
SQL> SET LINESIZE 140



To enable output from PL/SQL blocks with DBMS_OUTPUT.PUT_LINE, use the following:

SQL> SET SERVEROUTPUT ON







Running Scripts From SQL Command Line

You can use a text editor to create SQL Command Line script files that contain SQL*Plus, SQL, and PL/SQL statements. For consistency, use the .sql extension for the script file name.

A SQL script file is executed with a START or @ command. For example, in a Windows environment, you can execute a SQL script as follows:

SQL> @c:\my_scripts\my_sql_script.sql

A SQL script file can be executed in a Linux environment as follows:

SQL> START /home/cjones/my_scripts/my_sql_script.sql

You can use SET ECHO ON to cause a script to echo each statement that is executed. You can use SET TERMOUT OFF to prevent the script output from displaying on the screen.

When running a script, you need to include the full path name unless the script is located in the directory from which SQL Command Line was started, or the script is located in the default script location specified by the SQLPATHenvironment variable.

SET MARKUP HTML PREFORMAT ON
SET COLSEP '|'
SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 20;

LAST_NAME |JOB_ID |DEPARTMENT_ID -------------------------|----------|------------- Hartstein |MK_MAN | 20 Fay |MK_REP | 20 How to check the Oracle database version SELECT * FROM V$VERSION or SELECT version FROM V$INSTANCE or SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE); END; Figure 1-1 Example of an Oracle Database Release Number Major Database Release Number The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality. Database Maintenance Release Number The second digit represents a maintenance release level. Some new features may also be included. Application Server Release Number The third digit reflects the release level of the Oracle Application Server (OracleAS). Component-Specific Release Number The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases. Platform-Specific Release Number The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms. SQL> COL PRODUCT FORMAT A35 SQL> COL VERSION FORMAT A15 SQL> COL STATUS FORMAT A15 SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION; PRODUCT VERSION STATUS ----------------------------------- --------------- --------------- NLSRTL 11.2.0.1.0 Production Oracle Database 11g Enterprise Edit 11.2.0.1.0 Production ion PL/SQL 11.2.0.1.0 Production TNS for 32-bit Windows: 11.2.0.1.0 Production How to find the latest SQL statements within the database? Step 1: Determine the installatin IDs & user IDs. SELECT inst_id,sid FROM gv$session WHERE username=''; Step 2: SELECT s.sid ,s.CLIENT_INFO ,s.MACHINE ,s.PROGRAM ,s.TYPE ,s.logon_time ,s.osuser ,sq.sorts ,sq.DISK_READS ,sq.BUFFER_GETS ,sq.ROWS_PROCESSED ,sq.SQLTYPE ,sq.SQL_TEXT FROM gv$session s , gv$sql sq WHERE s.SQL_HASH_VALUE = sq.HASH_VALUE AND s.inst_id=:id -- replace with ID from above AND s.sid = :sid -- replace with instID from above AND sq.inst_id= s.inst_id select * from global_name;