sqlplus tips

After I tryed psql, I really hate sqlplus. Hey Oracle, you are a really big corporation, why you keep sqlplus so unusable? Don't you have money to pay your developers?

By the way, sometimes I need to use sqlplus, here is a set of tips I always forget.



See also http://www.orafaq.com/wiki/SQL*Plus_FAQ

How to login

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, Postgres and many more)

export EDITOR=vim

If you are already logged in sqlplus, you can do

SQL> DEFINE _EDITOR=vim

Connection syntax

$ sqlplus schema/password@sid
$ sqlplus schema/password

$ sqlplus /nolog
SQL> connect as sysdba -- if you can :)

Exit command

SQL> quit

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

Loading .... gist https://gist.github.com/fibo/6592631

Remember that, since PAUSE flag is on, you should hit enter after you run a query to see a first result.

Basic operations

Show command help

SQL> help index
SQL> help [topic]

Show sqlplus settings

SQL> SHOW ALL

Show current user

SQL> SHOW USER

Get table list

SQL> select * from user_objects where object_type = 'TABLE';

List table columns

SQL> DESC mytable

Get first n rows from a table

SQL> select * from foo where rownum < 100;

Find user's 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

Loading .... gist https://gist.github.com/fibo/6807322