SELECT也需要COMMIT
SELECT (DBLINK) 需要 COMMIT來釋放事務;
[@more@]業務環境:
A機器10.0.69.101 -- heyf
B機器10.2.226.24 -- VOUCHER
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)
)
)
database link a connect to heyf identified by heyf using 'heyf';
Database link created.
or replace procedure proc_t1
2 as
3 v_cnt number;
4 begin
5 select count(*) into v_cnt from ;
6 execute immediate 'alter session close database link A';
7 end;
8 /
Procedure created.
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
session close database link a;
ERROR:
ORA-02081: database link is not open
heyf =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.69.101 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = heyf)
)
)
database link a connect to heyf identified by heyf using 'heyf';
Database link created.
or replace procedure proc_t1
2 as
3 v_cnt number;
4 begin
5 select count(*) into v_cnt from ;
6 execute immediate 'alter session close database link A';
7 end;
8 /
Procedure created.
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
session close database link a;
ERROR:
ORA-02081: database link is not open
引出問題:
ERROR at line 1:
ORA-02080: database link is in use
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.
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.
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 的執行在本機產生了一個事務,但沒有COMMIT或者ROLLBACK,這時如果要執行alter session close database link A；ORACLE認為還有事務在這個DBLINK上面,所以不能關閉這個DBLINK,於是就報ORA-02080: database link is in use 錯誤!
下面我們來深入分解一下:
在B機器上起一個SESSION #1:
在B機器上起一個SESSION #1:
count(*) from
2 ;
COUNT(*)
----------
0
xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
4 22 7808 0 0 0 0
2 ;
COUNT(*)
----------
0
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
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 執行:
session close database link a;
ERROR:
ORA-02080: database link is in use
;
這裡如果執行
alter session close database link a;
oracle首先會去檢查是否有未提交的事務. 如果有,就會提示錯誤:
在B機器 SESSION #1 執行:
session close database link a;
ERROR:
ORA-02080: database link is in use
;
Commit complete.
session close database link a;
Session altered.
正常關閉!
到這裡為止,我們也基本明白了,為什麼最上面的例子中會報這個錯誤,從而也知道當select一個DBLINK的時候,需要一個COMMIT來釋放這個事務.
那麼我們為什麼要關閉 DBLINK呢?
正常關閉!
到這裡為止,我們也基本明白了,為什麼最上面的例子中會報這個錯誤,從而也知道當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.
parameter open_links
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
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.
parameter open_links
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
也就是說,如果一個SESSION中使用超過這個引數的DBLINK,就會報錯.
另外,我們在SESSION中使用一個DBLINK,那麼就會在REMOTE DB上建立一個SESSION,(同個DBLINK只建立一個SESSION_ID),考慮到REMOTE DB的SESSION 壓力,我們需要關閉不用的DBLINK;
database link a1 connect to heyf identified by heyf using 'heyf';
Database link created.
database link a2 connect to heyf identified by heyf using 'heyf';
Database link created.
database link a3 connect to heyf identified by heyf using 'heyf';
Database link created.
database link a4 connect to heyf identified by heyf using 'heyf';
Database link created.
>
>
count(*) from ;
COUNT(*)
----------
0
count(*) from ;
COUNT(*)
----------
0
count(*) from ;
COUNT(*)
----------
0
count(*) from ;
COUNT(*)
----------
0
count(*) from ;
select count(*) from
*
ERROR at line 1:
ORA-02020: too many database links in use
Database link created.
database link a2 connect to heyf identified by heyf using 'heyf';
Database link created.
database link a3 connect to heyf identified by heyf using 'heyf';
Database link created.
database link a4 connect to heyf identified by heyf using 'heyf';
Database link created.
>
>
count(*) from ;
COUNT(*)
----------
0
count(*) from ;
COUNT(*)
----------
0
count(*) from ;
COUNT(*)
----------
0
count(*) from ;
COUNT(*)
----------
0
count(*) from ;
select count(*) from
*
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 為什麼忘記commit也會造成select查詢的效能問題MIT
- 為什麼忘記commit也會造成select查詢的效能問題（SELECT產生Redo的情形）MIT
- 【轉載】為什麼忘記commit也會造成select查詢的效能問題MIT
- Oracle DML（非select） 操作不commit 對select的影響OracleMIT
- update/select也可能產生buffer busy waits。AI
- 為什麼MCU也需要AI？AI
- 小型資料中心也需要管理效率
- 前端也需要了解的 JSONP 安全前端JSON
- 〖使用iBatis做select後不提交也不回滾問題〗BAT
- IT人為什麼也需要懂點FMEA？
- 專案經理也需要“不講理”（轉）
- 延遲塊清理介紹(select也會產生redo的原因)
- git commit後如何取消commitGitMIT
- on commit delete rows and on commit preserve rowsMITdelete
- CommitMIT
- eclipse svn commit 報錯 Commit blocked by pre-commit hookEclipseMITBloCHook
- Git commit 之後，想撤銷 commitGitMIT
- 圓心科技衝刺IPO：獨角獸也需要反思
- 程式設計師反捲指南 - 打工人也需要生活程式設計師
- 『翻譯』你也許並不需要構建工具
- 個人也需要進行知識管理(zt 計世網)
- 軟體企業也需要做好自己的定位 (轉)
- ERP專案實施也需要人性化
- create table as select where 1=0會把null和not null屬性也帶上Null
- svn commitMIT
- Asynchronous CommitMIT
- 軟體開發人員需要的不僅是技術，也不是文件，也不是管理，而是……
- 為什麼數字化轉型不僅需要技術，也需要人的改變？
- select 下拉框用 Select select = new Select (element) 方法失敗
- 也許沒有哪個春天，比現在更需要AIAI
- 前端也需要好好的精進自己的演算法前端演算法
- 前端也需要了解的資料結構-連結串列前端資料結構
- commit 與do_key('commit_form')區別MITORM
- git commit --amendGitMIT
- AWS Code CommitMIT
- commit 衝突MIT
- commit_writeMIT
- git commit 彈出編輯器後報錯: Aborting commit due to empty commit message.GitMIT