You may find yourself in an environment that contains hundreds of databases located on dozens of different servers. In such a scenario, you want to ensure that you do not run the wrong commands or connect to the wrong database, or both. When performing DBA tasks, it is prudent to verify that you are connected as the appropriate account and to the correct database. You can run the following types of SQL commands to verify the currently connected user and database:

SQL> show user;

SQL> select * from user_users; SQL> select name from v$database;

SQL> select instance_name, host_name from v$instance; SQL> show pdbs;

An efficient way of staying aware of your environment is to set your prompt automatically, via the login.sql script, to display user and instance information. This example manually sets the SQL prompt:

SQL> set sqlprompt ‘&_USER.@&_CONNECT_IDENTIFIER.> ‘

Here is what the SQL prompt now looks like:

SYS@mmdb23>

You can also use the SYS_CONTEXT built-in SQL function to extract information from the data dictionary regarding details about your currently connected session. The general syntax for this function is as follows:

SYS_CONTEXT(‘<namespace>’,'<parameter>’,[length])

This example displays the user, authentication method, host, and instance:

SYS@mmdb23> select sys_context(‘USERENV’,’CURRENT_USER’) usr

,sys_context(‘USERENV’,’AUTHENTICATION_METHOD’) auth_mth ,sys_context(‘USERENV’,’HOST’) host ,sys_context(‘USERENV’,’INSTANCE_NAME’) inst from dual;

USERENV is a built-in Oracle namespace. More than 50 parameters are available when you use the USERENV namespace with the SYS_CONTEXT function. See the Oracle SQL Language Reference, which can be freely downloaded from the Technology Network area of the Oracle website (https://docs.oracle.com/database) for a complete list of parameters.

Determining Your Environment’s Details

Sometimes, when deploying code through various development, test, beta, and production environments, it is handy to be prompted as to whether you are in the correct environment.

The technique for accomplishing this requires two files: answer_yes.sql and answer_no.sql. Here are the contents of answer_yes.sql:

— answer_yes.sql PROMPT PROMPT Continuing…

And here is answer_no.sql: — answer_no.sql PROMPT

PROMPT Quitting and discarding changes… ROLLBACK; EXIT;

Now, you can insert the following code into the first part of your deployment script; the code will prompt you as to whether you are in the right environment and if you want to continue:

WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; select host_name from v$instance; select name as db_name from v$database; SHOW user; SET ECHO OFF; PROMPT ACCEPT answer PROMPT ‘Correct environment? Enter yes to continue: ‘ @@answer_&answer..sql

If you type in yes, then the answer_yes.sql script will execute, and you will continue to run any other scripts you call. If you type in no, then the answer_no.sql script will run, and you will exit from SQL*Plus and end up at the OS prompt. If you press the Enter key without typing either, you will also exit and return to the OS prompt.