TL;DR: Use the sqlprompt to identify the connected system.
Why? - My most frequent mistake is to run a command in the wrong window or against the wrong database. Hence I often double-check my schema, database, server, container, etc...
How? - Using SQL to find my relevant information (how else...) and to set the prompt accordingly.
The simplest version looks like this:
rem following defines prompt as user @ database
set heading off
set feedback off
spool sqlstart
SELECT 'set sqlprompt "' || user || ' @ ' ||global_name||' '
|| ' > "'
FROM global_name gn
/
spool off
@sqlstart.lst
set heading on
set feedback on
This little script can be used in either SQL*Plus or SQLcl and it will modify the prompt to show me the schema and the global_name of the database I am connected to. This trick is 25 years old, and still useful.
So far so good. But in the world of pdbs, containers and often-cloned system, I needed something more advanced. In many places, the sandboxes, dev-clones and test-environments will have the same username, the same schemaname, and the same global_name or db_name, thereby preventing my cunning script to distinguish between those system.
Hence over the years my simple prompt-gadget turned into something like this:
rem following defines prompt as :
rem user [schema] @ database @ host (env)
set heading off
set feedback off
spool sqlstart
SELECT 'set sqlprompt "'
|| user
|| decode ( user
, sys_context('userenv','current_schema') , ''
, ' ['|| sys_context('userenv','current_schema') || ']'
)
|| ' @ ' || global_name
|| ' @ '|| SYS_CONTEXT('USERENV','SERVER_HOST')
|| decode (SYS_CONTEXT('USERENV','SERVER_HOST')
, 'ip-nnn-nn-2-109', ' (PROD)'
, 'ip-nnn-nn-0-226', ' (ACC)'
, 'ip-nnn-nn-0-23', ' (SE)'
, '98b6d46dd637', ' (XE)'
, '98eac28a59c0', ' (23c-demo)'
, ' (-chk env-)')
|| ' > "'
FROM global_name
;
spool off
@sqlstart.lst
set heading on
set feedback on
This version has a number of improvements:
It will check for current_schema, in case you are connected via a proxy-user.
it will check for server_host, which you can use to verify what server or (docker, k8s) container you are running from.
it has a decode that you can edit to provide better information about the system you are connected to.
In my case, if the default value of "-chk env-" appears, this means I am connected to a (new) database that is not yet "known to this script". If I want to clarify what this new system is about, I can go into the script and add a line to the decode to identify this database.
In this example, you can see that the last added line was to use the container (98ea....) to tell me that this is my latest 23c version.
(I have messy versions of the script with long lists of decode...)
In other versions, I have used v$database and v$instance to add more items to the prompt, and colleagues have used ANSI-escape codes to modify the colors of their terminal windows (red = danger... ).
Nothing will stop you from modifying this script to provide the information that You find relevant.
