Skip to Main Content
  • Questions
  • How to change the value in GLOBAL_NAME table

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Logo .

Asked: September 13, 2001 - 8:13 pm UTC

Last updated: February 27, 2008 - 11:25 am UTC

Version: 8.1.6

Viewed 100K+ times! This question is

You Asked

Hi Tom,

SQL*Plus: Release 8.1.6.0.0 - Production on Thu Sep 13 17:08:36 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
With the Partitioning option
JServer Release 8.1.6.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
INST816.US.ORACLE.COM

SQL> show parameter global

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
global_names boolean FALSE
SQL> show parameter instance_name

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
instance_name string DVLP
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_name string DVLP
SQL>


Will it cause any problems if I set the GLOBAL_NAME as follows

update global_name
set global_name = 'DVLP.US.MYCOMPANYNAME.COM';

Do I need to change anything else?

Thanks in advance.


and Tom said...

Umm, you don't UPDATE a global_name like that. You NEVER update any data dictionary table -- never.

alter database rename global_name to dvlp.us.mycompanyname.com;

will work. It will only affect OTHER databases that have database links to you that do use GLOBAL_NAMES=TRUE. their database link names that point to you will have to change to accommidate this change.

Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

GLOBAL_NAMES and SNAPSHOT_SITE

Nelson, September 13, 2001 - 8:47 pm UTC

Thanks for your help.

Like you said, I did
alter database rename global_name to dvlp.us.mycompanyname.com;

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DVLP.US.MYCOMPANYNAME.COM


SQL> SELECT NAME, SNAPSHOT_SITE from DBA_REGISTERED_SNAPSHOTS

NAME                           SNAPSHOT_SITE
------------------------------ ------------------------------
MSC_BIS_INV_DATE_MV            INST816.US.ORACLE.COM
MSC_BIS_RES_MV                 INST816.US.ORACLE.COM
MSC_BIS_RES_DATE_MV            INST816.US.ORACLE.COM
MSC_ATP_PLAN_SN                INST816.US.ORACLE.COM
MRP_SCHD_DATES_SN              INST816.US.ORACLE.COM
MTL_DEMAND_SN                  INST816.US.ORACLE.COM
BOM_RES_CHNGS_SN               INST816.US.ORACLE.COM
WIP_DSCR_JOBS_SN               INST816.US.ORACLE.COM
BOM_BOMS_SN                    INST816.US.ORACLE.COM
WIP_FLOW_SCHDS_SN              INST816.US.ORACLE.COM
MTL_SUPPLY_SN                  INST816.US.ORACLE.COM

Now, if I want the snapshot_site to be the same as the global_name, how do I do that? The snapshots are internal (i.e. within the same database). Will it work if I leave the snapshots as is? Or Do I pretty much have to drop and recreate them?  

Tom Kyte
September 13, 2001 - 11:31 pm UTC

geez, thats the sort of info you should provide with the original question.

You are treating yourself as is you were a "remote database". The snapshot site (replication uses GLOBAL_NAMES=TRUE) is the global name

Put your global name BACK (rename it back) and rethink your need to rename those global name changes.

You should use simple materialized views without database links when replicating to "yourself".

GLOBAL_NAMES and SNAPSHOT_SITE

Dennis DiMaria, July 29, 2004 - 4:10 pm UTC

Sometimes it is necessary to change the global name of a
database. Another DBA made a copy of a production database,
which I'll call P.world.. The new database, which I'll call
D.world, is for development/testing and needed to have
realistic data. Both databases now have snapshots to a third database (L.world). Some of the snapshots are Fast refreshes. All of the master tables and logs are on
L.world.

Many refreshes were done before I learned about this situation. A snapshot log was growing without bound. Why?
Well, there had been an existing D.world so I used the
dbms_snapshot package to purge the logs and unregister
the old D.world. And the snapshots seem to be working ok.

But - when I look into DBA_REGISTERED_SNAPSHOTS I don't
see entries for D.world. Shouldn't there be?

Tom Kyte
July 29, 2004 - 4:19 pm UTC

didn't quite follow all of your steps -- you said "unregistered d.world" but went looking for it to be registered?

GLOBAL_NAMES and SNAPSHOT_SITE

Denins DiMaria, July 29, 2004 - 4:45 pm UTC

On L.world I unregistered a snapshot (it's snapsite was D.world) I also purged the snapshot log completely.

On D.world I refreshed the snapshot. I think it required
a Complete refresh the first time. Now I can use Fast
refreshes.

Shouldn't I be able to see an entry for D.world in
DBA_REGISTERED_SNAPSHOTS (on L.world, of course)?
I can do fast refreshes, so I think it would have to
be registered.

In general, what is the proper procedure when copying
a database that has fast snapshots?

Thanks.

Tom Kyte
July 30, 2004 - 6:55 am UTC

it would be prefered to drop the replicated objects in the cloned database and recreate them to get everything registered correctly.

you manually "de-registered" that snapshot.


see the dbms_mview.register_mview procedure:

Note: Typically, a materialized view is registered automatically during materialized view creation. You should only run this procedure to manually register a materialized view if the automatic registration failed or if the registration information was deleted.

GLOBAL_NAMES and SNAPSHOT_SITE

Dennis DiMaria, July 30, 2004 - 2:41 pm UTC

Thanks, I learning more about the master site
registration process

in DOC ID Note:258634.1 and 

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=236292.1&p_database_id=NOT

 there's a query, mview_reg_info.sql, which I ran on the Master site:

   select r.name snapname, snapid,
              nvl(r.snapshot_site, 'not registered') snapsite,
              snaptime
       from   sys.slog$ s, dba_registered_snapshots r
       where  s.snapid=r.snapshot_id(+) and
                       mowner like upper('SPIN') and
                       master like upper('PUBLISHED_ARTICLES')
SQL> 
SQL> /

SNAPNAME             SNAPID SNAPSITE             SNAPTIME
-------------------- ------ -------------------- -------------------
PUBLISHED_ARTICLES      119 OJA8.WORLD           30-07-2004 13:58:52




OJA8.WORLD is the original, production DB
OJAS.WORLD is the clone


I can run: 

DBMS_SNAPSHOT.REGISTER_SNAPSHOT (
   snapowner => 'OJS',
   snapname => 'PUBLISHED_ARTICLES',
   snapsite => 'OJAS.WORLD',
   snapshot_id => 119, -- that's the value in OJAS
   flag => 2097249,
   qry_txt => current_value_ );

I took flag and snapshot id from SNAP$ in OJAS

Will the above proc call cause a problem in the SLOG$ table?
SLOG$ has snapshot owner, name, and snapid, but not
snapsite.

Currently, if I do a complete refresh in OJA8 I can't do a fast refresh in OJAS. So I do a complete refresh in OJAS
and then I can't do a fast in OJA8, etc.
 

Tom Kyte
July 30, 2004 - 6:05 pm UTC

I think your best bet at this point is either:

a) work with support -- they'll need a history of step by step whats happened here, lots going on, very hard to follow

b) recreate the snapshots entirely -- let them register themselves naturally.

Pravesh Karthik - Chennai, August 13, 2004 - 9:01 am UTC

Tom,

Global Naming -- enforce that a dblink has same name as the db it connects to.

assuming a,b are two db's, i can have only one db link to a->b ?

Then if i want to set different db links for two or more different users ..how to do?

Thanks a lot for your time.




Tom Kyte
August 13, 2004 - 4:46 pm UTC

create database link database_b@link_1 ...
create database link database_b@link_2 ...

and so on. connection qualifiers.

difference between register MVIEWS and MVIEW GROUPS

A reader, August 18, 2005 - 1:36 pm UTC

Hi

Working in Advanced Replication, a master site and a MV site.

I am looking into the difference between registering a MV group and MV suing dbms_repcat

I dont understand what is

DBMS_REPCAT.REGISTER_MVIEW_REPGROUP used for

What it confuses me if I want to register single MV I run

DBMS_REPCAT.REGISTER_MVIEW from MV site

If I want to register a group I have to run

DBMS_REPCAT.REGISTER_MVIEW_REPGROUP in the master site

I dont understand, if they are the same why one runs from MV site and the other from Master Site.

Also why isnt DBMS_REPCAT.REGISTER_MVIEW in the documents...?

Thx

A problem while changing GLOBAL_NAME

Serge Shmygelsky, October 13, 2005 - 5:07 am UTC

Hello Tom,

I have a strange problem while trying to change GLOBAL_NAME. I have just installed Oracle 10.2.0.1.0 on Windows XP. During the installation, I specified database name to be 'rex'. And what is happening now:

@> conn sys as sysdba;
Enter password: ******
Connected.
SYS@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------
REX.REGRESS.RDBMS.DEV.US.ORACLE.COM
SYS@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM> alter database rename global_name to rex;
SYS@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------
REX.REGRESS.RDBMS.DEV.US.ORACLE.COM
SYS@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM>

I have tried to reconnect:

SYS@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM> conn sys as sysdba;
Enter password: ******
Connected.
SYS@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM> select * from global_name;

GLOBAL_NAME
----------------------------------------------------------
REX.REGRESS.RDBMS.DEV.US.ORACLE.COM
SYS@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM>

In the alert log I can see the following:

Thu Oct 13 12:00:02 2005
alter database rename global_name to rex
Thu Oct 13 12:00:02 2005
Completed: alter database rename global_name to rex

But obviously nothing has happened. What I (or Oracle) did wrong?
Thanks in advance

Domain is not going away anyway

Serge Shmygelsky, October 14, 2005 - 10:07 am UTC

Hi Tom,

just as addition to the previous post. I've tried to create some DB links and they are created with this long domain also. I cannot understand where it comes from:

SHMYG@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM> create database link test connect to test identified by test using 'test';

SHMYG@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM> select * from user_db_links;

DB_LINK
---------------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

SHMYG@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------
db_domain string
SHMYG@REX.REGRESS.RDBMS.DEV.US.ORACLE.COM> show parameter name

NAME TYPE VALUE
------------------------------------ ----------- ------
db_file_name_convert string
db_name string rex
db_unique_name string rex
global_names boolean FALSE
instance_name string rex
lock_name_space string
log_file_name_convert string
service_names string rex

I don't have this domain in my SQLNET.ORA.

What could be the reason for that?


Tom Kyte
October 14, 2005 - 10:24 am UTC

select * from global_name, what do you see there.


ops$tkyte@ORA10G> select * from global_name;
 
GLOBAL_NAME
-------------------------------------------------------------------------------
ORA10G.US.ORACLE.COM
 
ops$tkyte@ORA10G> create database link x connect to a identified by b using 'x';
Database link created.
 
 
ops$tkyte@ORA10G> select db_link from user_db_links;
 
DB_LINK
-------------------------------------------------------------------------------
X.US.ORACLE.COM
 
ops$tkyte@ORA10G> alter database rename global_name to ora10g.hello.world;
Database altered.
 
ops$tkyte@ORA10G> create database link y connect to a identified by b using 'x';
Database link created.
 
ops$tkyte@ORA10G> select db_link from user_db_links;
 
DB_LINK
-------------------------------------------------------------------------------
X.US.ORACLE.COM
Y.HELLO.WORLD
 
 

Domain

Serge Shmygelsky, October 14, 2005 - 10:44 am UTC

Hi Tom,
Thanks for your reply.
I've already found in one of the posts on your site that I cannot get rid of the domain in the global name once it is there. I've also tried SQL proposed by you and it works as you said.
But the strange thing is that I haven't specified any domain during the installation, at least, explicitly. Where could it come from? And another question - why do these database links contain domain? Is it retreived from global_name? Because, as I said, db_domain is null in my database.
Thanks in advance.

Tom Kyte
October 14, 2005 - 4:52 pm UTC

it is coming from the global_name, you may set it to whatever you like (like your own domain in your environment using the alter database command)

Re: Domian.

Sai, October 14, 2005 - 2:58 pm UTC

That's there in "sqlnet.ora" file.

Tom Kyte
October 14, 2005 - 5:55 pm UTC

not necessarily

Jenifer, November 20, 2006 - 5:55 pm UTC

I am having this same problem but I am unable to get rid of the REGRESS.RDBMS.DEV.US.ORACLE.COM as well.

Tom Kyte
November 22, 2006 - 2:49 pm UTC

same problem as what? big page here - can you elaborate a bit more?

How to remove a domain name from a GLOBAL_NAME

Magnus Lübeck, February 27, 2008 - 9:48 am UTC

Hello Thomas,

We had some weird issues with this, and just could not revert the configuration after giving a database a GLOBAL_NAME with a domain name.

e.g DOMAIN_NAME=DGKO01.COMPANY.COM

After setting this, encountering really weird issues, reverting and go back to the way it was before was not possible, as we could not get rid of the domain name, and old database links that we had created was not possible to delete.

We are running Oracle 10.2.0.3 on HPUX.

dba_tzhluma3@DGKO01> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


Well, I sort of got a bit upset and spent some time to find a workaround, which I found after a couple of hours of googling and trial and error, which in the end was just an

UPDATE GLOBAL_NAME set GLOBAL_NAME=<DBNAME>


as the sys user, which was the only workaround I could find, which allowed me to remove the domain name completely.

Following my step by step description below should reproduce the problem. I would like to file this as a BUG, or is it really meant to be so that I will not be able to remove a domain name from the GLOBAL_NAME?

Also, DROP DATABASE LINK seems to fetch this domain name concatenating it to the database link name when I try to drop a database link that was created before the change of GLOBAL_NAME.


I sort of got to the solution by grepping for our domain name in the system01.dbf datafile, which sort of got me into the idea that the domain name is NOT in the control file, but rather in a table.

[DGKO01]mluebeck@hp06a:/db/DGKO01/ctrl $
> sudo -u oracle strings control01.ctl | grep -i company
[DGKO01]mluebeck@hp06a:/db/DGKO01/ctrl $

[DGKO01]mluebeck@hp06a:/db/DGKO01/data $
> sudo -u oracle strings system01.dbf | grep -i company
ASDF.COMPANY.COM
DGKO01.COMPANY.COM
WHATEVER.COMPANY.COM
PGKO01.COMPANY.COM
DASD.COMPANY.COM
DGKO01.COMPANY.COM
ASDF.COMPANY.COM


Then I started doing some targeted google.com searches.

http://www.google.ch/search?hl=de&q=alter+database+rename+global_name++%22database+link+name+expected%22&btnG=Suche&meta=

(google on: alter database rename global_name "database link name expected")


But first a short walk through the issue:

1) When assigning a domain name to a GLOBAL_NAME, we encountered issues

* Old db links not possible to drop
* We had issues in applications with hardcoded db link names (not using the domain name).


Setting the global name was done like this:

cd /tmp; sudo -u oracle sqlplus "/ as sysdba"
SQL> alter database rename global_name to DGKO01.company.com;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DGKO01.COMPANY.COM





Now we will try to create a database link without the domain name (LOG OUT and log in again as a normal user):


dba_tzhluma3> create database link malu connect to dba_tzhluma3 identified by XXXXX using 'DMAN01';

Database link created.

dba_tzhluma3> select db_link from user_db_links;

DB_LINK
--------------------------------------------------------------------------------
MALU.COMPANY.COM



This is where our issues start and we cannot revert.


What we wanted now was to remove the COMPANY.COM domain name from the global_name.


#==============================
#================= THE ATTEMPTED SOLUTION
#==============================

The solution was found here:


http://www.dbasupport.com/forums/showpost.php?s=1c9f6f9c23163d61f1e263cb3d9a3d8c&p=68077&postcount=2


By using the "UPDATE GLOBAL_NAME" clause and a "COMMIT" we were able to revert to a GLOBAL_NAME without a domain name.


Connect to the database using SYS:


sys> update global_name set global_name = 'DGKO01';

1 row updated.

sys> commit;

sys> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DGKO01





Just make sure you shut down all connections/restart the database so that it will affect all sessions.





This actually helped us solve another (by metalink never solved) issue with database links.


2) Old database links without domain name impossible to drop


First we create a db-link as a normal user:

dba_tzhluma3> create database link malu connect to dba_tzhluma3 identified by XXXXX using 'DMAN01';

Database link created.

dba_tzhluma3> select db_link from user_db_links;

DB_LINK
--------------------------------------------------------------------------------
MALU





Then we change the global_name:



cd /tmp; sudo -u oracle sqlplus "/ as sysdba"
SQL> alter database rename global_name to DGKO01.company.com;

Database altered.

sys> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DGKO01.COMPANY.COM




Now we switch back to our normal user (NOTE, you MUST LOG OUT AND LOG IN AGAIN) and try to drop the database link:

dba_tzhluma3> select db_link from user_db_links;

DB_LINK
--------------------------------------------------------------------------------
MALU

dba_tzhluma3> drop database link malu;
drop database link malu
                   *
ERROR at line 1:
ORA-02024: database link not found




This is because the database tries to add company.com to the database link name before dropping it.



Try this:

LOG OUT, LOG IN AS sys

sys> update global_name set global_name = 'DGKO01';

1 row updated.

sys> commit;

Commit complete.




LOG OUT, LOG IN AS normal user:


dba_tzhluma3> select db_link from user_db_links;

DB_LINK
--------------------------------------------------------------------------------
MALU

dba_tzhluma3> drop database link malu;

Database link dropped.






Tom Kyte
February 27, 2008 - 11:25 am UTC

....
Following my step by step description below should reproduce the problem. I would like to file this as a BUG, or is it really meant to be so that I will not be able to remove a domain name from the GLOBAL_NAME?
......

it is not a bug, it is the way it works. once you have a domain, you have a domain and there is no supported way to remove it - only to rename it.

what you did is something that should not be done. You are absolutely on your own there.

Documentation wrong?

dw, August 22, 2010 - 8:29 pm UTC

In reference to your response to the original question:

"Umm, you don't UPDATE a global_name like that. You NEVER update any data dictionary
table -- never.

alter database rename global_name to dvlp.us.mycompanyname.com;

will work. It will only affect OTHER databases that have database links to you that do
use GLOBAL_NAMES=TRUE. their database link names that point to you will have to change
to accommidate this change. "

In the 11GR2 documentation it states:

"Determining Whether Global Naming Is Enforced

The name that you give to a link on the local database depends on whether the remote database that you want to access enforces global naming. If the remote database enforces global naming, then you must use the remote database global database name as the name of the link. For example, if you are connected to the local hq server and want to create a link to the remote mfg database, and mfg enforces global naming, then you must use the mfg global database name as the link name........"


This contradicts what you said in your response. The docs imply that if you change the REMOTE database settings to enforce global names, then the local database must use the REMOTE database's global name when creating db links to it.
I tried this and it is absolutely false. What you said is correct. It is the LOCAL database machine GLOBAL_NAMES parameter setting that affects the database link names to remote databases. Changing the remote database GLOBAL_NAMES parameter setting does not affect any database links to that remote database.

So am I right in saying the Oracle docs are wrong or have I misunderstood?

Nice

A reader, April 04, 2017 - 4:36 am UTC


More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.