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"