ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sqlplus中灵活使用sqlprompt提示符

sqlplus中灵活使用sqlprompt提示符

原创 Linux操作系统 作者:keeptrying 时间:2012-04-09 22:38:58 0 删除 编辑

原文链接:http://warehouse.itpub.net/post/777/520127

当管理的数据库比较多的时候,在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 Database 10g 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.sqlsqlplus 在启动时,会读取该文件。 该文件位置:

$ORACLE_HOME/sqlplus/admin/glogin.sql

方法一:

$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

200,87,6076">SYS@tsid>200,87,6076>

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25744374/viewspace-720760/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-25

  • 博文量
    130
  • 访问量
    941314