Find the instance, SID, database name and service name of an Oracle session
SQL queries to read the instance name, SID, database name, service name and session context, and the background processes shown by ps.
An Oracle database has several names that are easy to confuse. The database name identifies the database (its files). The instance is the set of background processes and memory, identified by its SID and instance name. Clients connect through service names. The queries below read each of these names from a session, and show the background processes that run for an instance.
Service names
select value from v$parameter where name like '%service_name%';
show parameter service_name;
select sys_context('userenv','service_name') from dual;
The service name used by the current session:
select SERVICE_NAME from gv$session where sid in (
select sid from V$MYSTAT);
If the result is SYS$USERS, the session is connected to the default service: no SERVICE_NAME was given in the connection string. The services offered by the database are listed by:
select name from V$SERVICES;
select name from V$ACTIVE_SERVICES;
Instance name, SID and database name
SHOW PARAMETER INSTANCE_NAME
SELECT INSTANCE FROM V$THREAD;
SELECT SYS_CONTEXT('USERENV','INSTANCE_NAME') FROM DUAL;
SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL;
select ora_database_name from dual;
select * from global_name;
select name from v$database;
Session context
SELECT sys_context ('USERENV','db_name') db_name FROM dual;
SELECT sys_context ('USERENV','server_host') server_host FROM dual;
SELECT sys_context ('USERENV','current_schema') current_schema FROM dual;
SELECT sys_context ('USERENV','session_user') current_user FROM dual;
SELECT sys_context ('USERENV','host') client_host FROM dual;
SELECT sys_context ('USERENV','ip_address') client_ip_address FROM dual;
All of them in one query:
set linesize 2000
SELECT sys_context ('USERENV','db_name') db_name,
sys_context ('USERENV','server_host') server_host,
sys_context ('USERENV','current_schema') current_schema,
sys_context ('USERENV','session_user') current_user,
sys_context ('USERENV','host') client_host,
sys_context ('USERENV','ip_address') client_ip_address
FROM dual;
Current session and user
select sid, serial#, username from v$session
where sid = (select sid from v$mystat where rownum = 1);
select user from dual;
ALTER SESSION SET CURRENT_SCHEMA = <schema_name>;
Display large parameters and DDL
set linesize 400
column NAME_COL_PLUS_SHOW_PARAM format A20
column TYPE format A20
column VALUE_COL_PLUS_SHOW_PARAM format A100
show parameter control_files;
SET LONG 1000;
select dbms_metadata.get_ddl('USER', 'APP_SCHEMA') FROM DUAL;
Background processes
On the operating system, the processes of an instance are listed with ps. The process named ora_<process>_<SID> belongs to the instance <SID>:
ps -afe | grep ora_
oracle 2565 1 0 12:54 ? 00:00:01 ora_pmon_ORCL
oracle 2583 1 0 12:54 ? 00:00:01 ora_dbw0_ORCL
oracle 2585 1 0 12:54 ? 00:00:01 ora_lgwr_ORCL
The second column of ps (PID) matches the SPID column of V$PROCESS:
SELECT spid, program, background FROM v$process;
| Process | Role |
|---|---|
ora_pmon_<SID> |
Process monitor: cleans up failed user processes, rolls back their uncommitted work and frees their resources |
ora_vktm_<SID> |
Virtual keeper of time: tracks wall-clock time, used as a time reference |
ora_diag_<SID> |
Diagnosability: hang detection and deadlock resolution |
ora_dbrm_<SID> |
Database resource manager: sets up resource plans |
ora_psp0_<SID> |
Process spawner: starts Oracle processes as needed |
ora_mman_<SID> |
Internal database tasks |
ora_dbw0_<SID> |
Database writer: writes modified buffers from the buffer cache to the datafiles (several writers can run) |
ora_lgwr_<SID> |
Log writer: writes the redo log buffer to the online redo log files |
ora_ckpt_<SID> |
Checkpoint: signals the synchronization of the datafiles and control files, which shortens recovery |
ora_smon_<SID> |
System monitor: instance recovery and cleanup of temporary segments and free extents |
ora_reco_<SID> |
Recoverer: resolves in-doubt transactions of distributed databases |
ora_mmon_<SID> |
Manageability monitor: alerts, AWR snapshots and statistics |
ora_mmnl_<SID> |
Manageability monitor lite: session history and metrics |
ora_d000_<SID> |
Dispatcher: receives requests from sessions in shared server mode and returns the results |
ora_s000_<SID> |
Shared server: runs the calls of shared server sessions |
ora_smco_<SID> |
Space management coordinator: runs space management tasks |
ora_fbda_<SID> |
Flashback data archiver: archives the history of tracked tables |
ora_cjq0_<SID> |
Job queue coordinator: starts the job slaves (jnnn) that run scheduled jobs |