Simple Oracle command line

Since I’m forever forgetting my Oracle command line stuff, a note to myself for the most common things:

Logging in

sqlplus database_user@host

Will then be prompted for the password. Oracle being more user orientated than databases like MySQL where the separation from user and database is very distinct.

Exporting to a file Saving the output of a query into a text file for later analysis can be done using the spool command within the SQLPlus shell.

Spool on
Spool /tmp/my_output_file.txt
select * from sometable;
Spool off

Where select * from sometable; are your queries. To disable the header output you would need to add a line like this after enabling spooling:

set heading 

class='k'>off;

List tables

Use a query within the SQLPlus shell like this to see what tables your user has access to:

select TABLE_NAME from ALL_ALL_TABLES;

or

select * from user_objects where object_type = 'TABLE';

Describe tables

To see what the columns are in a given table issue a command within the SQLPlus shell thus:

desc table_name;

Written on 18 Aug 2009 and categorised in CommandLine and Database, tagged as SQL, oracle, and sqlplus

Home, Post archive