FIND HOSTNAME FROM SQL*PLUS ORACLE

Check in static view DATABASE_SUMMARY

SQL> col SERVICE_NAME for a20
SQL> col HOST_NAME for a20
SQL> col DB_CHARACTERSET for a20
SQL> select * from DATABASE_SUMMARY;

NAME      SERVICE_NAME         HOST_NAME            DB_CHARACTERSET
--------- -------------------- -------------------- --------------------
ORCL19X   ORCL19X              orcl19x              AL32UTF8

Use bang character to use host commands from sqlplus

kish@exdbx<>!hostname
exdbadm01

You can filter the hostname from uname transparently

kish@exdbx<>!uname -a|awk '{print $2}'
exdbadm01

Use v$instance

kish@exdbx<>select host_name from v$instance;

HOST_NAME
----------------------------------------------------------------
exdbadm01

Use v$session to get machine details from sys

kish@exdbx<>select distinct machine from v$session where username='SYS';

MACHINE
----------------------------------------------------------------
exdbadm01

Use ASH to get the machine details

kish@exdbx<>select distinct machine from v$active_session_history where machine <> 'NULL';

MACHINE
----------------------------------------------------------------
exdbadm01

Use sys_context function to get the host from pseudo view

kish@exdbx<>select sys_context('userenv','host') as host from dual;

HOST
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exdbadm01

Use utl_inaddr function to invoke get_host_name

kish@exdbx<>select utl_inaddr.get_host_name as host from dual;

HOST
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exdbadm01

Use local or remote listener to get the host from tns entry

kish@exdbx<>show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string       (ADDRESS=(PROTOCOL=TCP)(HOST=
                                                 exdbadm01)(PORT=1521))

You can also define the hostname manually using define. In order to prevent from manually entering the variable everytime you exit sql*plus, you can set permanent changes to sql*plus using glogin.sql file from ORACLE_HOME

kish@exdbx<> DEFINE _HOSTNAME       = "EXDBX"

kish@exdbx<> def
DEFINE _DATE           = "31-JUL-21" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "exdbx" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1201000200" (CHAR)
DEFINE _HOSTNAME       = "EXDBX" (CHAR)

Set permanent variable for host

[oracle@orcl19x ~]$ locate glogin
/apps01/base/product/11.2.0/sqlplus/admin/glogin.sql
/apps01/product/12.1.0/dbhome_1/sqlplus/admin/glogin.sql
/data01/product/19.0.0/sqlplus/admin/glogin.sql



[oracle@orcl19x ~]$ cat /apps01/product/12.1.0/dbhome_1/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--

DEFINE HOSTNAME = "EXDBX"

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s