2011-11-25 打开与关闭dblink

新知识点一： 打开与关闭db link
之前使用db link 都不知道有打开和关闭的说法， 今天在pub上偶然看到，引起了注意
Closing Database Links

If you access a database link in a session, then the link remains open until you close the session. A link is

open in the sense that a process is active on each of the remote databases accessed through the link. This

situation has the following consequences:


      If 20 users open sessions and access the same public link in a local database, then 20 database link

connections are open.

      If 20 users open sessions and each user accesses a private link, then 20 database link connections are

open.

      If one user starts a session and accesses 20 different links, then 20 database link connections are open.

After you close a session, the links that were active in the session are automatically closed. You may have

occasion to close the link manually. For example, close links when:

      The network connection established by a link is used infrequently in an application.

      The user session must be terminated.

If you want to close a link, issue the following statement, where linkname refers to the name of the link:

ALTER SESSION CLOSE DATABASE LINK linkname;

Note that this statement only closes the links that are active in your current session.


When you issue a
statement that uses a database link, Oracle Database creates a session for you on the
remote database using that link. The connection remains open until you end your local
session or until the number of database links for your session exceeds the value of the
initialization parameter OPEN_LINKS. If you want to reduce the network overhead
associated with keeping the link open, then use this clause to close the link explicitly if
you do not plan to use it again in your session.



做了一些测试，结果如下
1. v$dblink 视图 记录当前session打开的db_link
2. open_links 参数限制了每个session能打开的最大db_link数 超过这个值报错如下：
ORA-02020: too many database links in use
3. open_links_per_instance 参数 ----- 测试失败，值为4 但是开了5个session 每个session都能通过select方式

打开一个db_link
4. 通过select 方式打开db_link 后，执行 ALTER SESSION CLOSE DATABASE LINK linkname 报错如下：
    ORA-02080: database link is in use
5. 执行commit 后 ALTER SESSION CLOSE DATABASE LINK linkname 关闭成功


以下为oracle文档对这两个参数的说明：
OPEN_LINKS：
OPEN_LINKS specifies the maximum number of concurrent open connections to remote databases in one

session. These connections include database links, as well as external procedures and cartridges, each of

which uses a separate process.

Oracle counts one open link for the following:

    *

      For each user that references a public or private database link
    *

      For each external procedure or cartridge connection when it is executed for the first time

Both types of connections close when the session ends. You can also close a database link connection

explicitly by issuing an ALTER SESSION CLOSE DATABASE LINK statement.

You should set this parameter to allow for the external procedure and cartridge connections expected during

the session plus the number of databases referred to in typical distributed transactions (that is, a single SQL

statement that references multiple databases), so that all the databases can be open to execute the

statement. For example, if queries alternately access databases A, B, and C, and OPEN_LINKS is set to 2,

time will be lost waiting while one connection is broken and another made. Increase the value if many

different databases are accessed over time.

This parameter refers only to connections used for distributed transactions. Direct connections to a remote

database specified as an application connects are not counted.

If you set OPEN_LINKS to 0, then no distributed transactions are allowed.

OPEN_LINKS_PER_INSTANCE：

OPEN_LINKS_PER_INSTANCE specifies the maximum number of migratable open connections globally for

each database instance. XA transactions use migratable open connections so that the connections are

cached after a transaction is committed. Another transaction can use the connection, provided the user who

created the connection is the same as the user who owns the transaction.

OPEN_LINKS_PER_INSTANCE is different from OPEN_LINKS, which indicates the number of connections

from a session. The OPEN_LINKS parameter is not applicable to XA applications.

新知识点2：
create table "test"……
create table test......
在数据字典中会出现表名 TEST 和 test

新知识点3：
distinct 与group by
10g r2前 distinct  走sort unique           group by 走sort group by
10g r2后 distinct  走hash unique         group by 走hash group by
distinct 和 group by 效率上没明显区别，从执行计划上看 hash unique在cost 上比 hash group by 要大，且 hash unique 会占用一定的 tempspc

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

转载于:http://blog.itpub.net/24383181/viewspace-712052/