To select the tables you have in user tables

• SQL> select table_name from user_tables;

To describe a table

• SQL> desc emp;

To re-execute a command

• SQL> /

Note: the buffer can only hold one previous statement.

To find out what is in that buffer

• SQL> l

or

• SQL> list

To change part of your previous query in the buffer before running it

• SQL> c/<column-name>/<new-column-name>

To save the query u have in the buffer to a file

• SQL> save <filename>.sql

To execute what is in this query file

• SQL> @all_emp

Assuming you have made a lot of queries during a session and you want to save everthing at once

• SQL> spool <filename>.txt

Any other command you type will be stored inside the <filename>.txt. But before this can happen, you need to do this:

• SQL> spool off

So when you do the above, you can then exit sqlplus, and cat output.txt. The result will show you all the commands or work you have done while in sqlplus.

If you make new changes and you want to append it to <filename>.txt

• SQL> spool <filename>.txt append

To make changes to sql statement in an editor

• SQL> ed

To know what editor u are currently using to run ur sql statements

• SQL> define _editor

To change the sql statement editor to something else

• SQL> define _editor=path/to/your/editor

To switch between sqlplus and your host prompt

• SQL> host

To echo something

• SQL> prompt "Your text here"

To insert a variable during your sql queries in sqlplus

• SQL> select empno,ename,job,sal,comm,from emp where sal > &&x and comm > &&x

To query all tables in the database

• SQL> select table_name from all_tables;

To paginate your output

• SQL> set pause on

To permanently set the pagination variable

• SQL> host set sqlpath

The above command shows u where the SQLPATH variable is set to. So from here we can set our pagination variable in this path. In this dir is a file called login.sql. This file is used by each and every user on the database.

To find out more sqlplus commands

• SQL> help index

To change your password

• SQL> passw

To change the password of another user

• SQL> passw [username]

Note: You can only do this if u care logged in as the sysdba.

To show your controlfiles

• SQL> show parameter control_files

To suspend your database

• SQL> alter system suspend;

This is useful when u want to perform backups without IO interference

To know the status of your database

• SQL> select database_status from v$instance;

To get out of suspend state

• SQL> Alter system resume

Note: After inserting a record into the oracle db using the insert Data Manipulation Language, you will need to commit ur changes using the commit command.

To know your default tablespace

• SQL> select dafault_tablespace from user_users;