Re: Changing global_dbname

In addition to what Tom Kyte's suggested (which is very good), You may also want to check the value of init.ora parameter global_names. Most likely yours is set to TRUE, and that forces you to name the dblink the same as the global database name (i.e. db_name.db_domain). If you change it to false ("alter system set global_names = false"), then you can freely name your dblink with anything. However, this might not be the "recommended" approach, because advanced replication requires you to set global_names = TRUE.

In summary, "global_names" is a init.ora parameter, with values TRUE or FALSE; while "GLOBAL_NAME" is a data dictionary view with one row, one column, showing the global name of the database. BOTH are dynamically changible. BOTH have certain effects on your naming of dblinks.

This is a very confusing aspect of Oracle, I have to say.

Thomas Kyte (tkyte_at_oracle.com) wrote:
: In article <a5rnm8$qdg$1_at_lust.ihug.co.nz>, "Howard says...
: >
: >OK, I give up.
: >
: >If I ever knew, I've forgotten. How do you change the global database name?
: >
: >The problem is that a database link pointed at DB9.aldeburgh.local (a
: >version 9 database) from DB8.aldeburgh.local (a version 8 database) keeps
: >popping up, whenever used in a select statement, with the error ORA-02085:
: >database link DB9.ALDEBURGH.LOCAL connects to DB9.US.ORACLE.COM.
: >
: >In theory, global dbname is constructed from db_domain and db_name in the
: >relevant init.ora... well, my init.ora for DB9 says db_name=DB9,
: >db_domain=aldeburgh.local, so I've spent hours chasing my tail trying to
: >work out why the damn thing still thinks it's pointing to a us.oracle.com
: >domain. I confess the database was inadvertently created with global
: >database name set to merely DB9, the same as the service name. If I create
: >a brand new database specifying an appropriate 'X.aldeburgh.local' global
: >database name, then everything works as advertised.
: >
: >Listener.ora says
: >
: >SID_LIST_LISTENER =
: > (SID_LIST =
: > (SID_DESC =
: > (GLOBAL_DBNAME = DB9.aldeburgh.local)
: > (ORACLE_HOME = d:\oracle\ora91)
: > (SID_NAME = DB9)
: > )
: > )
: >
: >Sqlnet.ora doesn't have a default domain name set. And connections made
: >directly in the form 'connect system/manager_at_db9' work perfectly fine.
: >
: >What blindingly obvious nugget have I overlooked?
: >
: >Regards
: >HJR
: >--
: >----------------------------------------------
: >Resources for Oracle: http://www.hjrdba.com
: >===============================
: >
: >
: >
: >

: alter database rename global_name to NEW.NAME.AND.DOMAIN

: if you leave the domain off -- it'll pick it up from the last name. Consider:

: ops$tkyte_at_ORA9I.WORLD> select * from global_name;

: GLOBAL_NAME
: ------------------------------
: ORA9I.WORLD

: ops$tkyte_at_ORA9I.WORLD> show parameter domain

: NAME TYPE VALUE
: ------------------------------------ ----------- ------------------------------
: db_domain string foo.bar
: ops$tkyte_at_ORA9I.WORLD> alter database rename global_name to test;

: Database altered.

: ops$tkyte_at_ORA9I.WORLD> select * from global_name;

: GLOBAL_NAME
: ------------------------------
: TEST.WORLD

: ops$tkyte_at_ORA9I.WORLD> alter database rename global_name to test.foo.bar;

: Database altered.

: ops$tkyte_at_ORA9I.WORLD> select * from global_name;

: GLOBAL_NAME
: ------------------------------
: TEST.FOO.BAR

: ops$tkyte_at_ORA9I.WORLD>

: Just use the fully qualified name DB9.ALDEBURGH.LOCAL in your rename command.

: --
: Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
: Expert one on one Oracle, programming techniques and solutions for Oracle.
: http://www.amazon.com/exec/obidos/ASIN/1861004826/
: Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Mon Mar 04 2002 - 12:49:16 CST