Oracle get database name

In this Oracle tutorial, we will learn about the oracle get database name in oracle database 19c & 21c. Also, we will cover these topics.

• Oracle get database name sql
• Oracle get database name from dual
• Oracle get database name from sys_context
• Oracle get global database name
• Oracle get database instance name
• Oracle get database service name
• Oracle get database timezone name
• Oracle get pluggable database name

Oracle get database name sql

Using v$database, you can get the name of all the databases in oracle database 19c & 21c. SQL developer tool displays more information than sqlplus.

The below script displays the database name and other information on the SQL developer tool.

SELECT * FROM V$DATABASE;

You can specify name instead of * in case you want the name only but this works only on the SQL developer tool.

The below image shows the output of the above query. It displays the name and other information of the database in the oracle database.

Also, read: Oracle Add Row Number

Oracle get database name from dual

DUAL is a table that comes automatically with an oracle. It is the schema of the SYS user. Dual can be accessible to all the users. It has 1 column, row, and is mainly used for quick access or experiments.
It is very useful as you don’t need to create an entire table just to demonstrate a feature of oracle.

Using the below script, you can get a database from dual using oracle database 19c & 21:

SELECT ora_database_name 
FROM dual;

The below image shows the database name as ORCL and this information is fetched using dual in oracle database 19c & 21c.

Read:

Oracle get database name from sys_context

SYS_CONTEXT returns the value of the parameter associated with the context namespace (userenv) at the current instant. Sys_context must be executed locally.

Userenv and session users are the built-in namespaces in the oracle database. Below script shows how to get database name from sys_context in oracle:

SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') 
AS SYS_CONTEXT
FROM DUAL;

The output of the above query is SYS as this is the default database name created while installing oracle database 19c & 21c.

Read: Oracle to_char Date Format

Oracle get global database name

Global database in oracle consists of the database name and domain name. The Oracle software identified the database using a global database name.

ORCL is the default name of a global database created while installing oracle software. You can change or create a new global database name.

The below query shows how to display global database names in oracle database 19c & 21c. In this query, we have changed the session from container to pubs and you can see different results each time.

-- GET GLOBAL DATABASE NAME
SELECT * FROM GLOBAL_NAME;

-- CHANGE DATABASE
ALTER SESSION SET CONTAINER=ORCLPDB;

-- GET GLOBAL DATABASE NAME (AGAIN)
SELECT * FROM GLOBAL_NAME;

In the below output, the global database name is displayed in oracle. By default, orcl is the global database but you switch to a different session to change the global database.

Read: Oracle Add Foreign Key

Oracle get database instance name

An instance name is the art of defining a specific instance for a particular group or section. You can switch to an instance you want to work on. It can be compared with the workspace area.

The below script shows how to get the database instance name also how to check the version of the instance name.

-- VIEW INSTANCE NAME
SELECT instance_name 
FROM v$instance;

-- VIEW INSTANCE VERSION
SELECT version 
FROM v$instance;

The output shows ORCL, the instance name is always the same as sid.

Read: Oracle to_date Format

Oracle get database service name

Service name or SID is the name of the container in the oracle database. The default name of the oracle database service name is ORCL.

Using v$parameter you can check the current SID or service name being used by you in the oracle database.

The below script shows how to get the database service name in oracle database 19c & 21c.

SELECT VALUE AS SERVICE NAME 
FROM v$parameter 
WHERE NAME like '%service_name%';

In this output, ORCL is the current service name used by us.

There is another name for this topic as well, if someone looking for a solution to find the service name used by oracle then in this section we have shared the information for that as well.

Everything that runs on the computer has associated services. These services usually run in the background and if manually closed by the user then the associated program stops working normally.

Use the below script to get the database service name in oracle database 19c & 21c.

select name from V$SERVICES;

The output shows that 5 oracle services are currently running on the system.

Use the below code to get active services in the oracle database:

SELECT name 
FROM V$ACTIVE_SERVICES;

Read: Number Datatype in Oracle

Oracle get database timezone name

Timezone is the uniform time being followed within the boundaries of a particular country. This timezone is being followed for all-purpose (commercial, legal, social, etc). Timezone varies from country to country.

The below script shows how to change Timezone, view a list of available timezones in the oracle database, view the database timezone.

-- CHANGE TIMEZONE
ALTER DATABASE SET TIME_ZONE='America/Toronto';
ALTER DATABASE SET TIME_ZONE='-05:00';

-- VIEW LIST OF AVAILABLE TIMEZONES
SELECT tzn.tzabbrev, tzname, tz_offset(tzname)
FROM V$TIMEZONE_NAMES tzn;

-- VIEW DATABASE TIMEZONE
SELECT DBTIMEZONE FROM DUAL;

-- MORE ACCURATE
SELECT sessiontimezone 
FROM DUAL;

Read: How to create a database in Oracle

Oracle get pluggable database name

Oracle pluggable database is a collection of portable schemas, schemas objects, and nonschemas objects. In oracle, the DBA_PDBS object stores the information of PDBs.

The below script shows, how to get pluggable database name in oracle database 19c & 21c.

SELECT PDB_NAME
FROM DBA_PDBS;

In the below output, ORCLPDB & PDB$SEED are two pdbs available in our system. PDF$SEED is the template for pdbs.

Also, take a look at some more Oracle tutorials.

• Connect Excel to Oracle Database
• How to Check Oracle Database Version
• How to Get List all Tables in Oracle
• Oracle check database status
• Oracle Change Database Name
• Connect to Oracle Database using Python
• Convert rows into columns in Oracle

In this tutorial, we have learned how to get database names in oracle database 19c & 21c. Also, we have covered these topics.

• Oracle get database name sql
• Oracle get database name from dual
• Oracle get database name from sys_context
• Oracle get global database name
• Oracle get database instance name
• Oracle get database service name
• Oracle get database timezone name
• Oracle get pluggable database name