Using SQL*Plus commands

Hello everyone,



In this post I will summarize some of useful sql*plus commands that i use frequently.

-- To run a OS command from withen SQL*Plus prompt [two methods]
> host os_command

> host
$ os_command
$ exit
---------------------------------------------------------------- #


-- To know which database you work with from OS
$ echo $ORACLE_SID        -- Unix
c:> echo %oracle_sid%    -- Linux
---------------------------------------------------------------- #


-- To know your corrent settings like db_name, user
-- Used when working with hundreds of databases
> define
_CONNECT_IDENTIFIED = "orcl"
_USER = "scott"
---------------------------------------------------------------- #


-- To connect to a different user without exiting SQL
> connect hr/hr
> conn scott/tiger
> conn hr
> connect sys as sysdba        -- "as" means using the special privs for that user
---------------------------------------------------------------- #


-- To get the last statement in the SQL buffer [list or l]
-- To run the last statement in buffer [/]
> select table_name from user_tables;
> list
  1* select table_name from user_tables
> /
---------------------------------------------------------------- #


-- To modify specific line from select on multiple lines
> select first_name, last_name, salary
  from employees
  order by first_name;

> list
  1 select first_name, last_name, salary
  2 from employees
  3 order by first_name;

-- to bring line number 3 to be current
> 3
  3 order by first_name

-- to change word by another
> c/first/last   
> l
select first_name, last_name, salary
  from employees
  order by last_name;
> /

-- to append more terms to a line
> 3
  order by lastname
> a  , first_name
  order by last_name, first_name
> /
---------------------------------------------------------------- #


-- To change date format
> conn scott/tiger
> select deptno, ename, job, hiredate, sal
  from emp order by 2;

hiredate
--------
23-MAY-95
23-JAN-99

> alter session set
  nls_date_format = 'mm/dd/yyyy';
---------------------------------------------------------------- #


-- To change column format [sal column]
> col sal format $999.99.00
---------------------------------------------------------------- #


-- To change column heading [sal, name columns]
> col sal heading Salary
> col ename heading "Employee | Name"     -- Appears above each other
---------------------------------------------------------------- #


-- To change column format [to be like another column] with different heading
> col comm like sal heading "commission"
---------------------------------------------------------------- #


-- To increase column width [using a#]
> col sal format a15
> col comm format a60
---------------------------------------------------------------- #


-- To remove column format
> col comm clear
---------------------------------------------------------------- #


-- To remove all columns format
> clear columns
---------------------------------------------------------------- #


-- To change output heading underline [--- to ===]
> set underline '='
---------------------------------------------------------------- #


-- To save select statement into file to be used as script
> select * from employees;
> list
> save all_emp.sql

-- To use this select from file [usignfile]
> @all_emp.sql

-- To save to specific directory
> save /u01/all_emp.sql
> @/u01/all_emp.sql
---------------------------------------------------------------- #


-- To spool output into file
> spool output.txt
> select * from employees;
> spool off

> host vi output.txt

-- To append to output file [spool but append don't overwrite]
> spool output.txt append
> select * from departments;
> spool off
---------------------------------------------------------------- #


-- To use operating system editor to edit SQL buffer
> select first_name, last_name
  from employees
  order by first_name;
 
> ed
Edit from editor, save & exit

-- To change default editor in os level
> define _editor
Define _editor = "Notepad"
> define = _editor = /full/path/to/editor/editor.sh
---------------------------------------------------------------- #


-- To be continued ISA

Regards
Mostafa