LLI Wiki — My Digital Knowledge Base

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

Further reading

Mastodon LLIMastodon SocialFosstodon