SQL Plus Commands - DBA References

SQLPlus COPY command

posted Sep 10, 2010, 7:13 AM by Sachchida Ojha

The SQL*Plus COPY command is one of the fastest ways of copying data between databases and schemas. This is also one of the few methods that will handle LONG columns correctly. Look at this example:

SQL> COPY FROM scott/tiger@db1 TO scott/tiger@db2 INSERT mytable USING select * from mytable;

Frequently used SQL Plus Commands

posted Sep 10, 2010, 7:09 AM by Sachchida Ojha [ updated Sep 10, 2010, 7:11 AM ]

ACCEPT - Get input from the user
DEFINE - Declare a variable (short: DEF)
DESCRIBE - Lists the attributes of tables and other objects (short: DESC)
EDIT - Places you in an editor so you can edit a SQL command (short: ED)
EXIT or QUIT - Disconnect from the database and terminate SQL*Plus
GET - Retrieves a SQL file and places it into the SQL buffer
HOST - Issue an operating system command (short: !)
LIST - Displays the last command executed/ command in the SQL buffer (short: L)
PROMPT - Display a text string on the screen. Eg prompt Hello World!!!
RUN - List and Run the command stored in the SQL buffer (short: /)
SAVE - Saves command in the SQL buffer to a file. Eg "save x" will create a script file called x.sql
SET - Modify the SQL*Plus environment eg. SET PAGESIZE 23
SHOW - Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc.
SPOOL - Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst
START - Run a SQL script file (short: @)

SQL*Plus tries to format data from the database into a human friendly format. This formatting can be disabled by issuing the following SET commands:
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET TAB OFF

OS variables to sqlplus

posted Sep 10, 2010, 7:05 AM by Sachchida Ojha

One can pass operating system variables to sqlplus using this syntax:

sqlplus username/password @cmdfile.sql var1 var2 var3

Parameter var1 will be mapped to SQL*Plus variable &1, var2 to &2, etc. Look at this example:

sqlplus scott/tiger @x.sql '"test parameter"' dual
Where x.sql consists of:
select '&1' from &2;
exit 5;

Single @ and double @@ in SQLPLUS

posted Sep 10, 2010, 7:01 AM by Sachchida Ojha

A single @ symbol runs a script in the current directory (or one specified with a full or relative path, or one that is found in your SQLPATH or ORACLE_PATH).

@@ will start a sqlplus script that is in the same directory as the script that called it (relative to the directory of the current script). This is normally used for nested command files. This technique is commonly used by scripts that call subscripts in the ?/rdbms/admin directory.

Set the SQL*Plus command prompt

posted Sep 10, 2010, 6:55 AM by Sachchida Ojha

From Oracle 10g SQL*Plus will attempt to execute glogin.sql and login.sql after each successful connection. This is handy if you want to change the sqlprompt to include the current user. Here is an example (g)login.sql file:
prompt Loading login.sql file.

set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER SQL>"

define _editor=vi

The following example scripts can be used to include the connected username and database name into the prompt:

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>