SQL*Plus User's Guide and Reference Release 9.2 Part Number A90842-01 |
|
This chapter explains how to configure your SQL*Plus command-line and iSQL*Plus environments. It has the following topics:
You may wish to set up your SQL*Plus environment in a particular way (such as showing the current time as part of the SQL*Plus command prompt) and then reuse those settings with each session. You can do this through two host operating system files, the Site Profile for site wide settings, and the User Profile for user specific settings. The exact names of these files is system dependent; see the Oracle installation and user's guide provided for your operating system for the precise name.
SQL*Plus supports a global Site Profile, a SQL*Plus script created by the database administrator. This file is generally named glogin.sql. SQL*Plus executes this script whenever any user starts SQL*Plus and SQL*Plus establishes the Oracle connection. The global Site Profile allows the DBA to set up SQL*Plus environment defaults for all users at a particular site; users cannot directly access the Site Profile.
The 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.
SQL*Plus also supports a User Profile, executed after the Site Profile. This file is generally named login.sql. SQL*Plus searches for the user profile in your current directory, and then the directories you specify with the SQLPATH environment variable. SQL*Plus searches this colon-separated list of directories in the order they are listed.
You can add any SQL commands, PL/SQL blocks, or SQL*Plus commands to your user profile. When you start SQL*Plus, it automatically searches for your user profile and runs the commands it contains.
A user profile is ineffective in the iSQL*Plus user interface context, and cannot be used to control the initial settings or behavior of an individual iSQL*Plus session.
You can modify your LOGIN file just as you would any other script. You may wish to add some of the following commands to the LOGIN file:
See the SET command for more information on these and other SET command variables you may wish to set in your SQL*Plus LOGIN file.
You can store the current SQL*Plus system ("SET") variables in a host operating system file (a script) with the STORE command. If you alter any variables, this script can be run to restore the original values. This is useful if you want to reset system variables after running a report that alters them.
To store the current setting of all system variables, enter
STORE SET file_name
By default, SQL*Plus adds the extension "SQL" to the file name. If you want to use a different file extension, type a period at the end of the file name, followed by the extension. Alternatively, you can use the SET SUFFIX command to change the default file extension.
To restore the stored system variables, enter
START file_name
If the file has the default extension (as specified by the SET SUFFIX command), you do not need to add the period and extension to the file name.
You can also use the @ ("at" sign) or the @@ (double "at" sign) commands to run the script.
To store the current values of the SQL*Plus system variables in a new script "plusenv.sql":
STORE SET plusenv
Created file plusenv
Now the value of any system variable can be changed:
SHOW PAGESIZE
PAGESIZE 24
SET PAGESIZE 60 SHOW PAGESIZE
PAGESIZE 60
The original values of system variables can then be restored from the script:
START plusenv SHOW PAGESIZE
PAGESIZE 24
Configuring iSQL*Plus is discussed in the following topics:
This section discusses options for configuring your web browser to use iSQL*Plus.
Your web browser needs to be configured to enable cookies. iSQL*Plus can be used without JavaScript, but you may find it better with JavaScript enabled.
Some web browsers may require you to either remove a MIME type definition or application association for files with a .SQL extension, or to create a MIME type or application association for files with a .SQL extension in order to load scripts into iSQL*Plus.
Some browsers may require you to set up a MIME type to be able to save scripts to your local machine. If iSQL*Plus opens a new window when you click "Save Script" on the Work screen instead of prompting you to enter a filename to save the script, then set up a MIME type
application/vnd.oracle-isqlplus.script
and configure it to allow you to save to disk. Also add
application/vnd.oracle-isqlplus.output
to allow you to use the Save to file option accessed through the Interface Options screen of iSQL*Plus.
For example, to set up an application association for files with a SQL extension in Netscape Navigator 4.7 for Windows NT:
If this is not set up in your web browser, you may get an error when you try to load scripts that iSQL*Plus cannot identify as text files.
Some configurations of proxy servers may affect the ability of the iSQL*Plus user interface to connect with the iSQL*Plus Server. If you cannot connect with the iSQL*Plus Server, a browser alert "Document contains no data" or "Server Interrupt Error" is displayed when you try to load the iSQL*Plus Login screen. If the Oracle HTTP Server has been started and this situation occurs, you should reconfigure your proxy server or create a proxy exception in your browser for the Oracle HTTP Server running iSQL*Plus. There are two examples of setting proxy exceptions following:
To configure the proxy exceptions setting in Netscape Navigator 4.7 for Windows
To configure the proxy exceptions setting in Microsoft Internet Explorer 5.0
Certain settings from a session are either retained or automatically filled in the next time you log in to iSQL*Plus from the same workstation:
Your username, password and Output preference are not saved by iSQL*Plus.
The iSQL*Plus Server is installed during Oracle9i Database installation on the middle tier. You can configure several parameters and settings in the iSQL*Plus Server.
Each iSQL*Plus login is uniquely identified, so you can:
iSQL*Plus supports this stateful behavior by storing session context information in the Oracle HTTP Server. You must ensure that your Oracle HTTP Server always routes HTTP requests to the same server, otherwise the session context will not be found. However, you may find it useful to start more than one Oracle HTTP Server to distribute user load across the multiple servers.
You can edit the isqlplus.conf configuration file directly to change settings for iSQL*Plus Server parameter settings such as:
iSQLPlusNumberOfThreads
iSQLPlusLogLevel
iSQLPlusTimeOutInterval
iSQLPlusHashTableSize
iSQLPlusConnectIdList
-idle-timeout
On Windows, the isqlplus.conf file is located in:
%ORACLE_HOME%\sqlplus\admin\isqlplus.conf
On UNIX, the isqlplus.conf file is located in:
$ORACLE_HOME/sqlplus/admin/isqlplus.conf
To change the value of an iSQL*Plus initialization parameter, edit the line associated with the parameter. The syntax of the line to be edited is shown in the following sections.
The iSQLPlusNumberOfThreads
parameter sets the number of threads enabled in the iSQL*Plus Server. Because each thread enables an HTTP request to be handled, this value sets the maximum number of simultaneous HTTP requests that can be handled by the iSQL*Plus Server.
When many users are executing long running queries, increasing the iSQLPlusNumberOfThreads
value may help performance. You can edit the iSQL*Plus configuration file, isqlplus.conf, to change the number of threads. The syntax of the line to change in the configuration file is:
FastCgiServer ... -initial-env iSQLPlusNumberOfThreads=n
Where n can have a minimum value of 1, and a maximum determined by machine resources. When no value is set, iSQLPlusNumberOfThreads
defaults to 20.
There should be no need to set iSQLPlusLogLevel to warn or debug unless instructed to by Oracle Support.
The iSQLPlusLogLevel
parameter determines whether logging is enabled to record iSQL*Plus Server messages, and the level of logging. Logging is useful to help resolve user problems. Logging can be turned off
, set to warn
level or set to debug
level, which provides the richest information.
iSQLPlusLogLevel
defaults to warn
if it is set to an invalid value or if it is not set. If the log file cannot be opened or written, it is set to off
. The default logfile location is %ORACLE_HOME%\sqlplus\log\isqlplus\log.xml
in Windows, and $ORACLE_HOME/sqlplus/log/isqlplus/log.xml in UNIX. A new log file is started when its size reaches 64 kilobytes. The old file is renamed with a unique name like log1006130580.xml. A maximum of 10 log files are kept, and older files are automatically deleted.
When logging is enabled, iSQL*Plus appends log data to an existing log file, or creates a new file if one does not exist. If there is any sort of write error that prevents writing to the logfile, iSQL*Plus starts, but does not create a log file or log any messages.
The log directory must be writable by iSQL*Plus. The log directory has owner write permissions. If iSQL*Plus is running as nobody
and therefore cannot write to the log file, the iSQL*Plus administrator 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.
Some abnormal errors may be written to the Oracle HTTP Server log file. These may include errors generated if the iSQL*Plus Server cannot be started. These errors are written even if iSQLPlusLogLevel
is OFF.
When iSQLPlusLogLevel is set to debug
, then process tracing information is logged.
You can edit the iSQL*Plus configuration file, isqlplus.conf, to change the log level. The syntax of the line to change in the configuration file is:
FastCgiServer ... -initial-env iSQLPlusLogLevel=[off|warn|debug]
Timing out iSQL*Plus sessions helps to reduce machine load and to maximize resources. The time out interval is set by the iSQLPlusTimeOutInterval
initialization parameter. It defines the time a session can be idle before it is expired. You can edit the iSQL*Plus configuration file, isqlplus.conf, to change the timeout interval. The syntax of the line to change in the configuration file is:
FastCgiServer ... -initial-env iSQLPlusTimeOutInterval=n
Where n is the number of whole minutes of idle time before the session times out. iSQLPlusTimeOutInterval
has a default of 30 minutes. It can be set to any value from 1 to 1440 minutes. It should not be set so small that users do not get a chance to enter their scripts.
When a user tries to use a timed out iSQL*Plus session, the Login screen is displayed and the user is prompted to log in again and the following error is displayed:
SP2-0864: Session has expired. Please log in again.
The number of hash table entries is defined by the iSQLPlusHashTableSize
initialization parameter. Each user session consumes one entry in the hash table even if it is idle. Its default value is derived from the value of the iSQLPlusNumberOfThreads
parameter value. However, iSQLPlusHashTableSize
can be set independently to tune the system.
You can edit the iSQL*Plus configuration file, isqlplus.conf, to change the hash table size. The syntax of the line to change in the configuration file is:
FastCgiServer ... -initial-env iSQLPlusHashTableSize=n
Where n is the number of entries in the hash table.
The restricted database parameter limits the databases that users can access in iSQL*Plus. When enabled, a dropdown 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 iSQLPlusConnectIdList
.
You can edit the iSQL*Plus configuration file, isqlplus.conf, to restrict database access. The syntax of the line to change in the configuration file is:
FastCgiServer ... -initial-env "iSQLPlusConnectIdList=SID1, SID2,..."
where SID1, SID2,, ... is a comma separated list of Oracle Net connection identifiers specifying permitted databases. For example:
FastCgiServer ... -initial-env "iSQLPlusConnectIdList=ABC1, PROD2, DEV3"
Note the use of quotes in the example. The opening quote must occur before the iSQLPlusConnectIdList
parameter name, the closing quote at the end of the list of SIDs.
For more information about restricted database access, see "Enabling or Disabling Restricted Database Access".
The FastCGI timeout parameter or idle timeout is the time the Oracle HTTP Server waits for results from iSQL*Plus. It is set to a value likely to prevent iSQL*Plus timing out before the web browser. It is sufficient for many long queries to return results before iSQL*Plus times out.
The idle timeout should not be confused with the iSQLPlusTimeOutInterval
which manages the lifetime of a user's session.
You can edit the iSQL*Plus configuration file, isqlplus.conf, to change the idle timeout value. The syntax of the line to change in the configuration file is:
FastCgiServer ... -initial-env -idle-timeout=n
Where n is the number of seconds of the Oracle HTTP Server waits for results from iSQL*Plus. The default value is 3600 seconds.
iSQL*Plus uses a cascading style sheet to control the format of the user interface. You can replace the default style sheet with your own style sheet. It is recommended that you do not delete the default style sheet, but rename it so that you can revert to it if necessary.
For example, to replace the cascading style sheet in Windows
%ORACLE_HOME%\sqlplus\admin\iplus\
directory.%ORACLE_HOME%\sqlplus\admin\iplus\
directory.By default, Oracle HTTP Server authentication is required for SYSDBA and SYSOPER privileged users. You may also want to limit who can access iSQL*Plus by requiring Oracle HTTP Server authentication for normal user logins. See "Enabling Server Authentication for Users" for more details.
To connect with SYSDBA or SYSOPER privileges, or to generate the iSQL*Plus Server Statistics report, your username and password must be added to the iSQL*Plus authentication file for the Oracle HTTP Server. See "Enabling DBA Access" for more details.
The Oracle HTTP Server is installed during Oracle9i Database installation on the middle tier. You can:
Additional iSQL*Plus configuration information must be included in the Oracle HTTP Server configuration file, httpd.conf, for the iSQL*Plus Server.
There are two layers of nested configuration files:
Changes are usually only made to:
After making changes to .conf files, check them to make sure there are no errors, and then stop and start the Oracle HTTP Server to implement the changes.
To check the Oracle HTTP Server configuration file, httpd.conf, and any included configuration files for errors. On Windows do the following steps:
cd %ORACLE_HOME%\Apache\Apache\conf
apache -t
Any errors in the configuration file are displayed. If there are any errors, edit the included configuration files, oracle_apache.conf or isqlplus.conf, again to correct them and then test again. If there are no errors, the message "Syntax OK" is displayed.
On UNIX, do the following steps:
cd $ORACLE_HOME/Apache/Apache/conf
apachectl configtest
Any errors in the configuration file are displayed. If there are any errors, edit the included configuration files, oracle_apache.conf or isqlplus.conf, again to correct them and then test again. If there are no errors, the message "Syntax OK" is displayed.
For changes to the Oracle HTTP Server configuration file, httpd.conf, and any included configuration files to take affect, you must stop and restart the Oracle HTTP Server if it is running. There is no convenient way to know how many users are currently accessing the server, so it is important to have the server down for the shortest time. When making changes to configuration files parse the httpd.conf configuration file and report any errors before starting and stopping the Oracle HTTP Server.
To stop and start the Oracle HTTP Server. On Windows do the following steps:
cd %ORACLE_HOME%\Apache\Apache
apache -k shutdown
apache -k start
On UNIX, do the following steps:
cd $ORACLE_HOME/Apache/Apache/bin
apachectl stop
apachectl start
You can edit the Oracle HTTP Server configuration file to disable iSQL*Plus.
To disable iSQL*Plus
On Windows:
cd %ORACLE_HOME%\Apache\Apache\conf
On UNIX:
cd $ORACLE_HOME/Apache/Apache/conf
On Windows:
# include "ORACLE_HOME\sqlplus\admin\isqlplus.conf"
On UNIX:
# include "ORACLE_HOME/sqlplus/admin/isqlplus.conf"
Where ORACLE_HOME is the path of your Oracle home directory.
To re-enable iSQL*Plus, reverse the edit to uncomment the include line in oracle_apache.conf and restart the Oracle HTTP Server.
If you plan to connect to a remote database you need to ensure that you install and configure Oracle Net on the middle tier middle tier where the iSQL*Plus server is running. For further information about installing and configuring Oracle Net, see the Oracle9i documentation.
Oracle9i is installed on the database tier. The database tier may be physically separate from the middle tier and accessed using Oracle Net, or it may be the same physical machine as used by the middle tier. If you are using Oracle Net to access the database, then make sure the database listener is configured and running. For further information about installing and configuring Oracle9i, see the Oracle9i documentation.
The iSQL*Plus Extension for Windows is accessed through extended menus in Windows platforms. The iSQL*Plus Extension for Windows extended menu is accessed by right clicking files with the .SQL file extension.
If you have installed the Oracle9i Server, iSQL*Plus Server entries for your Oracle HTTP Server are created by default. If you have installed the Oracle9i Client, no iSQL*Plus Server entries are created. You can use the Configuration dialog to configure the available iSQL*Plus Servers that you want.
You can open the Configuration dialog by right clicking a .SQL file in Windows Explorer, then select iSQL*Plus Servers, and then Configure. The Configure iSQL*Plus Servers dialog is displayed.
When the column is narrower than the field it contains, tool tips show the full text in the field, but to edit or copy a field, make sure that the column width is wide enough to display the full field. You can widen the column by selecting and dragging the heading column separator.
You can sort the list of iSQL*Plus Servers in ascending or descending field name order by clicking the field name column heading. You can also manually sort the server definitions by using the up and down arrow buttons on the right side of the list of iSQL*Plus Servers.
Text description of the illustration iseconf.gif
Enter a valid iSQL*Plus Server URL in the form:
http://host.domain/isqlplus
This field is mandatory.
Enter a name for this iSQL*Plus Server. The name, if entered, is used in the context menu, otherwise the full iSQL*Plus Server URL is used.
Enter a connect string to specify your username and password and the database you want to connect to. If you omit a username and password you are prompted for them when iSQL*Plus starts. If you omit a database connection identifier or Oracle Net alias, you are connected to the default database.
Clear the Execute checkbox to change the action performed on the selected file. The default action is Execute, the alternative is Load. Execute runs the selected file in the iSQL*Plus Server and displays the results in your browser. Load opens the iSQL*Plus Server and loads the selected file into the Input area.
Click Add to add a new iSQL*Plus Server.
Click Remove to delete a selected iSQL*Plus Server definition.
Enter a path and arguments for the application you want to associate with the .SQL file extension when you double click. The default application is Windows Notepad. This definition does not affect the iSQL*Plus Server definitions.
Click Browse to find an application you want to associate with the .SQL file extension when you double click.
Select a language you want to use in the iSQL*Plus Extension from the dropdown list of available languages:
Brazilian Portuguese
English
French/Canadian French
German
Italian
Japanese
Korean
Simplified Chinese
Spanish/LA Spanish
Traditional Chinese
This language setting only affects the iSQL*Plus Extension, not iSQL*Plus.
Click OK to save your changes and close the Configure iSQL*Plus Servers dialog.
Click Cancel to quit your changes and close iSQL*Plus Extension. You are prompted once more before your changes are lost.
When you first open the Configure iSQL*Plus Servers dialog, the OK button is unavailable and the Cancel button is labelled Close. Once you make changes, OK is enabled and Close is renamed Cancel.
You must configure at least one iSQL*Plus Server before you can use the iSQL*Plus Server Extension. To add an iSQL*Plus Server, perform the following steps:
URL: << http://host.domain/isqlplus >> Name: << host >> Connect String: << username/password@connect_identifier >>
You must enter a URL for your iSQL*Plus Server, the other fields are optional. This may be different if iSQL*Plus Servers have been configured during an Oracle9i Server installation.
The new iSQL*Plus Server Name appears in the context menu. When you select a server from the context menu, the full iSQL*Plus Server URL is shown in the status bar.
To modify an iSQL*Plus Server, perform the following steps:
The modified iSQL*Plus Server name appears in the context menu.
Select the server you wish to remove from the list of available servers. The server's details will appear in the following text boxes. Click the Remove button to remove it from the list of available servers.
To remove an iSQL*Plus Server, perform the following steps:
The iSQL*Plus Server is removed from the context menu.
|
Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|