Search This Blog

Total Pageviews

Tuesday, 20 July 2021

Oracle who am I whoami.sql


set feed off
alter session set nls_date_format='dd-mm-YYYY hh24:mi'; 
set linesize 300 pagesize 100 serveroutput on
 column "db details" format a300
select 
' Sysdate:-'                  || (select sysdate from dual)              	||
' \SERVER_HOST:-'              ||SYS_CONTEXT('USERENV', 'SERVER_HOST')   	|| 
' \DB_UNIQUE_NAME:-'           ||SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')	|| 
' \INSTANCE_NAME:-'            ||SYS_CONTEXT('USERENV', 'INSTANCE_NAME') 	||
--' \SERVICE_NAME:-'           ||SYS_CONTEXT('USERENV', 'SERVICE_NAME')  	||
' \PLATFORM_NAME:-'            ||(select platform_name from v$database)  	||
' \INSTANCE NUMBER:-'          ||INSTANCE_NUMBER                         	|| 
' \STARTUP_TIME:-'             || STARTUP_TIME                           	||
' \STATUS:-'                   ||  STATUS                                	|| 
'\ CONTAINER NAME:- '          ||sys_context('userenv','con_name')     	 	||
' \DATABASE ROLE:- '          ||sys_context('userenv','database_role')		||
' \CLIENT IP ADDRESS: '       ||sys_context('userenv','ip_address')			                        ||
'\OS USER: '                  ||sys_context('userenv','os_user')			                        ||
SYS_CONTEXT('USERENV', 'DB_DOMAIN') "db details"
from gv$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME');



with con name 

set linesize 300 pagesize 100 serveroutput on
column "db details" format a300
select
'CON_NAME:\'||sys_context('USERENV','CON_NAME')||'  user:\'||sys_context('userenv','session_user')||' session_id:\ '||'current_schema:\ '||sys_context('userenv','current_schema')||' instance_name:\ '||sys_context('userenv','instance_name')||
' database role: '||sys_context('userenv','database_role')||' os user:\ '||sys_context('userenv','os_user')||' client ip address:\ '||sys_context('userenv','ip_address')||' server hostname:\ '||sys_context('userenv','server_host')||' client hostname:\ '||sys_context('userenv','host') "db details"
from dual ;

No comments:

Oracle DBA

anuj blog Archive