Each time we use sqlplus – we usually need check dataguard status, database role, and collect details on our system. This help us to be more proactive and collect data to leave the database in same status we started to work. A good approach is to get all details automatically without getting trash for our screen output. Same examples are below – I have been using this a couple of years and is very helpful in my day by day.

glogin.sql – update the file under $ORACLE_HOME/sqlplus/admin

set linesize 200
define _editor=vi
set serveroutput on size 1000000
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||instance_name||'('||host_name||')' global_name from v$instance;
--set sqlprompt '&gname> '
set termout on

column db_unique_name format a16
column database_role format a16
column db_version format a10
column db_status format a8
column db_compatible format a10
column db_version heading VERSION
column db_status heading STATUS
column db_compatible heading COMPATIBLE
select d.db_unique_name, d.database_role, d.open_mode, v.version db_version,
p.value db_compatible, v.status db_status
from gv$database d, gv$instance v, gv$parameter p
where p.name = 'compatible'
and d.inst_id=v.inst_id and v.inst_id = p.inst_id
;

col logins format a8
column instance_name format A14
column status format A8
column CONNECTED format A12
column host_name format A34
select host_name,instance_name,
status, to_char(startup_time,
'DD-MON-YYYY HH24:MI ') "Startup time",logins,
decode(instance_name,(select instance_name from v$instance) ,'CURRENT','NOT CURRENT') CONNECTED
from gv$instance
/