ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 修改数据库的global_name

修改数据库的global_name

原创 Linux操作系统 作者:lsq_008 时间:2009-03-11 13:43:33 0 删除 编辑

关于修改数据库的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的方式,实际执行时会自动加上后面的域名,从而导致修改不成功。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-566791/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1238943