Oracle 用 sqlprompt 修改 sqlplus 提示符 [轉]

zhouxianwang發表於2013-05-07

當管理的資料庫比較多的時候,在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)>


source:http://blog.csdn.net/tianlesoftware/article/details/6412769

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27036311/viewspace-759923/,如需轉載,請註明出處,否則將追究法律責任。

相關文章