Toad World® Forums

DB Links are not closed after testing

Howdy!

I’m creating several DB links via the SB in 13.0.0.12. After the number of DB link tests surpasses the “open_links” init.ora parameter, I get:

ORA-02020: too many database links in use

The workaround is to close the SB (or the DB connection, obviously), but it could also lead to hitting “open_links_per_instance”, which would be arguably worse, depending on the installation/usage of that particular Oracle DB.

A “DBMS_SESSION.CLOSE_DATABASE_LINK(db_link_name_here);” should work after the “rollback to savepoint DBLinkTest;”. It would need to be after the rollback to prevent “ORA-02080: database link is in use” errors.

Thanks!
Rich

Hi Rich.

Thanks for the info. I thought a rollback would close the link. Guess I was wrong about that! Maybe it used to in older versions of Oracle? It’s been a while since I’ve gone anywhere near that code.

-John

Hey John,

I have a procedure that grabs data from multiple DB links, and that code has a comment from 2014 about the ORA-2080, which puts the DB with those errors at v11.2.0.3.

Before that, I don’t know. I was at Oracle 10.1, which was held together with irregular shared pool flushing and animal sacrifices (aka “cheeseburgers”) to the Oracles.

Thanks John!
Rich

I’m trying to add this for you, but I keep getting ORA-02080.


– Session: JDORLON@AZURE_12C_PLUG
– Timestamp: 15:34:14.594
savepoint DBLinkTest;


– Session: JDORLON@AZURE_12C_PLUG
– Timestamp: 15:34:14.661
SELECT ‘Link Name : “AZURE_12CR2_PLUG.CLOUDAPP.NET”’ || chr(13) || chr(10) ||
‘Connection : Successful’|| chr(13) || chr(10) ||
'DB Name : ’ || n.GLOBAL_NAME || chr(13) || chr(10) ||
'DB Version : ’ || v.banner info
FROM v$version@“AZURE_12CR2_PLUG.CLOUDAPP.NET” v, GLOBAL_NAME@“AZURE_12CR2_PLUG.CLOUDAPP.NET” n
WHERE ROWNUM = 1;

(it’s not shown in the spool SQL, but I’m closing the cursor here for the above query)


– Session: JDORLON@AZURE_12C_PLUG
– Timestamp: 15:34:17.669
rollback to savepoint DBLinkTest;


– Session: JDORLON@AZURE_12C_PLUG
– Timestamp: 15:34:18.817
begin DBMS_SESSION.CLOSE_DATABASE_LINK(’“AZURE_12CR2_PLUG.CLOUDAPP.NET”’); end;
Error: ORA-02080: database link is in use
ORA-06512: at “SYS.DBMS_SESSION”, line 191
ORA-06512: at line 1

Weirdness! I blame Azure, or any other shade of blue.

If it’s not the double quotes inside of the single quoted literal in the call to CLOSE_DATABASE_LINK, perhaps querying the session’s open links from (G)V$DBLINK might shed some light?

Rich

I tried without the quotes and got the same result. I the following in v$dblink

and even after waiting a bit, I still get the ORA-2080 from this in the editor:

begin DBMS_SESSION.CLOSE_DATABASE_LINK('AZURE_12CR2_PLUG.CLOUDAPP.NET'); end;

same error from ALTER SESSION CLOSE DATABASE LINK AZURE_12CR2_PLUG.CLOUDAPP.NET;

So, yeah, Azure, or any other shade of blue sounds like a good scapegoat to me.

Evening All,

I might be wrong but …

you cannot close a db link that is active. Looking at John’s trace below, a rollback to savepoint has not finished the transaction, so the link is still active. I had these problems years ago - hence the vagueness of my reply - but I think you need a commit or rollback to vlose the transaction prior to the dbms_session.close_databse_link(…)/alter session close database link … calls.

I’m in bed so I’m unable to test the above!

Cheers,

Nirm[TeamT]

On 29 November 2017 21:36:03 GMT+00:00, John Dorlon bounce-jdorlon@toadworld.com wrote:

RE: DB Links are not closed after testing

Reply by John Dorlon
I’m trying to add this for you, but I keep getting ORA-02080.


– Session: JDORLON@AZURE_12C_PLUG
– Timestamp: 15:34:14.594
savepoint DBLinkTest;


– Session: JDORLON@AZURE_12C_PLUG
– Timestamp: 15:34:14.661
SELECT ‘Link Name : “AZURE_12CR2_PLUG.CLOUDAPP.NET”’ || chr(13) || chr(10) ||
‘Connection : Successful’|| chr(13) || chr(10) ||
'DB Name : ’ || n.GLOBAL_NAME || chr(13) || chr(10) ||
'DB Version : ’ || v.banner info
FROM v$version@“AZURE_12CR2_PLUG.CLOUDAPP.NET” v, GLOBAL_NAME@“AZURE_12CR2_PLUG.CLOUDAPP.NET” n
WHERE ROWNUM = 1;

(it’s not shown in the spool SQL, but I’m closing the cursor here for the above query)


– Session: JDORLON@AZURE_12C_PLUG
– Timestamp: 15:34:17.669
rollback to savepoint DBLinkTest;


– Session: JDORLON@AZURE_12C_PLUG
– Timestamp: 15:34:18.817
begin DBMS_SESSION.CLOSE_DATABASE_LINK(’“AZURE_12CR2_PLUG.CLOUDAPP.NET”’); end;
Error: ORA-02080: database link is in use
ORA-06512: at “SYS.DBMS_SESSION”, line 191
ORA-06512: at line 1

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta Forum notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag this post as spam/abuse.

Sent from my Android device with K-9 Mail. Please excuse my brevity.

Nirm, I think you are right (as usual!) about the transaction preventing the DB Link from being closed. When I just rollback to the savepoint, there is still a transaction and I can’t close the link. When I rollback completely, I can close the link. What confuses me is, if there was no transaction before the savepoint, shouldn’t rolling back to the savepoint end the transaction? See below.

SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 30 08:22:55 2017

Copyright © 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Thu Nov 30 2017 08:22:12 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL> select decode(sys.dbms_transaction.local_transaction_id, null, ‘No’, ‘Yes’) as in_transaction
2 from dual;

IN_TRANSACTION

No

SQL>
SQL> savepoint DBLinkTest;

Savepoint created.

SQL>
SQL> select decode(sys.dbms_transaction.local_transaction_id, null, ‘No’, ‘Yes’) as in_transaction
2 from dual;

IN_TRANSACTION

Yes

SQL>
SQL> SELECT ‘Link Opened’ as Link_opened
2 from dual@“AZURE_12CR2_PLUG.CLOUDAPP.NET”;

LINK_OPENED

Link Opened

SQL>
SQL> select decode(sys.dbms_transaction.local_transaction_id, null, ‘No’, ‘Yes’) as in_transaction
2 from dual;

IN_TRANSACTION

Yes

SQL>
SQL> rollback to savepoint DBLinkTest;

Rollback complete.

SQL>
SQL> select decode(sys.dbms_transaction.local_transaction_id, null, ‘No’, ‘Yes’) as in_transaction
2 from dual;

IN_TRANSACTION

Yes

SQL>
SQL> exec DBMS_SESSION.CLOSE_DATABASE_LINK(‘AZURE_12CR2_PLUG.CLOUDAPP.NET’);
BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK(‘AZURE_12CR2_PLUG.CLOUDAPP.NET’); END;

ERROR at line 1:
ORA-02080: database link is in use
ORA-06512: at “SYS.DBMS_SESSION”, line 191
ORA-06512: at line 1

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> select decode(sys.dbms_transaction.local_transaction_id, null, ‘No’, ‘Yes’) as in_transaction
2 from dual;

IN_TRANSACTION

No

SQL>
SQL> exec DBMS_SESSION.CLOSE_DATABASE_LINK(‘AZURE_12CR2_PLUG.CLOUDAPP.NET’);

PL/SQL procedure successfully completed.

SQL>
SQL>

Oh, it looks like rolling back to a savepoint does not delete the savepoint. It can be reused.

So the only way to really close the DB link would be to do a full commit or rollback. I’ll make it do that, if there was no transaction going on before the DB Link testing began. I don’t want to automatically commit or rollback anything else…

Hi John,

when you begin to use a database link - even for a SELECT - Oracle opens

a transaction at the far end. This transaction remains in force until

closed explicitly. The reason, as far as I remember, is because setting

up a database link for use, from scratch, is an “expensive” database

thing to do. Oracle effectively caches the database link setup in case

you come back and use it again in the same session.

Until explicitly closed, it remains open. You can see then when closing

Toad after a SELECT down a database link, there’s a transaction open, so

Toad prompts you to commit or rollback (assuming you have the correct

Toad options set of course!)

The transaction can be closed by:

  • COMMIT [FORCE]

  • ROLLBACK

  • Session disconnect

But until such time as that transaction closes, the link itself cannot

be closed.

I suffered this problem many years ago while still a fledgling DBA and

it took me ages to work out how I should fix it.

Would it be a good idea, or not, to do the database link testing in a

separate connection especially for this purpose? A bit of a faff I

suppose, but at least it wouldn’t affect anything you are doing elsewhere.

When I say “separate connection” I mean always, not just according to

the Toad Options to use separate connections for various things.

Cheers,

Norm. [TeamT]

On 30/11/17 14:40, John Dorlon wrote:

*RE: DB Links are not closed after testing

Reply by John Dorlon

Oh, it looks like rolling back to a savepoint does not delete the

savepoint. It can be reused.

So the only way to really close the DB link would be to do a full commit

or rollback. I’ll make it do that, if there was no transaction going

on before the DB Link testing began. I don’t want to automatically

commit or rollback anything else…

Hi John,

On 30/11/17 14:26, John Dorlon wrote:

What confuses me is, if there was no transaction before the savepoint,

shouldn’t rolling back to the savepoint end the transaction?

What effectively happens at the remote end of the DB Link is:

  • Connection made, new transaction started;

  • Your savepoint is created;

  • You test the DB Link - it works;

  • You rollback to savepoint;

  • The transaction is still there.

I’m afraid that you must COMMIT or ROLLBACK the entire transaction to

get the DB Link to close.

Cheers,

Norm. [TeamT]

Hi Norm,

I understand about a transaction beginning when a DB Link is used. That’s why I created the savepoint before testing the DB Link, and then rolled back to the savepoint after, thinking that would be enough. I’m still not sure why it isn’t enough…the best I can come up with is that the DB Link and savepoint were using the same transaction, and as long as the transaction still exists, the DB Link is tied to it.

Using a new session would definitely make this easier, but I’ll avoid doing that if possible since some users are limited to a single session in a database…so I think I’ll do this:

  1. test current session for transaction. If not in transaction, proceed. If in transaction, create a separate session.

  2. for each link…test it, rollback, close link.

  3. if a separate session was created, close it.

Just to add to this, if there’s only one session in Toad, then the CREATE DATABASE LINK has already done two implicit COMMITs, one before and one after:

https://docs.oracle.com/database/121/SQLRF/statements_1001.htm#SQLRF30001

(assuming that transactional logic hasn’t changed too much before or after Oracle 12.1)

Our ERP system COMMITs after every stinking DML statement (even when 2M rows are being updated one at a time), so it’s been awhile since I’ve had to deal with real transactions in Oracle!

Rich

Yeah, the test is performed right after a create, and in that case, there won’t be a transaction and all is well. But it’s also possible to just go in there and test without creating first, so there could be a transaction already going when the test begins.

Done for next beta.

Hi John,

On 30/11/17 15:02, John Dorlon wrote:

I understand about a transaction beginning when a DB Link is used.

That’s why I created the savepoint before testing the DB Link, and then

rolled back to the savepoint after, thinking that would be enough. I’m

still not sure why it isn’t enough…the best I can come up with is that

the DB Link and savepoint were using the same transaction, and as long

as the transaction still exists, the DB Link is tied to it.

The savepoint isn’t really a new/full transaction, it’s most similar to

an autonomous transaction as per what we can set up in

functions/procedures/packages etc. It’s a specific point within the main

transaction.

So, unfortunately, the main transaction is still active, even when you

rollback to the savepoint - hence why everything works when you fully

rollback/commit.

If the savepoint did create a new transaction, when you committed after

creating the savepoint, that would commit only the changes since the

savepoint was created and we know, that it commits everything (since the

transaction began.)

HTH

Cheers,

Norm. [TeamT]

Ahh, good call™!

Thanks John!

Rich

It appears my replies don’t work on the forum when I reply by email. Very strange!

Sent from my Android device with K-9 Mail. Please excuse my brevity.