SELECT也需要COMMIT

SELECT (DBLINK) 需要 COMMIT来释放事务;

[@more@]

业务环境:

A机器10.0.69.101 -- heyf
B机器10.2.226.24 -- VOUCHER

有业务需求,B需要去连接A取数据,下面创建一个简单的环境.

在B机器上:

TNSNAMES.ora

heyf =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.69.101 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = heyf)
)
)


create">heyf@VOUCHER>create database link a connect to heyf identified by heyf using 'heyf';

Database link created.

create">heyf@VOUCHER>create or replace procedure proc_t1
2 as
3 v_cnt number;
4 begin
5 select count(*) into v_cnt from t2@A ;
6 execute immediate 'alter session close database link A';
7 end;
8 /

Procedure created.

exec">heyf@VOUCHER>exec proc_t1
BEGIN proc_t1; END;

*
ERROR at line 1:
ORA-02080: database link is in use
ORA-06512: at "HEYF.PROC_T1", line 6
ORA-06512: at line 1

alter">heyf@VOUCHER>alter session close database link a;
ERROR:
ORA-02081: database link is not open

引出问题:

ERROR at line 1:
ORA-02080: database link is in use

GOOGLE:

Why does it seem that a SELECT over a db_link requires a commit after execution ?

Because it does! When Oracle performs a distributed SQL statement Oracle reserves an entry in the rollback segment area for the two-phase commit processing. This entry is held until the SQL statement is committed even if the SQL statement is a query.
If the application code fails to issue a commit after the remote or distributed select statement then the rollback segment entry is not released. If the program stays connected to Oracle but goes inactive for a significant period of time (such as a daemon, wait for alert, wait for mailbox entry, etc?) then when Oracle needs to wrap around and reuse the extent, Oracle has to extend the rollback segment because the remote transaction is still holding its extent. This can result in the rollback segments extending to either their maximum extent limit or consuming all free space in the rbs tablespace even where there are no large transactions in the application. When the rollback segment tablespace is created using extendable files then the files can end up growing well beyond any reasonable size necessary to support the transaction load of the database. Developers are often unaware of the need to commit distributed queries and as a result often create distributed applications that cause, experience, or contribute to rollback segment related problems like ORA-01650 (unable to extend rollback). The requirement to commit distributed SQL exists even with automated undo management available with version 9 and newer. If the segment is busy with an uncommitted distributed transaction Oracle will either have to create a new undo segment to hold new transactions or extend an existing one. Eventually undo space could be exhausted, but prior to this it is likely that data would have to be discarded before the undo_retention period has expired.
Note that per the Distributed manual that a remote SQL statement is one that references all its objects at a remote database so that the statement is sent to this site to be processed and only the result is returned to the submitting instance, while a distributed transaction is one that references objects at multiple databases. For the purposes of this FAQ there is no difference, as both need to commit after issuing any form of distributed query.

CLOSE DATABASE LINK Clause

Specify CLOSE DATABASE LINK to close the database link dblink. When you issue a statement that uses a database link,
Oracle 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.

ORA-02080: database link is in use
Cause: a transaction is active or a cursor is open on the database link given in the alter session close database link command.
Action: commit or rollback, and close all cursors

从上面我们基本上能理解为什么会发生这个错误了.

因为 select count(*) into v_cnt from t2@A 的执行在本机产生了一个事务,但没有COMMIT或者ROLLBACK,这时如果要执行alter session close database link A；ORACLE认为还有事务在这个DBLINK上面,所以不能关闭这个DBLINK,于是就报ORA-02080: database link is in use 错误!

下面我们来深入分解一下:
在B机器上起一个SESSION #1:

select">heyf@VOUCHER>select count(*) from t2@a
2 ;

COUNT(*)
----------
0

select">heyf@VOUCHER>select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
4 22 7808 0 0 0 0

--在B机器上另外起一个SESSION,用来DUMP UNDO HEADER:

@>select * from v$rollname where usn=4;

USN NAME
---------- ------------------------------
4 _SYSSMU4$

@>alter system dump undo header '_SYSSMU4$';

System altered.

@>SELECT d.VALUE|| '/'|| LOWER (RTRIM (i.INSTANCE, CHR (0)))|| '_ora_'|| p.spid|| '.trc' trace_file_name
2 FROM (SELECT p.spid
3 FROM SYS.v$mystat m, SYS.v$session s, SYS.v$process p
4 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
5 (SELECT t.INSTANCE
6 FROM SYS.v$thread t, SYS.v$parameter v
7 WHERE v.NAME = 'thread'
8 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
9 (SELECT VALUE FROM SYS.v$parameter WHERE NAME = 'user_dump_dest') d
10 ;

TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/admin/voucher/udump/voucher_ora_14098.trc


********************************************************************************
Undo Segment: _SYSSMU4$ (4)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 255
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x008007ff ext#: 15 blk#: 118 ext size: 128
#blocks in seg. hdr''s freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 15
Unlocked
Map Header:: next 0x00000000 #extents: 17 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080003a length: 7
0x00800031 length: 8
0x00800369 length: 8
0x00800371 length: 8
0x00800379 length: 8
0x00800381 length: 8
0x00800129 length: 8
0x00800131 length: 8
0x00800141 length: 8
0x00800149 length: 8
0x00800151 length: 8
0x00800241 length: 8
0x00800249 length: 8
0x00800251 length: 8
0x00800259 length: 8
0x00800789 length: 128
0x008000b9 length: 8

Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1218159665
Extent Number:1 Commit Time: 1218161807
Extent Number:2 Commit Time: 1218163488
Extent Number:3 Commit Time: 1218165254
Extent Number:4 Commit Time: 1218167095
Extent Number:5 Commit Time: 1218167095
Extent Number:6 Commit Time: 1218167095
Extent Number:7 Commit Time: 1218167095
Extent Number:8 Commit Time: 1218167095
Extent Number:9 Commit Time: 1218167146
Extent Number:10 Commit Time: 1218167146
Extent Number:11 Commit Time: 1218167147
Extent Number:12 Commit Time: 1218167147
Extent Number:13 Commit Time: 1218167147
Extent Number:14 Commit Time: 1218167172
Extent Number:15 Commit Time: 1218167172
Extent Number:16 Commit Time: 1218157730

TRN CTL:: seq: 0x0394 chd: 0x0003 ctl: 0x001b inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x008007fe.0394.17 scn: 0x0575.7f2214fe
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0394.16 ext: 0xf spc: 0xea2
uba: 0x008007ff.0394.08 ext: 0xf spc: 0x1aa2
uba: 0x00000000.0363.3c ext: 0x35 spc: 0xc2e
uba: 0x00000000.01a9.5c ext: 0x3 spc: 0x192
uba: 0x00000000.0151.2d ext: 0x11 spc: 0x1108
TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x1e8a 0x0026 0x0575.7f2216f4 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x01 9 0x00 0x1e80 0x0009 0x0575.7f221651 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x02 9 0x00 0x1e4c 0x002a 0x0575.7f22156d 0x008007dd 0x0000.000.00000000 0x00000006 0x00000000
0x03 9 0x00 0x1e72 0x000f 0x0575.7f2214ff 0x008007cf 0x0000.000.00000000 0x00000003 0x00000000
0x04 9 0x00 0x1e6b 0x001d 0x0575.7f221596 0x008007df 0x0000.000.00000000 0x00000003 0x00000000
0x05 9 0x00 0x1e96 0x001a 0x0575.7f2217d9 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x06 9 0x00 0x1e9a 0x001b 0x0575.7f2217fd 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x07 9 0x00 0x1e89 0x001f 0x0575.7f22160d 0x008007f9 0x0000.000.00000000 0x00000003 0x00000000
0x08 9 0x00 0x1e6a 0x0014 0x0575.7f221599 0x008007e8 0x0000.000.00000000 0x00000003 0x00000000
0x09 9 0x00 0x1e90 0x0013 0x0575.7f221653 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x0a 9 0x00 0x1ea3 0x0011 0x0575.7f2216aa 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x0b 9 0x00 0x1e4c 0x0000 0x0575.7f2216f2 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x0c 9 0x00 0x1e99 0x0012 0x0575.7f22160f 0x008007fe 0x0000.000.00000000 0x00000003 0x00000000
0x0d 9 0x00 0x1e66 0x0015 0x0575.7f22159b 0x008007ed 0x0000.000.00000000 0x00000003 0x00000000
0x0e 9 0x00 0x1e9b 0x0028 0x0575.7f22160a 0x008007f0 0x0000.000.00000000 0x00000003 0x00000000
0x0f 9 0x00 0x1ea2 0x002e 0x0575.7f221500 0x008007d0 0x0000.000.00000000 0x00000006 0x00000000
0x10 9 0x00 0x1e6f 0x000a 0x0575.7f221690 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x11 9 0x00 0x1ea1 0x000b 0x0575.7f2216c2 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x12 9 0x00 0x1e8a 0x0001 0x0575.7f221610 0x008007ff 0x0000.000.00000000 0x00000006 0x00000000
0x13 9 0x00 0x1e7f 0x002b 0x0575.7f221669 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x14 9 0x00 0x1e87 0x000d 0x0575.7f22159a 0x008007eb 0x0000.000.00000000 0x00000003 0x00000000
0x15 9 0x00 0x1e9c 0x002c 0x0575.7f22159c 0x008007ee 0x0000.000.00000000 0x00000006 0x00000000
0x16 10 0x80 0x1e80 0x000f 0x0575.7f221800 0x00000000 0x0000.000.00000000 0x00000000 0x00000000
0x17 9 0x00 0x1e5f 0x0020 0x0575.7f22156b 0x008007d9 0x0000.000.00000000 0x00000003 0x00000000
0x18 9 0x00 0x1e88 0x001c 0x0575.7f221783 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x19 9 0x00 0x1e69 0x0017 0x0575.7f22156a 0x008007d5 0x0000.000.00000000 0x00000003 0x00000000
0x1a 9 0x00 0x1e8c 0x0006 0x0575.7f2217e9 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x1b 9 0x00 0x1e60 0xffff 0x0575.7f2217ff 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x1c 9 0x00 0x1e15 0x0005 0x0575.7f2217a7 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x1d 9 0x00 0x1e87 0x001e 0x0575.7f221597 0x008007e3 0x0000.000.00000000 0x00000003 0x00000000
0x1e 9 0x00 0x1e87 0x0008 0x0575.7f221598 0x008007e5 0x0000.000.00000000 0x00000003 0x00000000
0x1f 9 0x00 0x1ea8 0x000c 0x0575.7f22160e 0x008007fc 0x0000.000.00000000 0x00000003 0x00000000
0x20 9 0x00 0x1e5f 0x0002 0x0575.7f22156c 0x008007db 0x0000.000.00000000 0x00000003 0x00000000
0x21 9 0x00 0x1e70 0x0007 0x0575.7f22160c 0x008007f6 0x0000.000.00000000 0x00000003 0x00000000
0x22 9 0x00 0x1e70 0x0018 0x0575.7f221781 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x23 9 0x00 0x1e89 0x002d 0x0575.7f2215eb 0x008007ed 0x0000.000.00000000 0x00000001 0x00000000
0x24 9 0x00 0x1e53 0x002f 0x0575.7f2215ef 0x008007ed 0x0000.000.00000000 0x00000001 0x00000000
0x25 9 0x00 0x1e90 0x0023 0x0575.7f2215e8 0x008007ed 0x0000.000.00000000 0x00000001 0x00000000
0x26 9 0x00 0x1e7b 0x0027 0x0575.7f22170c 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x27 9 0x00 0x1e5c 0x0022 0x0575.7f221769 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x28 9 0x00 0x1e7e 0x0021 0x0575.7f22160b 0x008007f3 0x0000.000.00000000 0x00000003 0x00000000
0x29 9 0x00 0x1e87 0x0019 0x0575.7f221569 0x008007d3 0x0000.000.00000000 0x00000003 0x00000000
0x2a 9 0x00 0x1e66 0x0004 0x0575.7f221594 0x008007db 0x0000.000.00000000 0x00000001 0x00000000
0x2b 9 0x00 0x1e62 0x0010 0x0575.7f22166b 0x008007fe 0x0000.000.00000000 0x00000001 0x00000000
0x2c 9 0x00 0x1e7f 0x0025 0x0575.7f2215e5 0x008007ed 0x0000.000.00000000 0x00000001 0x00000000
0x2d 9 0x00 0x1e7c 0x0024 0x0575.7f2215ed 0x00000000 0x0000.000.00000000 0x00000000 0x00000000
0x2e 9 0x00 0x1e5e 0x0029 0x0575.7f221526 0x008007cf 0x0000.000.00000000 0x00000001 0x00000000
0x2f 9 0x00 0x1e78 0x000e 0x0575.7f2215fa 0x008007ed 0x0000.000.00000000 0x00000001 0x00000000

我们可以看到在回滚段头的0X16 槽里,有一个状态为10,ACTIVE的事务.但他的DBA为0x00000000,即没有用到UNDO DATABLOCK!
这里如果执行

alter session close database link a;

oracle首先会去检查是否有未提交的事务. 如果有,就会提示错误:

在B机器 SESSION #1 执行:

alter">heyf@VOUCHER>alter session close database link a;
ERROR:
ORA-02080: database link is in use

commit">heyf@VOUCHER>commit ;

Commit complete.

alter">heyf@VOUCHER>alter session close database link a;

Session altered.

正常关闭!


到这里为止,我们也基本明白了,为什么最上面的例子中会报这个错误,从而也知道当select一个DBLINK的时候,需要一个COMMIT来释放这个事务.

那么我们为什么要关闭 DBLINK呢?

CLOSE DATABASE LINK Clause

Oracle 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.

show">heyf@VOUCHER>show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4

也就是说,如果一个SESSION中使用超过这个参数的DBLINK,就会报错.
另外,我们在SESSION中使用一个DBLINK,那么就会在REMOTE DB上创建一个SESSION,(同个DBLINK只创建一个SESSION_ID),考虑到REMOTE DB的SESSION 压力,我们需要关闭不用的DBLINK;

create">heyf@VOUCHER>create database link a1 connect to heyf identified by heyf using 'heyf';

Database link created.

create">heyf@VOUCHER>create database link a2 connect to heyf identified by heyf using 'heyf';

Database link created.

create">heyf@VOUCHER>create database link a3 connect to heyf identified by heyf using 'heyf';

Database link created.

create">heyf@VOUCHER>create database link a4 connect to heyf identified by heyf using 'heyf';

Database link created.

heyf@VOUCHER >
heyf@VOUCHER >
select">heyf@VOUCHER>select count(*) from t2@a1 ;

COUNT(*)
----------
0

select">heyf@VOUCHER>select count(*) from t2@a2 ;

COUNT(*)
----------
0

select">heyf@VOUCHER>select count(*) from t2@a3 ;

COUNT(*)
----------
0

select">heyf@VOUCHER>select count(*) from t2@a4 ;

COUNT(*)
----------
0

select">heyf@VOUCHER>select count(*) from t2@a ;
select count(*) from t2@a
*
ERROR at line 1:
ORA-02020: too many database links in use

小结一下:

1. SELECT over a database link need a COMMIT or ROLLBACK to release the transaction ;

2. The number of dblink used in one session can not exceed the initial parameter named open_links;

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

转载于:http://blog.itpub.net/703656/viewspace-1008651/