sql*plus登录环境设定

最新推荐文章于 2024-05-03 09:35:55 发布

ctlkt20620

最新推荐文章于 2024-05-03 09:35:55 发布

阅读量150

点赞数

SQL*Plus Configuration

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.

[@more@]

SQL*Plus Configuration

Site Profile

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.

User Profile

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.

Modifying Your LOGIN File

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:

SET LINESIZE	Followed by a number, sets the number of characters as page width of the query results.
SET NUMFORMAT	Followed by a number format (such as $99,999), sets the default format for displaying numbers in query results.
SET PAGESIZE	Followed by a number, sets the number of lines per page.
SET PAUSE	Followed by ON, causes SQLPlus to pause at the beginning of each page of output (SQLPlus continues scrolling after you enter Return). Followed by text, sets the text to be displayed each time SQL*Plus pauses (you must also set PAUSE to ON).
SET SQLPROMPT	Followed by the connect information variable in the form: SET SQLPROMPT '&_CONNECT_IDENTIFIER > ' changes the SQL*Plus command-line prompt to display the SID of the database you are connected to.
SET TIME	Followed by ON, displays the current time before each command prompt.

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.

Storing and Restoring SQL*Plus System Variables

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.