Oracle修改globalname,修改数据库的global_name

关于修改数据库的global_name，在metalink上提供了2种方法，在实际使用时，如果global_name中带有域名，而想要修改为不带域名后缀，就需要注意选择合适的方法来修改，否则会修改失败。

Two methods can be used to rename the global_name for the database:

1.  UPDATE GLOBAL_NAME SET GLOBAL_NAME = '[.db_domain]';

Once the global name is in the correct form, further changes can be made using

the supported command:

2.  ALTER DATABASE RENAME GLOBAL_NAME TO [.db_domain];

分别对这两种方式进行了测试：

1.ALTER DATABASE RENAME GLOBAL_NAME TO [.db_domain];

SQL>  select * from global_name;

GLOBAL_NAME

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

MS1.CCB.COM.CN

SQL> alter database rename global_name to "MS2";

Database altered.

SQL>  select * from global_name;

GLOBAL_NAME

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

MS2.CCB.COM.CN

可见，没有生效，查看10046事件的trace内容，发现在后台是进行了如下操作：

=====================

PARSING IN CURSOR #1 len=43 dep=0 uid=0 ct=35 lid=0 tim=1207805173891065 hv=2712338254 ad='58086b90'

alter database rename global_name to "MS2"END OF STMT

PARSE #1:c=0,e=2621,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=4,tim=1207805173891059

BINDS #1:

=====================

PARSING IN CURSOR #2 len=59 dep=1 uid=0 ct=6 lid=0 tim=1207805173891722 hv=3885980964 ad='5825d020'

update props$ set value$ = :1 where name = 'GLOBAL_DB_NAME'END OF STMT

PARSE #2:c=0,e=401,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1207805173891716

BINDS #2:

bind 0: dty=1 mxl=32(14) mal=00 scl=00 pre=00 acflg=18 oacfl2=1 size=32 ffset=0

bfp=580d84aa bln=32 avl=14 flg=09

value="MS2.CCB.COM.CN"EXEC #2:c=0,e=1176,p=0,cr=3,cu=2,mis=0,r=1,dep=1,og=4,tim=1207805173893000

XCTEND rlbk=0, rd_only=1

EXEC #1:c=0,e=3345,p=0,cr=3,cu=5,mis=0,r=0,dep=0,og=4,tim=1207805173894474

WAIT #1: nam='log file sync' ela= 2089 p1=253 p2=0 p3=0

WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0可以看出，在修改props$表时，并没有把value修改为MS2,而是仍然修改为MS2.CCB.COM.CN。

2.UPDATE GLOBAL_NAME SET GLOBAL_NAME = '[.db_domain]';

SQL> UPDATE GLOBAL_NAME SET GLOBAL_NAME ='MS2';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from global_name;

GLOBAL_NAME

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

MS2

修改成功，此时查看10046事件的trace内容：

=====================

PARSING IN CURSOR #1 len=41 dep=0 uid=0 ct=6 lid=0 tim=1207805466351466 hv=1935203014 ad='58330e38'

UPDATE GLOBAL_NAME SET GLOBAL_NAME ='MS2'END OF STMT

PARSE #1:c=10000,e=13060,p=0,cr=11,cu=0,mis=1,r=0,dep=0,og=4,tim=1207805466351458

BINDS #1:

EXEC #1:c=0,e=973,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=4,tim=1207805466352526

WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1650815232 p2=1 p3=0

*** 2009-03-11 12:33:36.109

WAIT #1: nam='SQL*Net message from client' ela= 18129623 p1=1650815232 p2=1 p3=0

STAT #1 id=1 cnt=0 pid=0 pos=1 bj=0 p='UPDATE  (cr=3 r=0 w=0 time=852 us)'

STAT #1 id=2 cnt=1 pid=1 pos=1 bj=101 p='TABLE ACCESS FULL PROPS$ (cr=3 r=0 w=0 time=205 us)'=====================

修改的是GLOBAL_NAME这个视图，而这个视图的定义是：

SQL> select view_name,text from dba_views where view_name='GLOBAL_NAME';

VIEW_NAME                      TEXT

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

GLOBAL_NAME                    select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'

可见，通过update global_name的方法，其实质仍然是修改props$表的value$值，但如果采用alter database的方式，实际执行时会自动加上后面的域名，从而导致修改不成功。