[SQL*PLUS]Set Up Sql*Plus

Sql*Plus的重要性不用多说，但是由于Sql*Plus默认的设置往往使得结果显示不是那么友好，比如说linesize, pagesize。 因此，很多时候都要手动去重复设置这些参数。那么有没有一劳永逸的方法呢，glogin.sql就是答案！

 

Sql*Plus每次启动的时候都会自动运行文件glogin.sql中的设置，因此如果我们把常用的参数设置都放在这个文件里面，我们就不用每次都要重复去设置了。

 

glogin.sql 可以在目录 <ORACLE_HOME>\sqlplus\admin 下找到， 文件初始内容如下，

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--

 

 

我们可以把我们喜欢的设置放在这个文件中，下面是我的glogin.sql文件内容，仅供参考，

-- Notepad is the default editor in Windows, which is not very
-- good for SQL input, so I use notepad++ as the replacement.
define _editor="E:\Program Files\Notepad++\notepad++.exe"

-- set linesize, pagesize, long
-- The default values for these parameters are too small
set linesize 300
set pagesize 9999
set long 5000


-- By default, the serveroutput is off
-- Personally, I wanna keep it turned on always
-- The format wrapped elements causes SQLPLUS to preserve leading
-- whitespace which is very useful.
set serveroutput on size 1000000 format wrapped

-- Set signature prompt as username@database>

-- First turn off terminal output, so that SQLPLUS will not
-- print out the SQL result below
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> '

-- Last, turn on SQLPLUS terminal output
set termout on

 

 

可以检验下效果，

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

frank@ORCL> show linesize
linesize 300
frank@ORCL> show pagesize
pagesize 9999
frank@ORCL> show serveroutput
serveroutput ON SIZE 1000000 FORMAT WRAPPED
frank@ORCL> conn sys/sys as sysdba
Connected.
sys@ORCL>

 

sys@ORCL> host sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 6 22:48:23 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

idle>