当管理的数据库比较多的时候,在sqlplus里切换就是一件麻烦的事。要非常小心,以防出错。可以修改sqlplus的提示符:SQL>,把这个改成我们用户和实例名,这样就不容易出错。
先看一下Oracle自定义的参数:
SQL>define
DEFINE _DATE = "11-MAY-11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "dave1" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
我们可以使用这些定义的参数来设定相关信息,也可以使sql来拼接显示的信息。如:
SQL>set sqlprompt "_USER@ _CONNECT_IDENTIFIER >"
SYS@ dave1 >
但是这个方式在sqlplus关闭之后就没有了。 要想永久的保存,就需要修改配置文件glogin.sql,sqlplus在启动时,会读取该文件。 该文件位置:
$ORACLE_HOME/sqlplus/admin/glogin.sql
关于该文件的介绍,参照我的博客:http://blog.csdn.net/changyanmanman/article/details/6993635
方法一:
在$ORACLE_HOME/sqlplus/admin/glogin.sql文件里添加如下参数:
set sqlprompt "_USER@ _CONNECT_IDENTIFIER >"
然后打开sqlplus:
[oracle@rac1 admin]$sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 11 18:46:50 2011
Copyright (c) 1982,2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SYS@ dave1>
方法二: 使用sql语句拼接
在$ORACLE_HOME/sqlplus/admin/glogin.sql文件里添加如下参数:
set time on
set termout off --如果不加这句,每次都会显示下面查询的select结果集
column propmt_q new_value propmt_q
select upper(user)||''@''|| instance_name||''(''||host_name||'')'' as propmt_q from v$instance;
set sqlprompt ''&propmt_q> ''
在打开sqlplus,效果如下:
[oracle@rac1 admin]$sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 11 18:50:27 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
18:50:27 SYS@dave1(rac1)>select sysdate from dual;
SYSDATE
---------
11-MAY-11
18:51:27 SYS@dave1(rac1)>
把时间去掉:
set termout off --如果不加这句,每次都会显示下面查询的select结果集
column propmt_q new_value propmt_q
select upper(user)||''@''|| instance_name||''(''||host_name||'')'' as propmt_q from v$instance;
set sqlprompt ''&propmt_q> ''
[oracle@rac1 admin]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 11 18:55:06 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SYS@dave1(rac1)>
--===============================================================================================
我添加下面内容到glogin.sql文件中:
set termout off
column propmt_q new_value propmt_q
select upper(user)||'@'|| instance_name||'>'||s.sid||','||s.serial#||','||p.spid as propmt_q from v$session s,v$process p,v$mystat m,v$instance where s.sid=m.sid and s.paddr=p.addr and rownum=1;
set sqlprompt '&propmt_q> '
--=======================================
sqlplus中连接效果如下:
C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 3 22:36:33 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
set echo on
--设置运行命令是是否显示语句
--设置显示“已选择XX行”
set colsep |
--设置列与列之间的分割符号
set pagesize 10
--设置每一页的行数
set serveroutput on
--设置允许显示输出类似dbms_output
set heading on
--设置显示列名
set timing on
--设置显示“已用时间:XXXX”
set time on
--设置显示当前时间
set autotrace on
--设置允许对执行的sql进行分析
set verify off
--可以关闭和打开提示确认信息old 1和new 1的显示.
set colsep ''
--域输出分隔符
set linesize 4000
--输出一行字符个数,缺省为80
set pagesize 0
--输出每页行数,缺省为24,为了避免分页,可设定为0。
set num 16(或者set numwidth 16)
--输出number类型域长度,缺省为10
set trimspool on
--去除重定向(spool)输出每行的拖尾空格,缺省为off
set heading off
--输出域标题,缺省为on
set feedback off
--回显本次sql命令处理的记录条数,缺省为on
set termout off
--显示脚本中的命令的执行结果,缺省为on
set timing off
--显示每条sql命令的耗时,缺省为off
set trimout on
--去除标准输出每行的拖尾空格,缺省为off
set echo off
--显示start启动的脚本中的每个sql命令,缺省为on
set termout off;
必须写在.sql文件中进行。
spool e:a
set termout off
select * from student;
spool off
Example:
SET TERMOUT OFF
--显示脚本中的命令的执行结果,缺省为on