0

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
1

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.

| improve this answer | |
  • Means that sys_context('userenv','db_name') is the same as global_name and ora_database_name, am I correct? By the way thank for you response, my question was a little bit more "wide" since I have questioned about SID, Service Name and instances to differentiate also their behaviour related to my Input/Output. For example right now, by keeping the same connection Conf and chaging the ServiceName into SID (using the same XEPDB1) it gives me an error (like that there are no listener). Ok, but if I add the listener in listener.ora, means that i am connecting again to the same Istance and Service? – Aerox Apr 3 at 6:39
  • 1
    Yes sys_context('userenv','db_name'), global_name and ora_database_name are the same by default in a given pluggable database or in the root container. I dont' understand your question about listener and I recommand you have a look to the relevant section of the Concepts Guide: docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/…. With container/pluggable dbs everything is more complex. – pifor Apr 3 at 7:49
0

You are on Express Edition (XE) which uses the Multi Tenant feature.

  • SID for Container Database (CDB) is XE
  • SID for the Pluggable Database (PDB) is XEPDB1
| improve this answer | |
  • Yes, I know what I've installed, but I don't understand the differences between v$database which is XE, the global_name is XEPDB1, and the insance is XE, knowing that i'm connected with XEPDB1 as a ServiceName during connection – Aerox Mar 28 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.