SQLプロンプトにユーザ名などを出力する

SQL*Plusでプロンプト(頭にSQL>と毎回出るもの)を変更する方法について記載します。

ログインユーザ名と、接続情報を出力する

以下の文でログインユーザ名と、接続情報が出力されます。

$ export ORACLE_SID=orcl
$ sqlplus / as sysdba
SQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
SYS@orcl> 

ログイン時に自動で出力する

glogin.sqlを使用した出力

以下のファイルにset文を追記しておくことで、毎回接続時に自動で出力することが出来ます。

-- ファイル内容の確認
$ ls -l $ORACLE_HOME/sqlplus/admin/glogin.sql
$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
-- ファイルに追記
$ echo "set sqlprompt \"_USER'@'_CONNECT_IDENTIFIER> \"" >> $ORACLE_HOME/sqlplus/admin/glogin.sql
-- 追記されたことの確認
$ tail $ORACLE_HOME/sqlplus/admin/glogin.sql
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
-- ログイン時にプロンプトが設定されていることを確認
$ sqlplus / as sysdba
SYS@orcl>

その他の情報を出力する

事前定義変数の使用

仕組みとしては、SQL*Plusの事前定義変数を出力しています。
以下の文を実行することで事前定義変数を確認できます。

SQL> define
DEFINE _DATE           = "19-MAR-16" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1201000200" (CHAR)
SQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "
SYS@orcl AS SYSDBA> 

ユーザ定義変数の使用

変数の定義のため、ユーザの変数定義でも出力が可能です。

SQL> set sqlprompt "_USER'@'_DBNAME> "
-- _DBNAME という変数が定義されていない為、 _DBNAME とそのまま出力される
SYS@_DBNAME> 
-- _DBNAME に直接DB名を設定します
SYS@_DBNAME> define _DBNAME = orcl
SYS@orcl> 

ユーザ定義変数の使用（SELECTの結果を使用）

-- _DBNAME に SELECT 結果の内容を設定します
-- column を設定することで、 define_dbname という列を SELECT した場合、その値を _DBNAME という変数に指定します。
SYS@orcl> column define_dbname new_value _DBNAME noprint
SYS@orcl> select decode(SYS_CONTEXT('USERENV','CDB_NAME'),
  2               null, SYS_CONTEXT('USERENV','INSTANCE_NAME'),
  3               SYS_CONTEXT('USERENV','INSTANCE_NAME') ||'.'|| SYS_CONTEXT('USERENV','CON_NAME')
  4        ) define_dbname
  5 from dual;
-- _DBNAME に orcl.CDB$ROOT が設定される
SYS@orcl.CDB$ROOT> 
-- セッションを PDB に変更
SYS@orcl.CDB$ROOT> alter session set container=pdborcl;
SYS@orcl.CDB$ROOT> select decode(SYS_CONTEXT('USERENV','CDB_NAME'),
  2               null, SYS_CONTEXT('USERENV','INSTANCE_NAME'),
  3               SYS_CONTEXT('USERENV','INSTANCE_NAME') ||'.'|| SYS_CONTEXT('USERENV','CON_NAME')
  4        ) define_dbname
  5 from dual;
-- _DBNAME に orcl.PDBORCL が設定される
SYS@orcl.PDBORCL> 

glogin.sql使用時の注意点

glogin.sqlを使用して自動でSQL文を発行する場合、SQL*Plusでのログインか、CONNECT文の発行の度に実行されます。
alter session などで PDB を変更した場合ではglogin.sqlが実行されません。以下例になります。

$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
set sqlprompt "_USER'@'_DBNAME> "
set feedback off pagesize 0
col define_dbname new_value _DBNAME noprint
select decode(SYS_CONTEXT('USERENV','CDB_NAME'),
              null, SYS_CONTEXT('USERENV','INSTANCE_NAME'),
              SYS_CONTEXT('USERENV','INSTANCE_NAME') ||'.'|| SYS_CONTEXT('USERENV','CON_NAME')
       ) define_dbname
from dual;
set feedback on pagesize 1000 trim on linesize 120
$ sqlplus / as sysdba
-- glogin.sql が実行されるためプロンプトが変化する
SYS@orcl.CDB$ROOT> alter session set container=pdborcl;
-- alter session では glogin.sql が実行されないためプロンプトが変化しない
SYS@orcl.CDB$ROOT> 
-- connect による接続であれば、プロンプトが変化する
SYS@orcl.CDB$ROOT> connect sys/welcome1@orahost:1521/pdborcl as sysdba
SYS@orcl.PDBORCL> 

glogin.sqlを使用して自動でSQL文を発行する場合、DBが起動していない時にエラーが発生するので注意してください。
以下はDB停止中にSQL*PlusでSYSログインする時の例です。

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 19 02:23:00 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

select decode(SYS_CONTEXT('USERENV','CDB_NAME'),
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SYS@_DBNAME>