sqlplus tips
Tricks and tips about your everyday Oracle sqlplus usage.
After I tryed psql, I hate sqlplus.
Hey Oracle, you are a really big corporation. Why do you keep sqlplus so unusable? Don’t you have money to pay your developers and add new features like command history?
RANT> exit
By the way, sometimes I need to use sqlplus. Here it is a set of tips I always forget.
How to login
$ sqlplus schema@sid
where schema is your user name and sid can be found in the tnsnames.ora.
Do not show your password around, avoid this syntax
$ sqlplus schema/password@sid
You can also do this
$ sqlplus /nolog
SQL> CONNECT AS SYSDBA -- if you can :)
Check your environment
Make sure your env vars are set properly
echo $ORACLE_HOME
Set your EDITOR var to enable the EDIT command. It is a good idea to set this variable in your .bash_profile, cause it is used also by other softwares (like git, subversion, PostgreSQL and many more)
export EDITOR=vim
If you are already logged in sqlplus, you can do
SQL> DEFINE _EDITOR=vim
Exit command
SQL> quit
Basic operations
Manage SQL buffer content
Suppose you run a query, for instance
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
------------------
02-APR-15
Now the SQL buffer contains last statement, you can run it again with
SQL> RUN
Or even, the more comfortable
SQL> r
Yes, because Oracle is not case sensitive with its keywords and many sqlplus commands has a shorter alias, for example LIST has L or l.
If you want to show the most recently executed SQL command
SQL> LIST
or simply
SQL> l
Show command help
SQL> HELP INDEX
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
For example
SQL> HELP LIST
Show sqlplus settings
SQL> SHOW ALL
Show current user
SQL> SHOW USER
Get tables list
SQL> SELECT * FROM user_objects WHERE object_type = 'TABLE';
List table columns
SQL> DESC mytable
Common tasks
Get first n rows from a table
SQL> SELECT * FROM foo WHERE ROWNUM < 100;
Setup your prompt
Defaults are really weird, try to make it more usable.
Create a login.sql file in your current directory, your $HOME for instance, or in some directory you add to SQLPATH env var. Add to login.sql the following content
-- Make SQL prompt show database name so I know where I am (thanks to Tom Kyte for this)
COLUMN global_name new_value gname
SET TERMOUT OFF
SELECT LOWER(USER) || '@' || global_name || '> ' AS global_name FROM global_name;
SET SQLPROMPT '&gname'
SET TERMOUT ON
SET TIME ON
SET PAUSE ON
SET PAGESIZE 80
SET LINESIZE 120
Remember that, since PAUSE flag is on, you should hit enter after you run a query to see a first result.
Compute table size
SQL> SELECT segment_name, bytes/(1024*1024) AS Mb FROM user_segments WHERE segment_type = 'TABLE' ORDER BY 2 DESC;
Spool table content to file
#!/bin/bash
#
## Configuration
#
# Don't forget to edit credentials, filename and query, see below.
#
## How to launch
#
# $ chmod +x spool_Oracle_table.sh
# $ nohup spool_Oracle_table.sh > spool_Oracle_table.nohup &
###
sqlplus -S /nolog <<EOF > /dev/null
-------------------------------------------
-- Set your credentials here
-------------------------------------------
CONN Oracle_user/Oracle_password@Oracle_sid
-------------------------------------------
SET ARRAY 100
SET PAGES 0
SET WRAP OFF
SET FLUSH OFF
SET FEED OFF
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SET TERM OFF
SET TRIMSPOOL ON
SET HEAD OFF
SET TRIM ON
SET COLSEP "|"
SET LINESIZE 800 --> this value should be enough otherwise rows will be truncated
--------------------------------------------------------------------
-- edit you filename here
SPOOL filename.dat
--------------------------------------------------------------------
-- your spool query here, something like
-- SELECT
-- col1
-- || '|' || col2
-- || '|' || TRIM(col3_varchar)
-- || '|' || TO_CHAR(col3_date, 'yyyy-mm-dd')
-- FROM table;
SPOOL OFF
EXIT
EOF
Edit SQL buffer
Suppose you write a wrong query and you want to fix it.
SQL> SELECT
2 sydate
3 FROM DUAL;
SYDATE
*
*ERROR at line 2:
ORA-00904: "SYDATE": invalid identifier
Go to line 2
SQL> 2
2* sydate
Apply a substitution
SQL> c/sydate/SYSDATE/
2* sysdate
Run the query
SQL> r
1 SELECT
2 SYSDATE
3* FROM DUAL
SYSDATE
------------------
02-APR-15
Format query output
If you try the following query without formatting output, you will get something unreadable.
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
Try this
SQL> SET LINES 200
SQL> COL parameter FOR a25
SQL> COL name FOR a50
Then run it again
SQL> r
Note that 25 is enough for parameter field, in fact
SQL> SELECT MAX(LENGTH(parameter)) len FROM NLS_DATABASE_PARAMETERS;
LEN
----------
23
Generate SQL
Sometimes it is really useful to launch a SQL that generates a SQL statement. For example in the example Format query output you can run.
SQL> SELECT 'COL parameter FOR a' || MAX(LENGTH(parameter)) copyme FROM NLS_DATABASE_PARAMETERS;
COPYME
--------------------------------------------------------------------------------
COL parameter FOR a23
to generate the COL parameter FOR a23 expression, and then just copy and paste it into your SQL prompt.
Launch a script
SQL> @script.sql
if script.sql is in the same directory you launched sqlplus.
You can pass an absolute path
SQL> @/path/to/script.sql
and even a name with spaces, by adding double quotes
SQL> @"001 - My script.sql"
Note that if there is an ampersand (&) you will be prompted to insert a value,
since it is the syntax for interactive variable assignations. Unluckly, if there is
any string in your insert that contains an ampersand, sqlplus will recognize it as a variable,
so you will be prompted for its assignation. You can solve it by setting
SET DEFINE OFF
in your script or in the sqlplus session.