GLOBAL_NAMES参数研究
<DIV><DIV><BR><A title=http://space.itpub.net/69924/viewspace-192128 href="http://space.itpub.net/69924/viewspace-192128"><FONT title=http://space.itpub.net/69924/viewspace-192128 color=#000080 size=2 face="Courier New">http://space.itpub.net/69924/viewspace-192128</FONT> </A></DIV>
<DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2><SPAN class=515130807-26062009></SPAN></FONT></FONT></DIV><FONT size=2 face="Courier New"></FONT></DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>当GLOBAL_NAMES参数设置为TRUE时，使用DATABASE LINK时，DATABASE LINK的名称必须与被连接库的GLOBAL_NAME一致。下面做一个测试，在测试中，创建数据库链接的库为XJ（WINDOWS 2003 ORACLE 10g 10.2.0.1），被链接的库为DMDB（LINUX AS5 ORACLE 10g 10.2.0.1 RAC）</FONT> </FONT></DIV>
<DIV><FONT color=#000080></FONT> </DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>首先查看DMDB的相关配置：</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> show parameter global_names</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">NAME TYPE VALUE<BR>------------------------------------ ----------- ---------<BR>global_names boolean FALSE</FONT> </DIV>
<DIV><FONT size=2 face="Courier New"><BR><FONT color=#000080>SQL> select * from global_name;;</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">GLOBAL_NAME<BR>----------------------------------------------------------<BR>DMDB</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>可以看到这个被链接的库其global_names参数为FALSE。</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>要创建数据库链接的库的配置：</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> show parameter global_names</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">NAME TYPE VALUE<BR>------------------------------------ ----------- -------<BR>global_names boolean FALSE<BR></FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * from global_name;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">GLOBAL_NAME<BR>--------------------------------------------------------<BR>XJ</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>然后做下面的操作：</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> create database link test_link connect to test identified by test using 'DMDB';</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>数据库链接已创建。</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * from </FONT><A title=mailto:dual@test_link href="mailto:dual@test_link"><FONT title=mailto:dual@test_link color=#000080 size=2 face="Courier New">dual@test_link</FONT> </A><FONT color=#000080 size=2 face="Courier New">;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">D<BR>-<BR>X</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>可以看到数据库链接工作正常。</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>在DMDB库上将global_names设为TRUE：</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> alter system set global_names=true;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">系统已更改。</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>在XJ库上再次查询，并新建一个DATABASE LINK再进行查询：</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * from </FONT><A title=mailto:dual@test_link href="mailto:dual@test_link"><FONT title=mailto:dual@test_link color=#000080 size=2 face="Courier New">dual@test_link</FONT> </A><FONT color=#000080 size=2 face="Courier New">;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">D<BR>-<BR>X</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> create database link test_link2 connect to test identified by test using 'DMDB';</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>数据库链接已创建。</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * from </FONT><A title=mailto:dual@test_link2 href="mailto:dual@test_link2"><FONT title=mailto:dual@test_link2 color=#000080 size=2 face="Courier New">dual@test_link2</FONT> </A><FONT color=#000080 size=2 face="Courier New">;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">D<BR>-<BR>X</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"><FONT color=#000080 size=2>此时可以看数据库链接工作正常。我们再将XJ库的global_names参数设置为TRUE：</FONT> </FONT></DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> alter system set global_names=true;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">系统已更改。</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * from </FONT><A title=mailto:dual@test_link2 href="mailto:dual@test_link2"><FONT title=mailto:dual@test_link2 color=#000080 size=2 face="Courier New">dual@test_link2</FONT> </A><FONT color=#000080 size=2 face="Courier New">;<BR>select * from </FONT><A title=mailto:dual@test_link2 href="mailto:dual@test_link2"><FONT title=mailto:dual@test_link2 color=#000080 size=2 face="Courier New">dual@test_link2</FONT> </A><BR><FONT color=#000080 size=2 face="Courier New"> *<BR>第 1 行出现错误:<BR>ORA-02085: 数据库链接 TEST_LINK2 连接到 DMDB</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * from </FONT><A title=mailto:dual@test_link href="mailto:dual@test_link"><FONT title=mailto:dual@test_link color=#000080 size=2 face="Courier New">dual@test_link</FONT> </A><BR><FONT color=#000080 size=2 face="Courier New">select * from </FONT><A title=mailto:dual@test_link href="mailto:dual@test_link"><FONT title=mailto:dual@test_link color=#000080 size=2 face="Courier New">dual@test_link</FONT> </A><BR><FONT color=#000080 size=2 face="Courier New"> *<BR>第 1 行出现错误:<BR>ORA-02085: 数据库链接 TEST_LINK 连接到 DMDB</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">而再次将XJ库的global_names设为FALSE，则数据库链接又可用了。<BR>SQL> alter system set global_names=false;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">系统已更改。</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * from </FONT><A title=mailto:dual@test_link href="mailto:dual@test_link"><FONT title=mailto:dual@test_link color=#000080 size=2 face="Courier New">dual@test_link</FONT> </A><FONT color=#000080 size=2 face="Courier New">;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">D<BR>-<BR>X</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * from </FONT><A title=mailto:dual@test_link2 href="mailto:dual@test_link2"><FONT title=mailto:dual@test_link2 color=#000080 size=2 face="Courier New">dual@test_link2</FONT> </A><FONT color=#000080 size=2 face="Courier New">;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">D<BR>-<BR>X</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">再将DMDB库的global_names设为FALSE，数据库链接仍然可用：</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">在DMDB库上：</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> alter system set global_names=false;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">系统已更改。</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><BR><FONT color=#000080 size=2 face="Courier New">在XJ库上：</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * from </FONT><A title=mailto:dual@test_link href="mailto:dual@test_link"><FONT title=mailto:dual@test_link color=#000080 size=2 face="Courier New">dual@test_link</FONT> </A><FONT color=#000080 size=2 face="Courier New">;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">D<BR>-<BR>X</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">可以看到，链接仍然可以用。<BR>如果在DMDB库上创建链接到XJ库上，可以观察到同样的结果。<BR>可以得出一个结论：global_names参数设置为FALSE，影响的是创建数据库链接的那个库对数据库链接的使用。也就是说，如果一个库（实例）的global_names参数设值为TRUE，则该库连接其他库的数据库链接，其名称必须要与被连接的库的global_name相同：</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">在XJ库上：</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> alter system set global_names=true;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">系统已更改。</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> create database link dmdb connect to test identified by test using 'dmdb';</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">数据库链接已创建。</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * from </FONT><A title=mailto:dual@dmdb href="mailto:dual@dmdb"><FONT title=mailto:dual@dmdb color=#000080 size=2 face="Courier New">dual@dmdb</FONT> </A><FONT color=#000080 size=2 face="Courier New">;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">D<BR>-<BR>X<BR>SQL> select * from </FONT><A title=mailto:dual@test_link href="mailto:dual@test_link"><FONT title=mailto:dual@test_link color=#000080 size=2 face="Courier New">dual@test_link</FONT> </A><FONT color=#000080 size=2 face="Courier New">;<BR>select * from </FONT><A title=mailto:dual@test_link href="mailto:dual@test_link"><FONT title=mailto:dual@test_link color=#000080 size=2 face="Courier New">dual@test_link</FONT> </A><BR><FONT color=#000080 size=2 face="Courier New">*<BR>第 1 行出现错误:<BR>ORA-02085: 数据库链接 TEST_LINK 连接到 DMDB</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><BR><FONT color=#000080 size=2 face="Courier New">SQL> select * from </FONT><A title=mailto:dual@test_link2 href="mailto:dual@test_link2"><FONT title=mailto:dual@test_link2 color=#000080 size=2 face="Courier New">dual@test_link2</FONT> </A><FONT color=#000080 size=2 face="Courier New">;<BR>select * from </FONT><A title=mailto:dual@test_link2 href="mailto:dual@test_link2"><FONT title=mailto:dual@test_link2 color=#000080 size=2 face="Courier New">dual@test_link2</FONT> </A><BR><FONT color=#000080 size=2 face="Courier New"> *<BR>第 1 行出现错误:<BR>ORA-02085: 数据库链接 TEST_LINK2 连接到 DMDB</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><BR><FONT color=#000080 size=2 face="Courier New">如果在GLOBAL_NAMES设置为TRUE的情况下，如果要建多个数据库链接到同一个库，怎么办呢？因为数据库链接的名称必须与目标库的GLOBAL_NAME相同。可以按如下的方法：</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> create database </FONT><A title=mailto:linkdmdb@link1connect href="mailto:linkdmdb@link1connect"><FONT title=mailto:linkdmdb@link1connect color=#000080 size=2 face="Courier New">linkdmdb@link1connect</FONT> </A><FONT color=#000080 size=2 face="Courier New">to test identified by test using 'dmdb';</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">数据库链接已创建。</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> create database </FONT><A title=mailto:linkdmdb@link2connect href="mailto:linkdmdb@link2connect"><FONT title=mailto:linkdmdb@link2connect color=#000080 size=2 face="Courier New">linkdmdb@link2connect</FONT> </A><FONT color=#000080 size=2 face="Courier New">to test identified by test using 'dmdb';</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">数据库链接已创建。</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * </FONT><A title=mailto:fromdual@dmdb href="mailto:fromdual@dmdb"><FONT title=mailto:fromdual@dmdb color=#000080 size=2 face="Courier New">fromdual@dmdb</FONT> </A><FONT color=#000080 size=2 face="Courier New">;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">D<BR>-<BR>X</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * </FONT><A title=mailto:fromdual@dmdb@link1 href="mailto:fromdual@dmdb@link1"><FONT title=mailto:fromdual@dmdb@link1 color=#000080 size=2 face="Courier New">fromdual@dmdb@link1</FONT> </A><FONT color=#000080 size=2 face="Courier New">;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">D<BR>-<BR>X</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">SQL> select * </FONT><A title=mailto:fromdual@dmdb@link2 href="mailto:fromdual@dmdb@link2"><FONT title=mailto:fromdual@dmdb@link2 color=#000080 size=2 face="Courier New">fromdual@dmdb@link2</FONT> </A><FONT color=#000080 size=2 face="Courier New">;</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">D<BR>-<BR>X</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">也就是在GLOBAL_NAME后面加上@再加上一个标识。这样就能够创建多个数据库链接到同一目标库上了。</FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New"></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face="Courier New">另外在创建数据库链接时，不能给其他SCHEMA创建链接，这是因为数据库链接（database link）其名称可以含有'.'即点号。比如A用户想给B用户创建一个DBLINK名叫LINKB，CREATE DATABASE LINK B.LINKB ......， 这个语句将会实际创建一个A用户下的名为B.LINKB的数据库链接。<BR></FONT></DIV>
<DIV><SPAN class=515130807-26062009><FONT color=#000080 size=2 face="Courier New">******************************************************************************************************</FONT> </SPAN></DIV></DIV>
页:
[1]