Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

SQLPLUS的参数配置

24 Dec 2018 » Oracle

配置SQLPLUS的glogin参数文件

cd $ORACLE_HOME/sqlplus/admin
[oracle@db admin]$ vi glogin.sql
--添加如下参数

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 are 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'


define_editor=vim
set serveroutput on size 1000000
set trimspool on
set long 5000
set pagesize 40
column plan_plus_exp format a80 
column global_name new_value gname
set termout off 
define gname=idle
column global_name new_value gname
set sqlprompt '_user"@"_connect_identifier> '
--select upper(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
set linesize 120
!stty erase ^H

1.进入SQLPLUS的效果

[oracle@db admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on 星期一 12 24 15:49:51 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

连接到: 
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@oradb> 

2.使用vi编辑上一条sql命令

SYS@oradb> select instance_name from v$instance;

INSTANCE_NAME
----------------
oradb

SYS@oradb> ed                            //开启vi编辑模式
已写入 file afiedt.buf
select count(*) from v$instance        
/
~
~
:wq
  1* select count(*) from v$instance
SYS@oradb> /                            //执行上一条命令

  COUNT(*)
----------
         1

enjoy!just for fun