Analysis of environment variable setting in Sql*plus

Sql*plus environment can be set by login.sql or Glogin.sql script, may be a lot of beginners or not accustomed to the use of Sql*plus veteran do not know. Because in today's UI tools (Toad, PL/SQL Developer. In the age of Sql*plus, this command tool has been neglected and may only be used occasionally, but this does not prevent it from being used extensively by many DBAs or sql*plus enthusiasts.

Where the Glogin.sql file is a global settings file that is located under $oracle_home/sqlplus/admin, and the Login.sql file belongs to the personalization file, it can be located in any location. You can either set or not set through the SQLPATH environment variable.

sql* Plus starts with the Glogin.sql script running first, and then finds out if there is a login.sql file in the current directory, and if found, runs the script if the current directory does not exist login.sql. Find out if the SQLPATH environment variable is set, and if it finds it, it will execute the environment variable path Ogin.sql the script, or the search will stop continuing.

First of all, we look at the Glogin.sql file of Lushan face it! In fact, there are some formatting of the actual formatting of column headings and column data,

--
-Copyright (c) 1988, 2004, Oracle Corporation. All rights Reserved.
--
--NAME
--Glogin.sql
--
--DESCRIPTION
--Sql*plus Global Login "site profile" file
--
--Add Any sql*plus commands here, is to is executed when a
--User starts Sql*plus, or uses the Sql*plus CONNECT command
--
--USAGE
--This script is automatically run
--

--Used by Trusted Oracle
COLUMN RowLabel FORMAT A15

--Used for the SHOW ERRORS command
COLUMN Line/col FORMAT A8
COLUMN ERROR FORMAT A65 word_wrapped

--Used for the SHOW SGA command
COLUMN NAME_COL_PLUS_SHOW_SGA FORMAT A24
COLUMN UNITS_COL_PLUS_SHOW_SGA FORMAT A15
--Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT A36 HEADING Name
COLUMN value_col_plus_show_param FORMAT A30 HEADING value

--Defaults for SHOW RecycleBin
COLUMN origname_plus_show_recyc FORMAT a16 HEADING ' ORIGINAL NAME '
COLUMN objectname_plus_show_recyc FORMAT A30 HEADING ' RecycleBin NAME '
COLUMN objtype_plus_show_recyc FORMAT A12 HEADING ' OBJECT TYPE '
COLUMN droptime_plus_show_recyc FORMAT A19 HEADING ' DROP time '

--Defaults for SET AUTOTRACE EXPLAIN report
--These column definitions is only used when sql*plus
--is connected to Oracle 9.2 or earlier.
COLUMN id_plus_exp FORMAT 990 HEADING I
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT A60
COLUMN Object_node_plus_exp FORMAT A8
COLUMN other_tag_plus_exp FORMAT A29
COLUMN other_plus_exp FORMAT A44

--Default for XQUERY
COLUMN result_plus_xquery HEADING ' result Sequence '

If you want to customize some of the general environment variable settings, then you can set it in Glogin.sql, it is important to note that its effect is global. If you want to customize your environment variables, it's best to use Login.sql to set them.

If there is no loging.sql, we usually use Sql*plus to log into the database as follows:

[Email protected] monitoring]$ Sqlplus/as SYSDBA

Sql*plus:release 10.2.0.4.0-production on Fri 2 14:26:36 2013

Copyright (c) 1982, Oracle. All rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.4.0-production

Sql>

Create a new Loging.sql file in the current directory, set some variables:

Set Sqlprompt "_connect_identifier>"
Set PageSize 500
Set Linesize 138
Set Serveroutput on

_connect_identifier: Instance of Login
PageSize: Sets the number of lines to print per page, including the number of empty lines set by NewPage
Linesize: Sets the number of characters printed per line and prints 80 characters per line by default. You can set Linesize to avoid collapsing the display
.....

Note the following noticeable changes (red ones)
[Email protected] monitoring]$ Sqlplus/as SYSDBA

Sql*plus:release 10.2.0.4.0-production on Fri 2 14:33:44 2013

Copyright (c) 1982, Oracle. All rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.4.0-production

Apps>

If you want to display Sql*plus login username, instance, Datetie, then you can modify the Login.sql file.
Set Sqlprompt "[email protected] _connect_identifier >"
Set PageSize 500
Set Linesize 138
Set Serveroutput on

The login Sql*plus is now as follows:
[Email protected] monitoring]$ Sqlplus/as SYSDBA

Sql*plus:release 10.2.0.4.0-production on Fri 2 14:41:07 2013

Copyright (c) 1982, Oracle. All rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.4.0-production

[email protected] apps >

Of course you log in to Sql*plus in other directories (not the current directory), there is no red environment setting above. At this point if you need to log in to any directory Sql*plus can apply this setting, you need to set the SQLPATH environment variable.
[Email protected] ~]$ VI ~/.bash_profile
Export Sqlpath=/home/oracle/monitoring/db_com_sql: $ORACLE _home/sqlplus/admin

The following is a more generic Login.sql script:

Define_editor=vi
Set serveroutput on size 100000
Set Trimspool on
Set Long 5000
Set Linesize 120
Set pagesize 9999
Column Plan_plus_exp format A80
Column Global_name New_value gname
Set Termout off
Define Gname=idle
Column Global_name New_value gname
Select lower (user) | | ' @ ' | | substr (Global_name,1,decode (Dot,0,length (global_name), dot-1)) Global_name from (select Global_name,instr (Global_ Name, '. ') Dot from global_name);
Set Sqlprompt ' &gname> '
Set Termout on

Resources:

http://blog.csdn.net/tianlesoftware/article/details/6412769
Http://blog.sina.com.cn/s/blog_4fb09d6201008bve.html

Analysis of environment variable setting in Sql*plus