Vordefinierte Variablen in SQLPlus verwenden

SQL*Plus ist oftmals das Werkzeug der Wahl, um schnell ein paar Dinge mit einer Oracle Datenbank auszuprobieren und natürlich um alle möglichen administrativen Tätigkeiten durchzuführen. Dazu ist es wichtig, dass man es an seine Bedürfnisse anpassen kann, um es möglichst optimal verwenden zu können. Daher gibt es eine ganze Reihe von Einstellungen, die man vornehmen kann. Auf zwei Dinge möchte ich in diesem Blog eingehen. Das Erste ist die Anpassung des Prompts. Normalerweise steht da schlicht "SQL>" was wenig hilfreich ist. Seit Oracle Version 9.2 gibt es vordefinierte Variablen im SQL*Plus, die auch zur Anpassung des Prompts verwendet werden können. Hier bieten sich die Variablen "_USER" und "_CONNECT_IDENTIFIER" an, vielleicht auch noch "_PRIVILEGE". Damit lässt sich der verbundene Datenbankbenutzer und die Datenbank direkt im Prompt darstellen, ganz ähnlich zu den bekannten Linux-Prompts. Am besten trägt man diese Einstellung direkt in der Datei ORACLE_HOME/sqlplus/admin/glogin.sql ein, denn diese wird bei jeder neuen Datenbankverbindung automatisch aufgerufen und die gewünschten Einstellungen so aktualisiert.

[oracle@db23ctest ~]$ tail -1 $ORACLE_HOME/sqlplus/admin/glogin.sql
set sqlprompt "_USER'['_CONNECT_IDENTIFIER]> "

[oracle@db23ctest ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Sat Jan 20 08:07:42 2024
Version 23.3.0.23.09

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


Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SYS[DB23C]> 

Man sieht also direkt den Nutzer und die Datenbank. Aber aufpassen, SQL*Plus scheint bei einer passwortlosen Anmeldung nur den Namen der Container Datenbank anzuzeigen, auch wenn man durch ORACLE_PDB_SID direkt in eine Pluggable Database wechselt.

[oracle@db23ctest ~]$ env | grep ORA
ORACLE_UNQNAME=DB23C_db23test
ORACLE_SID=DB23C
ORACLE_HOME=/u01/app/oracle/product/23.0.0.0/dbhome_1
ORACLE_PDB_SID=DB23C_PDB1

[oracle@db23ctest ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Sat Jan 20 08:07:42 2024
Version 23.3.0.23.09

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


Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SYS[DB23C]> 

Natürlich gelten diese Einstellungen nicht nur für SQL*Plus sondern auch für das SQLcl, dass ja kompatibel ist. Hier wird im Gegensatz zum SQL*Plus auch direkt der Name der PDB angezeigt, sofern ORACLE_PDB_SID gesetzt wurde. Ansonsten wird der Root-Container angezeigt statt des Datenbanknamens.

[oracle@db23ctest ~]$ sql / as sysdba


SQLcl: Release 23.2 Production on Mon Jan 22 16:01:19 2024

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


Session altered.

SP2-0246: Illegal FORMAT string "999g999g999g999d0000"
Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SYS[CDB$ROOT]>

[oracle@db23ctest ~]$ export ORACLE_PDB_SID=DB23C_PDB1
[oracle@db23ctest ~]$ sql / as sysdba


SQLcl: Release 23.2 Production on Mon Jan 22 16:03:25 2024

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


Session altered.

SP2-0246: Illegal FORMAT string "999g999g999g999d0000"
Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SYS[db23c_pdb1]>

Aufpassen muss man vor allem auch bei der Verwendung von "ALTER SESSION SET CONTAINER ....", denn das ändert nicht den Prompt. Die Datei "glogin.sql" wird eben nur nach dem Aufbau einer Datenbankverbindung aufgerufen. Das ist entweder direkt beim Start von SQL*Plus oder durch den Aufbau einer neuen Verbindung mittels "CONNECT ...".

Eine weitere interessante Standardvariable ist "_SQLID". Die SQL-ID wird oftmals benötigt um den Ausführungsplan für Tuning- oder Analysezwecke zu ermitteln. SQL*Plus kann die SQL-ID zu jedem ausgeführten Statement direkt mit ausgeben. Dabei wird dann auch gleich die Variable mit dem entsprechenden Wert befüllt und man kann damit weiterarbeiten.

SYS[db23c_pdb1]> set feedback on sql_id
SYS[db23c_pdb1]> select * from dual;

DUMMY
________
X

1 row selected.

SQL_ID: a5ks9fhw2v9s1
SYS[db23c_pdb1]> prompt &_SQL_ID
a5ks9fhw2v9s1
SYS[db23c_pdb1]> select * from table(dbms_xplan.display_cursor(sql_id => '&_SQL_ID.'));
old:select * from table(dbms_xplan.display_cursor(sql_id => '&_SQL_ID.'))
new:select * from table(dbms_xplan.display_cursor(sql_id => 'a5ks9fhw2v9s1'))

PLAN_TABLE_OUTPUT
_____________________________________________________________________________
SQL_ID  a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 rows selected.

SQL_ID: gkb3n3phumqxg

Gerade für Skripte kann das sehr praktisch sein, denn man spart sich dadurch das Copy & Paste der SQL-ID an die richtige Stelle in der nachfolgenden Analyse. Natürlich kann man noch viele weitere Dinge in die "glogin.sql" eintragen, beispielsweise die Breite oder die Seitengröße. Meine persönliche "glogin.sql" enthält folgendes:

alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';

set numformat 999g999g999g999d0000
set lines 200 pages 1000
set feedback on sql_id
set long 1000000

set sqlprompt "_USER'['_CONNECT_IDENTIFIER]> "

Der persönlichen Kreativität sind hier keine Grenzen gesetzt. Viel Freude beim Experimentieren.