Connection script

To start this blog I am sharing a script I like to use to certify in which database I am connected.

Most of the time you are connected in more than one Database. To be sure you are testing your scripts in a non-production environmente you need to query the V$instance or something similar.

I have this script that is located on $OH/dbs

Once the script is there it is visible to your Sqlplus.

wdb.sql (which database)

set serveroutput on
set linesize 500
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss'
/
set head off
Select '************************************'||chr(10)||
       ' PLATFORM : '||platform_name||chr(10)||
       ' HOST     : '||host_name||chr(10)||
       ' SID      : '||instance_name||chr(10)||
       ' VERSION  : '||version||chr(10)||
       ' STARTED  : '||startup_time||'('||status||')'||chr(10)||
       ' CREATED  : '||created||chr(10)||
       ' ROLE     : '||DATABASE_ROLE||chr(10)||
       ' ARC MODE : '||LOG_MODE||chr(10)||
       ' USER     : '||sys_context('USERENV','SESSION_USER')||chr(10)||
       '************************************'
from v$instance, v$database
/

set head on
set serveroutput off

This is the output: 

connect system@oab_dev
Enter password:
Connected.system@oabde> @wdb

Session altered.

************************************
 PLATFORM : Linux x86 64-bit
 HOST     : duck.com

 SID      : OABDE
 VERSION  : 11.2.0.3.0
 STARTED  : 04/10/2012 01:17:11(OPEN)
 CREATED  : 05/04/2012 13:04:44
 ROLE     : PRIMARY
 ARC MODE : NOARCHIVELOG
 USER     : SYSTEM
************************************

You can see a lot of infomation in a single command. 

Thank you.