The fundamental interface to an Oracle database is an Oracle command-line utility program called SQL*Plus. This utility is available at every Oracle site and it is a critical tool for virtually every Oracle user. Despite its wide use, few developers and DBAs know how powerful this tool can be. Below are the most popular SQL*Plus commands that one should dare to learn.
Starting/Exiting | |
sqlplus <username>[/password]@<net_service_name> [as sysdba | as sysoper] | Start an SQL*Plus session with SYSDBA or SYSOPER privileges connected to the Oracle Net database alias (@net_service_name). The password is also optional in the first command and if not written here it will not be echoed back to the user in clear text. |
exit or quit | Disconnect from the database and terminate SQL*Plus. |
Executing/Loading | |
exec<procedure_name> | Execute a stored procedure |
@<path_and_script_name> | Runs the SQL*Plus statements in the specified script. |
@@<path_and_script_name> | Similar to the previous statement but it is useful for running nested scripts because it looks for the specified script in the same path as the script which is was called. |
get<path_and_script_name> | This retrieves the contents of a file and places it in the SQL buffer. |
save<file_name> | Save the commands in the SQL buffer to a file. |
spo[ol][<path_and_file_name>] [CREATE | REPLACE | APPEND ]] |OFF|OUT] | Save SQL statements together with their output to a file or optionally sends the file to a printer. E.g.: spool c:\test.txt select * from dual; spo off spo c:\test.txt app select object_id from user_objects spo off |
Others | |
run (short: /) | List and run the command stored in the SQL buffer |
ed[it] | Edit the last command |
host | Issue an operating system command |
