What are v$database, SID, ServiceName, istance in Oracle 18c

instanceoracleoracle-18c

I have set up Oracle 18c (18.4) on Windows 10 on a company laptop (localhost should be the same as my company's domain name).
Once I've created user SYSTEM with password at the installation I tried to connect to DB with these settings:

hotsname: localhost
Port: 1521
database: XEPDB1 (connection with ServiceName)

Accessing to this, here the following query which I want do understand (having a detail description of what's have been requested to Oracle by receiving those results):

Many output are different from what I have expected (for example v$database with global_name).

Here follows the lsnrctl status (with my company domain obscured):

PS C:\WINDOWS\system32> lsnrctl status

LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 28-MAR-2020 08:43:00

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connessione a (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATO del LISTENER
------------------------
Alias                     LISTENER
Versione                  TNSLSNR for 64-bit Windows: Version 18.0.0.0.0 - Production
Data di inizio            23-MAR-2020 23:43:32
Tempo di attivitÓ           4 giorni 8 ore 59 min. 31 sec.
Livello trace             off
Sicurezza                 ON: Local OS Authentication
SNMP                      OFF
Servizio predefinito           XE
File di parametri listenerC:\app\sterragn\product\18.0.0\dbhomeXE\network\admin\listener.ora
File di log listener      C:\app\sterragn\product\18.0.0\diag\tnslsnr\MIL-GZSL2H2\listener\alert\log.xml
Summary table degli endpoint di ascolto...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=C:\APP\STERRAGN\PRODUCT\18.0.0\admin\XE\xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PCX-LAPTOP.domain.topLevelDomain.subDomain)(PORT=5520))(Presentation=HTTP)(Session=RAW))
Summary table dei servizi...
Il servizio "CLRExtProc" ha 1 istanze.
  L'istanza "CLRExtProc", stato UNKNOWN, ha 1 handler per questo servizio...
Il servizio "XE" ha 1 istanze.
  L'istanza "xe", stato READY, ha 1 handler per questo servizio...
Il servizio "XEXDB" ha 1 istanze.
  L'istanza "xe", stato READY, ha 1 handler per questo servizio...
Il servizio "bc0cc7179d094aa9b1c4b1ffb3e667da" ha 1 istanze.
  L'istanza "xe", stato READY, ha 1 handler per questo servizio...
Il servizio "xepdb1" ha 1 istanze.
  L'istanza "xe", stato READY, ha 1 handler per questo servizio...
Il comando Þ stato eseguito

Input:

SELECT NAME FROM v$database;  

Output:

NAME
-----
XE

Input:

SELECT * FROM v$instance;  

Output:

INSTANCE_NUMBER   INSTANCE_NAME   HOST_NAME   VERSION     VERSION_LEGACY  VERSION_FULL    STARTUP_TIME      STATUS    PARALLEL    THREAD#    ARCHIVER LOG_SWITCH_WAIT LOGINS  SHUTDOWN_PENDING    DATABASE_STATUS INSTANCE_ROLE      ACTIVE_STATE BLOCKED CON_ID  INSTANCE_MODE   EDITION FAMILY  DATABASE_TYPE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1             xe              PCX-LAPTOP  18.0.0.0.0  18.0.0.0.0      18.4.0.0.0      2020-03-23 16:15:57 OPEN    NO          1          STOPPED  [NULL]          ALLOWED NO                      ACTIVE      PRIMARY_INSTANCE   NORMAL       NO      0       REGULAR XE      [NULL]          SINGLE

Input:

select sys_context('userenv','instance_name') from dual;  

Output:

SYS_CONTEXT('USERENV','INSTANCE_NAME')
---------------------------------------
xe

Input:

select sys_context('userenv','db_name') from dual;

Output:

SYS_CONTEXT('USERENV','DB_NAME')
---------------------------------
XEPDB1

Input:

select ora_database_name from dual;

Output:

ORA_DATABASE_NAME
------------------
XEPDB1

Input:

select * from global_name;

Output:

GLOBAL_NAME
------------
XEPDB1

Best Answer

In Oracle terminology:

  1. a database is a set of physical files and V$DATABASE records some of this information: the database physical name is V$DATABASE.NAME
  2. a database instance is the set of running processes and related memory: database instance is identified by the instance name (the SID or ORACLE_SID environment variable) which is by default the same as the database name (but it can be different, you can try to change it by changing instance parameter instance_name).

This is the top level information that is valid if the database is a non-container database or a container database with pluggable databases. However container databases is a major change and explains why depending to which pluggable database you are connected sys_context('userenv','db_name') returns something diffrent i.e. the name of the current pluggable database. For each pluggable database there is by default a service name that has the same name as the pluggable database name.

For more information, you should really read the related Oracle Database Concepts Guide sections:

Note also that global_name is only used with distributed databases.