Release 2 (9.2.0.1.0) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, and Sun Solaris
Part No. A97297-01
 Contents |
 Index |
|
Oracle9i Administrator's Reference
Release 2 (9.2.0.1.0) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, and Sun Solaris Part No. A97297-01 |
|
This chapter describes how to use and administer SQL*Plus and iSQL*Plus on Oracle9i. It contains the following sections:
|
See Also: SQL*Plus Users Guide and Reference for more information on iSQL*Plus and SQL*Plus. |
This section describes how to administer command-line SQL*Plus. In the examples in this section, SQL*Plus uses the value of the ORACLE_HOME environment variable wherever a question mark (?) appears.
When you start SQL*Plus, it executes the glogin.sql site profile set-up file and then executes the login.sql user profile set-up file.
The global site profile file is $ORACLE_HOME/sqlplus/admin/glogin.sql. If a site profile already exists at this location, it is overwritten when you install SQL*Plus. If SQL*Plus is removed, the site profile file is deleted.
The user profile file is login.sql. SQL*Plus looks for this file in the current directory, and then in the directories you specify using the SQLPATH environment variable. Set this environment variable to a colon-separated list of directories. SQL*Plus searches these directories for the login.sql file in the order they are listed.
The options set in the login.sql file override those set in the glogin.sql file.
|
See Also: SQL*Plus User's Guide and Reference for more information on profile files. |
Oracle9i provides the PRODUCT_USER_PROFILE table that you can use to disable the SQL and SQL*Plus commands that you specify. This table is created automatically when you choose an installation type that installs a starter database. A started database is not installed, and demonstration tables are not created, in the following circumstances:
If you select the Software Only database configuration option
If you do not select the Demo Schemas option during a Custom installation
|
See Also: Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems for information on installation options. |
To recreate the PRODUCT_USER_PROFILE table, run the $ORACLE_HOME/sqlplus/admin/pupbld.sql script in the SYSTEM schema:
$ORACLE_HOME/sqlplus/admin/pupbld.sql
For example, enter the following commands, where SYSTEM_PASSWORD is the password of the SYSTEM user:
$ sqlplus SYSTEM/SYSTEM_PASSWORD SQL> @?/sqlplus/admin/pupbld.sql
You can also recreate the PRODUCT_USER_PROFILE table manually in the SYSTEM schema using the $ORACLE_HOME/bin/pupbld shell script. This script prompts for the SYSTEM password. If you need to run the pupbld script without interaction, set the SYSTEM_PASS environment variable to the SYSTEM username and password. For example, enter the following command where SYSTEM_PASSWORD is the password of the SYSTEM user:
$ SYSTEM_PASS=SYSTEM/SYSTEM_PASSWORD; export SYSTEM_PASS
Oracle9i provides demonstration tables that you can use for testing. These demonstration tables are created automatically when you choose an installation type that installs a starter database. A started database is not installed, and demonstration tables are not created, in the following circumstances:
If you select the Software Only database configuration option
If you do not select the Demo Schemas option during a Custom installation
|
See Also: Oracle9i Sample Schemas guide for information on demonstration tables and the Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems for information on installation options. |
This section describes how to manually create and delete the EMP and DEPT demonstration tables.
Use the $ORACLE_HOME/sqlplus/demo/demobld.sql SQL script to create the EMP and DEPT demonstration tables. In SQL*Plus, you can use any username to run the demobld.sql script to create the demonstration tables in a schema. For example, enter:
$ sqlplus SCOTT/TIGER SQL> @?/sqlplus/demo/demobld.sql
You can also use the $ORACLE_HOME/bin/demobld shell script to run the demobld.sql script, as follows:
$ demobld SCOTT TIGER
Use the $ORACLE_HOME/sqlplus/demo/demodrop.sql script to drop the EMP and DEPT demonstration tables. In SQL*Plus, you can use any username to drop the demonstration tables from the user's schema. For example, enter:
$ sqlplus SCOTT/TIGER SQL> @?/sqlplus/demo/demodrop.sql
You can also use the $ORACLE_HOME/bin/demodrop shell script to run the demodrop.sql script, as follows:
$ demodrop SCOTT TIGER
This section describes how to install and remove the SQL*Plus command-line help.
|
See Also: SQL*Plus User's Guide and Reference for more information on the SQL*Plus command-line help. |
There are three ways to install the SQL*Plus command-line help:
Perform an installation that installs a starter database.
When you copy a starter database with pre-built datafiles as part of an installation, SQL*Plus automatically installs the SQL*Plus command-line help in the SYSTEM schema.
Install the command-line help manually in the SYSTEM schema using the $ORACLE_HOME/bin/helpins shell script.
The helpins script prompts for the SYSTEM password. If you need to run this script without interaction, set the SYSTEM_PASS environment variable to the SYSTEM username and password. For example, enter the following command where SYSTEM_PASSWORD is the password of the SYSTEM user:
$ SYSTEM_PASS=SYSTEM/SYSTEM_PASSWORD; export SYSTEM_PASS
Install the command-line help manually in the SYSTEM schema using the $ORACLE_HOME/sqlplus/admin/help/helpbld.sql script.
For example, enter the following commands, where SYSTEM_PASSWORD is the password of the SYSTEM user:
$ sqlplus SYSTEM/SYSTEM_PASSWORD SQL> @?/sqlplus/admin/help/helpbld.sql ?/sqlplus/admin/help helpus.sql
|
Note: Both thehelpins shell script and the helpbld.sql script drop existing command-line help tables before creating new tables.
|
To manually drop the SQL*Plus command-line help tables from the SYSTEM schema, run the $ORACLE_HOME/sqlplus/admin/help/helpdrop.sql script. For example, enter the following commands, where SYSTEM_PASSWORD is the password of the SYSTEM user:
$ sqlplus SYSTEM/SYSTEM_PASSWORD SQL> @?/sqlplus/admin/help/helpdrop.sql
iSQL*Plus is a browser-based interface that uses the SQL*Plus processing engine in the following three-tier model:
| Tier | Description |
|---|---|
| Client | iSQL*Plus user interface, typically a Web browser |
| Middle | iSQL*Plus server, Oracle Net, and Oracle HTTP Server |
| Database | Oracle9i database |
When you install Oracle9i, iSQL*Plus is enabled by default. This section describes how to disable and re-enable iSQL*Plus.To disable iSQL*Plus:
Log in to the system running the Oracle HTTP Server as the Oracle software owner (oracle).
Change directory to the Oracle HTTP Server configuration directory:
$ cd $ORACLE_HOME/Apache/Apache/conf
Open the oracle_apache.conf configuration file in any text editor.
Insert a comment character (#) before the directive that includes the isqlplus.conf configuration file, as follows:
#include "ORACLE_HOME/sqlplus/admin/isqlplus.conf"
In the preceding example, ORACLE_HOME is the path of the Oracle home directory.
Save the file and exit from the text editor.
Verify the changes by parsing the Oracle HTTP Server configuration file:
$ $ORACLE_HOME/Apache/Apache/bin/apachectl configtest
If necessary, fix any errors displayed by this command.
Restart the Oracle HTTP Server:
$ $ORACLE_HOME/Apache/Apache/bin/apachectl restart
To re-enable iSQL*Plus, repeat the previous procedure, but in step 4, remove the comment character (#).
You can change the values of the following iSQL*Plus server parameters in the $ORACLE_HOME/sqlplus/admin/isqlplus.conf configuration file:
| Parameter | Description |
|---|---|
| iSQLPlusNumberOfThreads | Sets the maximum number of simultaneous HTTP requests that can be handled by the iSQL*Plus server. |
| iSQLPlusLogLevel | Enables iSQL*Plus to maintain log files and determines to what level log files are maintained. The default log file location is $ORACLE_HOME/sqlplus/log/isqlplus/log.xml. iSQL*Plus must be able to write to the $ORACLE_HOME/sqlplus/log directory. This directory has owner write permissions. If iSQL*Plus is running as the nobody user and therefore cannot write to the log file, you must manually give iSQL*Plus write permissions on the log file. It is not recommended to grant world write permission to the file except for temporary testing or tracing purposes.
|
| iSQLPlusTimeOutInterval | Sets the time a session can be idle before it is expired. Timing out iSQL*Plus sessions helps reduce system load and maximize resources. |
| iSQLPlusHashTableSize | Sets the maximum number of concurrent iSQL*Plus sessions that can be handled by the iSQL*Plus server. The default value is derived from the value of the iSQLPlusNumberOfThreads parameter. |
| iSQLPlusConnectIdList | Sets a drop-down list of databases that users can access in iSQL*Plus, in place of the Connection Identifier text field on the Login screen. This allows greater security for iSQL*Plus servers in hosted environments. |
| iSQLPlusAllowUserEntMap | Controls whether iSQL*Plus permits users to change the SET MARKUP HTML ENTMAP and COLUMN ENTMAP settings. In HTML, entity mapping replaces characters of special significance with printable representations of those characters. By default, entity mapping is enabled, and the value of the iSQLPlusAllowUserEntMap parameter is set to none. This value prevents users from changing the entity mapping settings, and prevents the use of user-defined HTML in iSQL*Plus output.
If you set the value of the iSQLPlusAllowUserEntMap parameter to |
-idle-timeout
|
Sets the time the Oracle HTTP Server waits for results from iSQL*Plus. |
The following example shows a sample FastCgiServer directive in the isqlplus.conf file:
FastCgiServer ORACLE_HOME/isqlplus -initial-env iSQLPlusNumberOfThreads=20 -initial-env iSQLPlusTimeOutInterval=30 -initial-env iSQLPlusLogLevel=warn -idle-timeout 3600
In the preceding example, ORACLE_HOME is the path of the Oracle home directory.
This section describes security issues specific to iSQL*Plus.
|
See Also: SQL*Plus User's Guide and Reference for more information on iSQL*Plus security. |
Users can access iSQL*Plus in two ways:
As SYSDBA or SYSOPER privileged users
As normal users, without SYSDBA or SYSOPER privileges
To access iSQL*Plus as a SYSDBA or SYSOPER privileged user, you use a different URL to the URL you use to connect as a normal user. By default, the URL used by privileged users is protected using Oracle HTTP Server authentication. If required, you can also use Oracle HTTP Server authentication to protect the URL used by normal users. To access a protected iSQL*Plus URL, users must specify a username and password, which is separate from their Oracle username and password, before they can access the login screen. These usernames and passwords are stored in a password file. An empty password file for SYSDBA and SYSOPER privileged users is installed when you install Oracle9i. Before accessing iSQL*Plus as a privileged user for the first time, you must add one or more usernames and passwords to this password file.Similarly, if you want to use Oracle HTTP Server authentication for normal users, you must create a separate password file and add usernames and passwords to it. You must then modify the iSQL*Plus configuration file to use this file to authenticate normal users. The following sections describe how to add users to password files and if necessary, modify the iSQL*Plus configuration file.
To add usernames and passwords to a password file:
Log in to the system running the Oracle HTTP Server as the Oracle software owner (oracle).
Change directory to the $ORACLE_HOME/Apache/Apache/bin directory:
$ cd $ORACLE_HOME/Apache/Apache/bin
Enter one of the following commands to add a username and password to the appropriate password file:
|
Note: You do not need to create the password file for privileged users. This file,iplusdba.pw, is installed by default.
|
To create a password file for normal users and add a username and password to it, enter:
$ htpasswd -c $ORACLE_HOME/sqlplus/admin/filename.pw username
To add a username and password to an existing password file, enter:
$ htpasswd $ORACLE_HOME/sqlplus/admin/filename.pw username
In the previous examples, filename.pw is the name of the password file that you want to modify or create, and username is the username that you want to add. The filename of the privileged user password file is iplusdba.pw. For unprivileged users, Oracle Corporation recommends that you use the name iplus.pw for the password file.
When prompted, enter the password for the username you specified.
If you created a new password file, see the following section for information on how to configure Oracle HTTP Server to use it.
When you create a new password file for normal users, you must configure the Oracle HTTP Server to use this password file to authenticate users that access the iSQL*Plus URL. To configure the Oracle HTTP Server to use the new password file:
Log into the system running the Oracle HTTP Server as the Oracle software owner (oracle).
Change directory to the $ORACLE_HOME/sqlplus/admin directory:
$ cd $ORACLE_HOME/sqlplus/admin
Open the isqlplus.conf configuration file in any text editor.
Locate the following section in the file:
## Enable handling of all virtual paths beginning with "/isqlplus"#<Location /isqlplus> SetHandler fastcgi-script Order deny,allow # Comment "Allow ..." and uncomment the four lines "AuthType ..." # to "Require ..." if Oracle HTTP authentication access is required # for the http://.../isqlplus URL Allow from all #AuthType Basic #AuthName 'iSQL*Plus' #AuthUserFile ORACLE_HOME/sqlplus/admin/iplus.pw #Require valid-user</Location>
In this example, ORACLE_HOME is the path of the Oracle home directory
Modify this section as shown in the following example:
## Enable handling of all virtual paths beginning with "/isqlplus"#<Location /isqlplus> SetHandler fastcgi-script Order deny,allow AuthType Basic AuthName 'iSQL*Plus' AuthUserFile ORACLE_HOME/sqlplus/admin/filename.pw Require valid-user</Location>
In this example, ORACLE_HOME is the path of the Oracle home directory and filename.pw is the name of the password file that you created for normal users (typically iplus.pw).
Verify the changes by parsing the Oracle HTTP Server configuration file:
$ $ORACLE_HOME/Apache/Apache/bin/apachectl configtest
If necessary, fix any errors displayed by this command.
Restart the Oracle HTTP Server:
$ $ORACLE_HOME/Apache/Apache/bin/apachectl restart
After the Oracle HTTP Server restarts, users accessing the iSQL*Plus URL are prompted for the Oracle HTTP Server authentication username and password before the browser displays the login screen.
You can restrict access to databases from iSQL*Plus. When restricted database access is enabled, a drop-down list of available databases is displayed in place of the Connection Identifier text field on the Login screen. This allows greater security for iSQL*Plus servers in hosted environments. Connection identifiers are listed in the order defined in the iSQLPlusConnectIdList parameter.
You can edit the isqlplus.conf file to enforce restricted database access by changing the following line, where SID1, SID2, ... is a comma separated list of Oracle Net connection identifiers specifying permitted databases:
FastCgiServer ... -initial-env "iSQLPlusConnectIdList=SID1, SID2,..."
For example:
FastCgiServer ... -initial-env "iSQLPlusConnectIdList=ABC1, PROD2, DEV3"
|
See Also: "Editing the iSQL*Plus Configuration File" on page 3-7 for information on editing theisqlplus.conf file.
|
While no quotes or embedded whitespace is allowed in a connection identifier, quotes are required around the entire iSQLPlusConnectIdList= argument, as shown in the preceding example. Connection identifiers are not case sensitive, and each connection identifier listed in the argument should be identical to an alias defined in the tnsnames.ora file.
Once set, all connections made through the Login screen, all Dynamic Reports and any connections attempted with the CONNECT command are refused unless the connection is to one of the databases specified in the restricted list. Similarly, if you use the SET INSTANCE system variable, the connection identifier defined must match an entry in the iSQLPlusConnectIdList parameter or the connection is refused. If no connection identifier is given, or if the one given does not match an entry in the iSQLPlusConnectIdList parameter, the database connection is refused and the following error occurs:
SP2-0884: Connection to database database_name is not allowed
In the preceding example, database_name is the name of the database that you are trying to connect to.
This section describes how to use command-line SQL*Plus on UNIX systems.
If you enter an ED or EDIT command at the SQL*Plus prompt, the system starts an operating system editor, such as ed, emacs, ned, or vi. The PATH variable must include the directory where the editor executable is located.
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. You can define this variable in the glogin.sql site profile, the login.sql user profile, or define it during the SQL*Plus session. For example, to set the default editor to vi, enter:
SQL> DEFINE _EDITOR=vi
If you do not set the _EDITOR variable, the value of either the EDITOR or the VISUAL environment variable is used. If both environment variables are set, the value of the EDITOR variable is used. When _EDITOR, EDITOR, and VISUAL are not specified, the default editor is ed.
If you start the editor, SQL*Plus uses the afiedt.buf temporary file to pass text to the editor. You can use the SET EDITFILE command to specify a different filename. For example, enter:
SQL> SET EDITFILE /tmp/myfile.sql
SQL*Plus does not delete the temporary file.
Using the HOST command or an exclamation mark (!) 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 the Bourne shell (/bin/sh). If the shell cannot be executed, an error message is displayed.
To return to SQL*Plus, enter the exit command or press Ctrl+d.
For example, to execute one command, enter:
SQL>! command
In the preceding example, command represents the operating system command that you want to execute.
To execute multiple operating system commands from SQL*Plus, enter the HOST or ! command then press Return. SQL*Plus returns you to the operating system prompt.
This section describes SQL*Plus restrictions.
The default values for the SQL*Plus LINESIZE and PAGESIZE system variables 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.
If you set the SYSTEM_PASS environment variable to the username and password of the SYSTEM user, the output from the ps command might display this information. To prevent unauthorized access, enter the SYSTEM password only when prompted by SQL*Plus.
If you want to automatically run a script, consider using an authentication method that does not require you to store a password, for example, externally authenticated logins to Oracle9i. If you have a low security environment, you might consider using UNIX pipes in script files to pass a password to SQL*Plus, for example:
$ echo SYSTEM_PASSWORD | sqlplus SYSTEM @MYSCRIPT
Alternatively, enter the following lines at the command prompt:
$ sqlplus <<EOFSYSTEM/SYSTEM_PASSWORD SELECT ...EXITEOF
In the preceding examples, SYSTEM_PASSWORD is the password of the SYSTEM user.
|
 Copyright © 1996, 2002 Oracle Corporation All rights reserved |
|