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:
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:
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/introduction-to-oracle-database.html#GUID-8F2EEEC8-0372-4419-88FF-7D77A9C0FCAD
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/introduction-to-oracle-database.html#GUID-AB84D6C9-4BBE-4D36-992F-2BB85739329F
Note also that global_name is only used with distributed databases.