2.9 Miscellaneous Commands

ALIAS [<name>=<SQL statement>;| LOAD [<filename>]|SAVE [<filename>] | LIST [<NAME>] | DROP <name> | DESC <name>  <Description String>]

Alias is a command which allows you to save a SQL, PL/SQL or SQL*Plus script and assign it a shortcut command.

  • ALIAS — Print a list of aliases

  • ALIAS LIST <alias_name> — List the contents of the alias

The following example shows how to create a simple alias:

SQL> ALIAS action1=select :one from dual;

Note:

Define an alias simply by using the alias keyword followed by a single identifier name followed by an '='. Anything after the '=' will be used as the alias contents. If it is SQL, it will be terminated by ';'. If it is PL/SQL, it will be terminated by '/'.

APEX

Lists Application Express Applications. Use APEX EXPORT <app id> to export the application which could be combined with spool for writing to a file.

ARCHIVE LOG LIST

Displays information about redo log files.

BRIDGE <targetTableName> as "<jdbcURL>"(<sqlQuery>);

Used mainly to script data move between two connections/schemas. It also includes functionality to dynamically create Oracle tables which "fit" the data being received through JDBC. The following functionality is available:

  1. Query tables in other connections

  2. Query tables in multiple connections in the same statement

  3. Insert data from one connection into another

  4. Create a table and insert data into it from another connection

CTAS table new_table

Uses DBMS_METADATA to extract the DDL for the existing table, then modifies that into a create table as select * from.

DDL [object_name [type] [SAVE filename]]

Generates the code to reconstruct the object listed. Use the type option for materialized views. Use the SAVE option to save the DDL to a file.

DESC[RIBE] {[schema.]object[@connect_identifier]}

Lists the column definitions for a table, view or synonym, or the specifications for a function or procedure.

FIND [<filename>]

Searches the SQLPATH and its directories for the specified file name. FIND where <filename> lists all the SQLPATH locations where it finds files matching the specified file name.

INFO[RMATION] {[schema.]object[@connect_identifier]}

Lists more detailed information about the column definitions for a table, view or synonym, or the specifications for a function or procedure.

Note:

INFORMATION+ will show column statistics.

OERR <facility> <error>

Displays information about errors. Facility is identified by the prefix string in the error message. For example, if you get ORA-7300, "ora" is the facility and "7300" is the error. So you should type "oerr ora 7300".

SSHTUNNEL <username>@<hostname> -i <identity_file> [-L localPort:Remotehost:RemotePort]

Creates a tunnel using standard ssh options such as port forwarding like option -L of the given port on the local host will be forwarded to the given remote host and port on the remote side. It also supports identity files, using the ssh -i option. If passwords are required, they will be prompted for.

TNSPING <address>

The TNSPING utility determines whether the listener for a service on an Oracle Net network can be reached successfully.

WHICH

Searches the SQLPATH and its directories for the specified file name and prints the name of the first file matching the specified file name in the SQLPATH.

Other commands are: