Полезная информация

Oracle8 Administrator's Reference
Release 8.0.5 for Intel-LINUX

A66585-02

Library

Product

Contents

Index

Prev Next

4
Administering SQL*Plus on LINUX

Administering SQL*Plus

Setup Files

The setup files for SQL*Plus are glogin.sql, the global setup file which defines the site profile, and login.sql, which defines the user profile. The glogin.sql and login.sql files contain either SQL statements or SQL*Plus commands that you choose to execute at the beginning of each SQL*Plus session. When you invoke SQL*Plus, glogin.sql is read first, followed by login.sql.

The Site Profile

The Site Profile file is $ORACLE_HOME/sqlplus/admin/glogin.sql. SQL*Plus executes this command file whenever any user starts SQL*Plus and SQL*Plus establishes the Oracle connection. The default Site Profile is placed in $ORACLE_HOME/sqlplus/admin whenever SQL*Plus is installed. If a Site Profile already exists, it will be overwritten. An existing Site Profile is deleted whenever SQL*Plus is de-installed.

The User Profile

The User Profile file is login.sql. SQL*Plus attempts to execute this command file whenever any user starts SQL*Plus and SQL*Plus establishes the Oracle connection. The User Profile is run after the Site Profile. SQL*Plus always searches the current directory for the User Profile. The environment variable SQLPATH may be set to a colon-separated list of directories that SQL*Plus will search in order.

For example, if the current directory is /u02/oracle and SQLPATH is set as follows:

% echo $SQLPATH 
/home:/home/oracle:/u01/oracle 

SQL*Plus will first look for login.sql in the current directory /u02/oracle. If it is not found there, SQL*Plus will then look in /home, /home/oracle, and /u01/oracle, respectively.

Here is a sample login.sql file:

set echo off
set feedback 4
set pause on
set pause "PLEASE PRESS RETURN TO CONTINUE"
set message on
set echo on

The PRODUCT_USER_PROFILE Table

The SQL script $ORACLE_HOME/sqlplus/admin/pupbld.sql may be run as the user SYSTEM to create the Product and User Profile tables.

$ORACLE_HOME/sqlplus/admin/pupbld.sql may also be run using the shell script $ORACLE_HOME/bin/pupbld. To use this script, the environment variables ORACLE_HOME and SYSTEM_PASS must be set. SYSTEM_PASS must be set to the SYSTEM's username and password. For example:

% setenv SYSTEM_PASS SYSTEM/manager
% pupbld

Installing product user profile tables...

Product user profile tables installed.

pupbld.sql will only be run by the Installer during SQL*Plus installation if Create Database Objects was selected.

Demonstration Tables

SQL*Plus is shipped with demonstration tables that may be used for testing.

Default Install

If using Default Install and Create Database Objects, the user SCOTT and the demonstration tables will be created automatically.

Custom Install

When installing SQL*Plus using Custom Install, if Create Database Objects is selected and you answer 'Yes' to the prompt "Would you like to load the SQL*Plus Demo Tables?", the Installer will create the user SCOTT with the password TIGER and create the demonstration tables.

Creating Demonstration Tables Manually

The SQL script $ORACLE_HOME/sqlplus/demo/demobld.sql is used to create the demonstration tables. The file demobld.sql, may be run in SQL*Plus as any user to create the demonstration tables in that schema. For example:

% sqlplus scott/tiger 
SQL> @?/sqlplus/demo/demobld.sql 

$ORACLE_HOME/sqlplus/demo/demobld.sql may also be run using the shell script $ORACLE_HOME/bin/demobld as follows:

% demobld scott tiger

Deleting Demonstration Tables

The SQL script $ORACLE_HOME/sqlplus/demo/demodrop.sql is used to drop the demonstration tables. The file demodrop.sql may be run in SQL*Plus as any user to drop the demonstration tables from that user's schema. For example:

% sqlplus scott/tiger 
SQL> @?/sqlplus/demo/demodrop.sql

$ORACLE_HOME/sqlplus/demo/demodrop.sql may also be run using the shell script $ORACLE_HOME/bin/demodrop as follows:

% demodrop scott tiger


Note:

Both SQL scripts demobld.sql and demodrop.sql drop the tables EMP, DEPT, BONUS, SALGRADE, and DUMMY. You must ensure that a table does not exist with the same name in the desired schema prior to running either script or the table data will be lost.

 

Help Facility

Default Install

If using Default Install and Create Database Objects, the Help Facility is installed automatically.

Custom Install

When installing SQL*Plus, if Create Database Objects is selected and you answer 'Yes' to the prompt "Would you like to load the SQL*Plus Help Facility?", the Installer will create the Help Facility.

Installing the Help Facility Manually

The Help Facility may be installed manually using the shell script $ORACLE_HOME/bin/helpins. To use this script, the environment variables ORACLE_HOME and SYSTEM_PASS must be set. SYSTEM_PASS must be set to SYSTEM's username and password. For example:

$ setenv SYSTEM_PASS SYSTEM/manager 
$ helpins

	SQL*Loader: Release 8.0.5.0.0 - Production

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 828

SQL*Loader: Release 8.0.5.0.0 - Production

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 1024 
Commit point reached - logical record count 1207

SQL*Loader: Release 8.0.5.0.0 - Production

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 1024 
Commit point reached - logical record count 1304 
Commit point reached - logical record count 2328 
Commit point reached - logical record count 2724 
Commit point reached - logical record count 2835 

See Also:

Refer to the SQL*Plus User's Guide and Reference, and the README file, $ORACLE_HOME/sqlplus/doc/release.doc.

 

Using SQL*Plus

Using a System Editor from SQL*Plus

An ed or edit command entered at the SQL*Plus prompt calls a default operating system editor, such as ed, emacs, ned, or vi. Your PATH variable must include the directory of the editor.

The global default editor is usually set by the DBA in glogin.sql using the SQL*Plus _editor option. Override this setting by specifying an editor in login.sql. Both files are read by SQL*Plus at startup, the local file taking precedence. The _editor option can also be set during a SQL*Plus session, overriding the setting in either file.

If the _editor option is not set, the EDITOR and VISUAL environment variables specify the SQL*Plus editor. These variables are not set in glogin.sql or login.sql. They are set in a user startup file, or at the system prompt. If both are set, the EDITOR variable is used.

Setting the Order of the Editor

SQL*Plus searches for the default editor in this order:

  1. The _editor variable during a SQL*Plus session.
  1. The _editor variable in login.sql.
  2. The _editor variable in glogin.sql.
  3. The EDITOR environment variable.
  4. The VISUAL environment variable.

When none of these values are set, SQL*Plus uses ed.

Setting the _editor option

Set the SQL*Plus _editor option by adding the following line to the login.sql file:

define _editor=editor_name

where editor_name is a LINUX editor.

Setting Environment Variables

For the Bourne or Korn shell, set the default editor with an environment variable by entering:

$ LINUX_VAR=editor_name; export LINUX_VAR

For the C shell, set the default editor with an environment variable by entering:

% setenv LINUX_VAR editor_name

Environment variable syntax is explained in Table 4-1.

Table 4-1 Syntax for LINUX Environment Variables

LINUX_VAR

 

the EDITOR or VISUAL environment variable

 

editor_name

 

the LINUX editor (for example, vi or ed)

 

Default Settings

If you call the system editor, the current SQL buffer is placed in the edit buffer and all statements available to the editor can change the SQL statement. SQL*Plus uses the afiedt.buf temporary file. When you exit the editor, the changed SQL buffer is returned to SQL*Plus.

Running Operating System Commands from SQL*Plus

An exclamation point (!) in the first position after the SQL*Plus prompt indicates subsequent character strings are passed to a sub-shell. The SHELL environment variable selects the shell you use to execute operating system commands. The default shell is /bin/sh (sh). If the shell cannot be executed, an error message is displayed.

Use the following SQL*Plus commands to perform specific tasks:

Interrupting SQL*Plus

While running SQL*Plus:

Using the SPOOL Command

The default filename extension for files generated by the SPOOL command is .lst. To change the extension, specify a spool file containing a period (.).

For example:

SQL> SPOOL query.lis

Restrictions

COPY Command

The COPY command in SQL*Plus is supported without restrictions on different machines running the same version of the operating system.

COPY may also work between machines running different versions of the operating system. If COPY fails, test the connection using rcp or ftp. Restrictions in vendor-supplied networking software may prevent rcp, ftp, or COPY from functioning properly between systems.


Note:

The rlogin command does not send or receive large packets of data and is not an adequate test for connections.

 

If COPY does not function between systems, create a database link to the system and user ID indicating the table you want to copy. To do this, enter:

SQL> create table newtable as \
(SELECT * FROM table@database_link_name)

This selects the rows and columns from the original table on the remote system and enters them in the new table on the local system.

Resizing Windows

The default value for SQL*Plus LINESIZE is 80 and for PAGESIZE is 25. These variables do not automatically adjust for window size.

Return Codes

LINUX 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.




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index