Name of database connected to 1

[BJCooperIT]

I have a function in a package that returns the name of the database to which a session is connected by returning GLOBAL_NAME. This has worked well for the past three years. When a testing script is run, the database, date, time and description are displayed:

HSDEV      04/09/2009 03:41:28pm     Test INTERNET_STATUS

The database names HSDEV, HSSTAGE, HSPROD have always reflected the database in which the test was run.

We have a new 10g database and we connect to HSDEV.HP. My problem is that the name of this database is returned as HSDEV, not HSDEV.HP:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as devbjc
 
SQL> select global_name from global_name;
 
GLOBAL_NAME
--------------------
HSDEV.WORLD
 
SQL> select ora_database_name from dual;
 
ORA_DATABASE_NAME
--------------------
HSDEV.WORLD
 
SQL> select sys_context('userenv','db_name') from dual;
 
SYS_CONTEXT('USERENV','DB_NAME
-------------------------------
hsdev
 
SQL>

The function no longer properly identified where the test was run. So I have modified the function to append the version number (extracted from the BANNER column in SYS.V_$VERSION) which helped:

HSDEV 10.2     04/09/2009 03:45:16pm     Test INTERNET_STATUS

But, the proper way would be to display the database name to which I connect (HSDEV.HP). Any help you can offer is much appreciated.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)

 

[KenCunningham]

May well be too simplistic, but you have renamed the database with alter database rename global_name to HSDEV.HP;?

Some days are diamonds, some days are rocks - make sure most are the former.

 

[BJCooperIT]

Here I am just a developer without those kinds of privileges. When I inquired about it, the answer I received from the state DBAs is that the name must be identical for migration purposes.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)

 

[LKBrwnDBA]

You are getting the expected results, I don't know why would you expect "HSDEV.HP"?

The global database name consists of the user-specified local database name and the location of the database within a network structure. The DB_NAME initialization parameter determines the local name component of the database name, and the DB_DOMAIN parameter indicates the domain (logical location) within a network structure. The combination of the settings for these two parameters must form a database name that is unique within a network.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: Perhaps the GLOBAL_NAME was set incorrectly in the previous database or the "DOMAIN_NAME" was set to HP instead of WORLD.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[BJCooperIT]

Not that I expect it, it is what I need for literally hundreds of testing scripts to be self-documenting.

It sounds like what I might need is the DB_DOMAIN and DB_NAME parameter values. Is there a way for me to be able to select those values to create the actual database name we connect to?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)

 

[LKBrwnDBA]

GLOBAL_NAME should be: {db_name}.{db_domain}

If you are getting HSDEV.WORLD that means the db_domain name is set to "WORLD"

To get the result you want, you would need to change db_domain to HP and GLOBAL_NAME to HSDEV.HP

If you actually "connect" to HSDEV.HP It may be perhaps the database "alias" in tnsnamea.ora is set to {db_name}.{hostname}, you could then try the following:

Select sys_context('USERENV','SERVER_HOST') from dual;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[BJCooperIT]

Thanks for trying again. I don't have privileges to change any parameters. The server host yields yet another name:

SQL> Select substr(sys_context('USERENV','SERVER_HOST'),1,30) server_host from dual;
 
SERVER_HOST
------------------------------
ofw1
 
SQL>

Seems like it shouldn't be this difficult. If I connect to that name, surely I should be able to recreate it. Sigh...

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)

 

[LKBrwnDBA]

Where does (did) the "HP" come from?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[KenCunningham]

Saucy!

Hewlett Packard perhaps?

Some days are diamonds, some days are rocks - make sure most are the former.

 

[BJCooperIT]

No idea. The DBAs are referring to the 10g database as the HP database. Maybe I can decode the version number into HP for my purposes. Thanks for trying though - I did learn a bit and will tuck that knowledge away.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)