What’s In A Name – Naming and Idenifying Oracle Databases and Instances

Identification of the database you are currently working on can
sometimes be somewhat confusing. Take a look at the internal parameters that
Oracle keeps and see if you can uniquely identify the database you are currently
working on.

A big problem in the Oracle database world is the
initialization and setup of naming conventions within the Oracle database. There
are quite a few DBA(s) that approach this with a lackadaisical attitude. It is
not until they find themselves in a distributed field of databases that they
soon begin to loose their mind and wonder where and what database they are
working on. Let’s take a quick look at the handful of parameters that need to
be set and at the same time take a look at how we can determine what database
and instance we are working on and connected to.

DB_NAME

The DB_NAME parameter is the value of the database and is
the name used when creating the database. It is specified within the INIT.ORA
parameter file or in the CREATE DATABASE command. This is one of those
parameters that is optional but is always best to set it. This is especially
true for the standby database where it should match the production database.
You can look at this parameter by issuing the SQL in Listing
1.

Listing 1
Getting DB_NAME from V$PARAMETER

HCMC-SQL> select name , value from v$parameter where name = 'db_name';

NAME                 VALUE

-------------------- --------------------------------------------------

db_name              hcmc

DB_DOMAIN

DB_DOMAIN is the value of the domain to which the database
belongs. It is the location of the database within the network hierarchy. Even
if the database you are creating is not going to be a part of any network or
distributed system, I would suggest that you set this parameter just for the
sake of going through the thought process and understanding how things connect
when networked. I personally always set up my net services for my local boxes
that are not ever going to be connected to another database. You might ask why?
Well I have never created a database that I ‘did not want to be part of a
distributed system after I played with it for a bit. After all, it will keep
you in good practice of these configuration parameters. You can look at this
parameter by issuing the SQL in Listing 2.

Listing 2
Getting DB_DOMAIN from V$PARAMETER

HCMC-SQL> select name , value from v$parameter where name = 'db_domain';

NAME                 VALUE

-------------------- --------------------------------------------------

db_domain den.dbdoctor.net

global_name

This little gem has always been at the top of my list for
determining what database I am connected to. This is the parameter that
everyone use to use to query and set the prompt for in SQL*PLUS to give a
unique identity to the SQL*PLUS session currently active. This parameter is a
combination of the DB_NAME parameter and the DB_DOMAIN parameter. The simple
SQL in Listing 3 will show you how to get this
value. If you are curious how you can set the SQL*PLUS prompt, just issue the
SQL in Listing 4. While I ‘would not quite
recommend this for anything other than a single node instance on a database, it
does work quite well if you have unique GLOBAL_NAME(s) within your
organization.

Listing 3

SQL for global_name extraction

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------
HCMC.DEN.DBDOCTOR.NET

Listing 4
Setting the SQL*PLUS prompt

column gname new_value dname noprint 
select substr(global_name,1,instr(global_name,'.')-1) gname from global_name;
define prmpt='&&dname'
set sqlprompt "&&prmpt-SQL> "
James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles