Oracle Sqlplus参数设置

　　oracle 11g在%oracle_home%/sqlplus/admin/下面会有一个glogin.sql文件，每次sqlplus启动的时候都会查找并执行这个文件，可以用这个文件来保存一些设置。读完glogin.sql这个文件之后，oracle还会查找login.sql这个文件，这是10G以及以上版本新添加的，这个文件通常被放置在%oracle_home%/sqlplus/admin/下sqlplus 每次启动前，会读login.sql（若有的话） ,然后才是glogin.sql，login.sql只对某特定用户生效，它的优先级高于glogin.sql，glogin.sql是全局设置。
　　所以,同一个环境变量，比如linesize ,glogin.sql 和login.sql 都有设置，则login.sql 中的设置起作用。glogin.sql 的文件位置固定，在$ORACLE_HOME/sqlplus/admin 下面login.sql的位置不固定，一般将login.sql放在运行sqlplus 的当前目录(用pwd查看当前目录)，即：/home/oralce。

serveroutput

dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。

pagesize

页面显示行数
show pagesize;
set pagesize 50;

linesize

行宽度默认是80
show linesize;
set linesize 100;

trimspool

set trimspool on
去除重定向（spool）输出每行的拖尾空格，缺省为off

　　修改页面显示和行的默认值，但是这种方法，在你下次进入Sql Plus的时候还要重新设定，很麻烦。所以可以修改默认设置。在Oracle的安装目录下找到glogin.sql，我安装的是Oracle 11，这个文件在11.2.0\dbhome\sqlplus\admin下，用搜索很容易找到。然后打开这个文件，

set serveroutput on size 1000000  
set trimspool on  
set long 5000  
set linesize 100  
set pagesize 9999  
set linesize 154  
col GROUP# for 99  
col THREAD# for 99  
col SEQUENCE# for 9999  
col MEMBER for a50  
col MEMBERS for 99  
col name for a35  
col username for a15  
col file_name for a45  
col tablespace_name for a30  
col MACHINE for a30  
col program for a34  
col sid for 9999  
col spid for a8  
col value for a50  
col COMMENTS for a50  
col object_owner for a20  
col object_name for a30  
col TABLE_OWNER for a20  
col TABLE_NAME for a30  
col COLUMN_NAME for a30  
col owner for a15;  
col table_owner for a15  
col index_type for a10  
col DB_BLOCK_SIZE for a20  
col UNDO_RETENTION for a30  
col sql_text for a74  
col text for a60  
col r_owner for a15  
col index_owner for a15  
col GRANTEE for a15  
col GRANTOR for a15  
col PRIVILEGE for a35  
col PROPERTY_VALUE for a50  
col LAST_RUN_DURATION for a28  

col DEST_NAME for a20  
col DESTINATION for a30  
col PLAN_TABLE_OUTPUT for a130  
col SCHEMA_NAME for a20  
col OCCUPANT_NAME for a30  
col MOVE_PROCEDURE for a35  
col DESCRIPTION for a50  
col p1 for a30  
col p2 for a30  
col p3 for a30  
col p3text for a46  
col p2text for a46  

col OPERATION for a50  
col OPTIONS for a50  
col statement for a88  
col object for a50  
col error for a35  
col ARCHIVE_NAME for a40  
col segment_name for a25  
col segment_type for a25  
col COMPONENT for a25  
col parameter1 for a35  
col parameter2 for a35  
col parameter3 for a35  

col sql_redo for a90  
col sql_undo for a90  

col log_user for a10  
col priv_user for a10  
col schema_user for a11  
col INTERVAL for a20  
col what for a48  
col seg_name for a30  
col SQL_REDO for a90  

col pval2 for a50  
col event for a50  

col dest_name for a30  

col p1 for 99999999999999  
col p2 for 99999999999999  
col p3 for 99999999999999  
col maxbytes for 999999999999999999  

col MODE_REQUESTED for a30  
col MODE_HELD for a30  

col OPERATION for a30  
col options for a30  

--调度相关  
COL JOB_NAME for A15  
COL JOB_CREATOR for A11  
COL PROGRAM_OWNER for A11  
COL PROGRAM_NAME FOR A12  
COL SCHEDULE_NAME FOR A15  
COL SCHEDULE_OWNER FOR A14;  
col SCHEDULE_TYPE for a13  
COL RESOURCE_PLAN FOR A13;  
COL JOB_CLASS FOR A20  
col START_DATE for a20  
col END_DATE FOR a19  
col PROGRAM_ACTION for a45  
col COMMENTS for a15  
col WINDOW_NAME for a20  
col REPEAT_INTERVAL for a22  
col DURATION for a20  
col NEXT_START_DATE for a18  
col DURATION for a8  
col LAST_START_DATE for a18  
col job_action for a10  
col job_type for a10  

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