|Oracle8i Administrator's Reference
Release 3 (8.1.7) 64 Bit for SGI IRIX
Part Number A87435_01
This chapter describes how to use and administer SQL*Plus on Oracle8i. It contains the following sections:
This section describes how to administer SQL*Plus.
When you start SQL*Plus, it executes the
glogin.sql site profile setup file and then executes the
login.sql user profile setup file.
The global site profile file is the
$ORACLE_HOME/sqlplus/admin/glogin.sql directory. The default site profile is placed in the
$ORACLE_HOME/sqlplus/admin directory when SQL*Plus is installed. If a site profile already exists, it is overwritten. If SQL*Plus is de-installed, the site profile file is deleted.
The user profile file is
login.sql. SQL*Plus looks in the current directory, and then in the directories you specify until it finds the
login.sql file. You can specify the directories to search by setting the SQLPATH environment variable to a colon-separated list of directories.
For example, if the current directory is
/u02/oracle and SQLPATH is set to
/home:/home/oracle:/u01/oracle, SQL*Plus looks for the
login.sql file in the following order:
The options set in the
login.sql file override those set in the
During a Typical installation, the PRODUCT_USER_PROFILE table is created automatically. This table is used to disable the SQL and SQL*Plus commands you specify. To recreate this table, run the
$ORACLE_HOME/sqlplus/admin/pupbld.sql script in the SYSTEM schema.
For example, enter:
SQL*Plus uses the value of the ORACLE_HOME environment variable wherever a question mark ( ? ) appears.
SQL*Plus is shipped with demonstration tables that you can use for testing.
During a Typical installation, the user SCOTT and the demonstration tables are created automatically.
$ORACLE_HOME/sqlplus/demo/demobld.sql SQL script to create the demonstration tables. In SQL*Plus, you can use any user name to run the
demobld.sql file to create the demonstration tables in a schema. For example, enter:
You can also use the
$ORACLE_HOME/bin/demobld shell script to run the
$ORACLE_HOME/sqlplus/demo/demobld.sql script by entering:
$ORACLE_HOME/sqlplus/demo/demodrop.sql script to drop demonstration tables. In SQL*Plus, you can use any user name to drop the demonstration tables in the user's schema. For example, enter:
You can also use the
$ORACLE_HOME/bin/demodrop shell script to run the
$ORACLE_HOME/sqlplus/demo/demodrop.sql script by entering:
This section describes how to install and remove the help facility.
When you copy a starter database with pre-built datafiles as part of the Typical installation or as an option in Oracle Database Configuration Assistant, SQL*Plus automatically installs the Help Facility.
You can use the Oracle Database Configuration Assistant to create help tables when creating a database.
You can use the
$ORACLE_HOME/bin/helpins shell script to manually install the help facility. Before you run the script, set the SYSTEM_PASS environment variable to the SYSTEM schema name and password. For example, enter:
If the SYSTEM_PASS variable is not set, the
helpins scripts prompt you for the SYSTEM password and load the help data into the
SYSTEM schema. Run the
$ORACLE_HOME/sqlplus/help/helpbld.sql script with the
helpus.sql script to create the help facility tables. Enter the following:
For example, enter:
You can also run
$ORACLE_HOME/sqlplus/help/helpdrop.sql in SQL*Plus to manually drop the help facility tables in a schema. For example, enter:
This section describes how to use SQL*Plus.
If you enter an ED or EDIT command at the SQL*Plus prompt, the system starts an operating system editor, such as
vi. Your PATH variable must include the directory of the editor.
When you start the editor, the current SQL buffer is placed in the editor. When you exit the editor, the changed SQL buffer is returned to SQL*Plus.
You can specify which editor starts by defining the SQL*Plus
_editor variable. This variable can be set in
glogin.sql, in l
ogin.sql, or entered during a SQL*Plus session. For example, to set the default editor to
If you do not set the _editor variable, the value of either the EDITOR or VISUAL environment variable is used. If both are set, the EDITOR variable value is used. When _editor, EDITOR, and VISUAL are not specified, the default editor is
If you run the editor, SQL*Plus uses the
afiedt.buf temporary file to pass text to the editor. You can use the SET EDITFILE command to rename this file. For example, enter:
SQL*Plus does not delete the temporary file.
Using the HOST command or an exclamation point (!) as the first character after the SQL*Plus prompt causes subsequent characters to be passed to a sub-shell. The SHELL environment variable sets the shell used to execute operating system commands. The default shell is
/bin/sh. If the shell cannot be executed, an error message is displayed.
To return to SQL*Plus, enter exit or press Ctrl+D.
For example, to enter one command, enter:
In the preceding example,
command represents the operating system command you want to execute.
To enter multiple operating system commands from SQL*Plus, enter the HOST or ! command then press return. SQL*Plus returns you to the operating system prompt.
While running SQL*Plus, you can stop the scrolling record display and terminate a SQL statement by pressing [Ctrl]+[c].
The default extension name of files generated by the SPOOL command is .
lst. To change this extension, specify a spool file containing a period (.). For example, enter:
This section describes SQL*Plus restrictions.
The default values for SQL*Plus LINESIZE and PAGESIZE do not automatically adjust for window size.
UNIX return codes use only one byte, which is not enough space to return an Oracle error code. The range for a return code is 0 to 255.