Oracle DBA by Example. Code samples, Scripts, Reference.: sqlplus options

Oracle Complete Reference for sqlplus options
=======================
command line options
=======================
sqlplus -? - Help for command line options

sqlplus -v - Return version for sqlplus and database

for example:

SQL*Plus: Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

sqlplus -S - Silent mode. This would suppress the SQL*Plus banner, prompts,
and echoing of commands.

sqlplus -L - Attempts to log on just once.

sqlplus -prelim - Opens session in Preliminary mode.
Sometimes, because the threashold of sessions is reached,
a DBA cannot login to the database. Even to perform a SHUTDOWN.
In this case, the solution would be to use the -prelim option.
When opening a session in Preliminary mode, it will not try to create
private session structures in the SGA.
This allows you to connect to perform debugging or shutdown
operations.

sqlplus -R <level>

- Restrict mode.
This would disable SQL*Plus commands that interact with the file system.
The level can be 1, 2 or 3.
The most restrictive is -R 3.
R3 which disables all user commands interacting with the file system.

sqlplus -M "<options>"
- Markup mode.
Would generate output in HTML format.
Sets automatic HTML markup of output.
The options have the form:
HTML [ON|OFF]
[HEAD text]
[BODY text]
[TABLE text]
[ENTMAP {ON|OFF}]
[SPOOL {ON|OFF}]
[PRE[FORMAT] {ON|OFF}]

sqlplus /nolog
Login without connecting to any schema.

=======================
Connect as sysdba
=======================

From privileged account:

sqlplus / as sysdba

sqlplus /nolog
connect as sysdba

In Oracle 9, one must use ""
sqlplus "/as sysdba"

=======================
Display options
=======================
SET COLSEP ,      - Separate columns with a comma
SET ESCAPE \      - This allows to enter special characters into database as strings.
For example 'A\&B' would be parsed to string 'A&B', rather than
to variable substitution.
SET DEFINE OFF   - Do not substitute variables with using &&

SET ECHO OFF     - Do not print the sql command
SET FEEDBACK OFF  - Do not display "5 rows selected"
SET HEADING OFF   - Do not display Heading line
SET HEADSEP OFF   - This may or may not be useful...depends on your headings.
SET LINESIZE X    - X should be the sum of the column widths

SET LONG X - X number of displayed characters for LONG column

SET LONGCHUNKSIZE X - Controls the number of characters retrieved at one time from a

LONG column.

The Default is 80.

With this default, the one long string coming from CLOB -

will be split into several 80 chars lines. each line is

a separate iteration to sever.
LONGCHUNKSIZE value should be equal to LONG.

SET NEWPAGE NONE  - Get rid on first empty line in spool file.
SET NUMW X        - X should be the length you want for numbers.
SET PAGESIZE 0    - No header rows
SET SHOW OFF      - Do not display old: value new: value messages for SET changes.
SET SERVEROUTPUT ON-Enable messaged from DBMS_OUTPUT to be printed to console.
SET SQLBLANKLINES ON - Ignore blank lines.
This is useful when dealing with multi line commands,
and not using '_' as "line continuation".
SET TERMOUT OFF   - Do not display output generated by commands in script.
SET TRIMSPOOL ON  - Do not put trailing blanks at the end of each spooled line.

SET TRIMOUT ON - Trim the trailing blanks

SET TIMING ON - Display the elapsed time

SET VERIFY OFF - Do not display old: value new: value messages

for variable substitution.
SET UNDERLINE OFF - Do not underline heading

NLS
Format date with NLS

ALTER SESSION SET NLS_DATE_FORMAT='YYYY:MMDD HH24:MI:SS';

COLUMN MY_COLUMN FORMAT A300 WORD_WRAP

WORD_WRAP         - Wraps each line to the length specified by LINESIZE.
                   That way words are not cut between lines.
NEW_VALUE   - An option to define, and assign value to the defined variable.
Usage of NEW_VALUE Example:

COL report_name_col NEW_VALUE report_name NOPRINT

SELECT 'MY_FILE_'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')||'.csv' report_name_col FROM DUAL;

SELECT &report_name FROM DUAL

SPOOL &report_name

=======================
Using variables
=======================
Option A. Static Variables
SET SHOW OFF
SET VERIFY OFF

DEFINE report_name = SELECT 'AIRCEL_HSC_REP_295_'||'201512'||'.csv' FROM DUAL;
SPOOL &report_name

Option B. Dynamic Variables
SET SHOW OFF
SET VERIFY OFF

COL report_name_col NEW_VALUE report_name NOPRINT
SELECT 'AIRCEL_HSC_REP_295_'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')||'.csv'
SPOOL &report_name

=========================

How to get rig of trailing blanks in output file

=========================

SET TRIMSPOOL ON

SET TRIMOUT ON

SET LINESIZE 4000

This will set output line to 4000, and the trailing lines in generated file will be trimmed.

=======================

Format Display options
=======================

COL COL_A NOPRINT - Suppress display of a column

=======================
Additional options
=======================



exec DBMS_LOCK.SLEEP(120); - Sleep in sqlplus

REM  - Remark line





=======================
HTML Display options
=======================
SET MARKUP HTML ON SPOOL ON  - Would produce output in HTML format.

                             i.e. HTML pages automatically encapsulated with

                             <HTML> and <BODY> tags.

PREFORMAT OFF 

ENTMAP ON 






=======================

spool options

=======================
SPOOL my_file
SPOOL my_file append




Error: SP2-0333: Illegal spool file name: (bad character: ' ')
SPOOL my_file append return an error.
Reason: append mode is available from sqlplus version 10 and higher. 
Example:

SQL> spool bbb append
SP2-0333: Illegal spool file name: "bbb append" (bad character: ' ')

SQL> select * from V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
SQL> exit

>sqlplus -version
SQL*Plus: Release 9.2.0.1.0 - Production



As a workaround, use the OS append option

TYPE file2 >> file1

less file2 >> file1




=======================

Example, spool to a file without spool to sqlplus window.
=======================

Create new file my_sql.sql


SET FEEDBACK OFF
SET HEADING OFF 
SET NEWPAGE NONE
SET SHOW OFF  
SET VERIFY OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET PAGESIZE 0
SET LINESIZE 600
SET COLSEP ","

SPOOL my_output.txt
SELECT col_1||' , '||col_2||' , '||col_3 
FROM MY_TABLE;

SPOOL OFF




Run the sqlplus

sqlplus -s my_user/my_pass@orainst @my_sql.sql

=======================

IF in sqlplus

=======================
-- Activate Variables Substitution

SET DEFINE ON
-- Suppress echo for substitution variables
SET VERIFY OFF
-- activate STDOUT
SET SERVEROUTPUT ON

SET FEEDBACK OFF



COL INSTALL_AUTO_UPD NEW_VAL INSTALL_AUTO_UPD_VAL
COL Auto_Activate_GSM_NET FOR A22
DEFINE Auto_Activate_GSM_NET=0


PROMPT Running with Auto_Activate_GSM_NET set to Value &&Auto_Activate_GSM_NET
SET TERMOUT OFF


SELECT DECODE (&&Auto_Activate_GSM_NET,1,'cre_moco_util_objects.sql','dummy_install.sql') 

       AS INSTALL_AUTO_UPD  

  FROM DUAL;


SET TERMOUT ON


PROMPT
PROMPT =======================================
PROMPT CREATE OR REPLACE PACKAGE MOCO_UTIL 
PROMPT =======================================

@@ &INSTALL_AUTO_UPD_VAL

PROMPT
PROMPT =======================================
PROMPT Done
PROMPT =======================================
PROMPT
SET DEFINE OFF
SPOOL OFF

=======================
Example, split a file to 10 files, and append commit at the end
=======================
Example:
Split input file aa.sql to several files, each one 3000 Lines.
And append commit at the end of each one

split -l 3000 -e --additional-suffix=.sql aa.sql

find . -type f -print0 | while IFS= read -r -d $'\0' file;
  do echo "$file"  | echo "commit;" >> "$file" ;
done

=======================

Reference

=======================

SQL*Plus Command Reference in docs.oracle.com

Generating HTML Reports from SQL*Plus Oracle Reference

Nice Blog: Simulating Control Flow in sqlplus

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Thursday, September 11, 2014

sqlplus options

No comments:

Post a Comment