DB Links are not closed after testing

rjesse · November 29, 2017, 12:44pm

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

JohnDorlon · November 29, 2017, 12:58pm

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

rjesse · November 29, 2017, 1:09pm

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

JohnDorlon · November 29, 2017, 1:35pm

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

rjesse · November 29, 2017, 1:49pm

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

JohnDorlon · November 29, 2017, 2:01pm

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.

NormTeamT · November 29, 2017, 2:51pm

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.

JohnDorlon · November 30, 2017, 6:26am

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

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>

JohnDorlon · November 30, 2017, 6:39am

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…

NormTeamT · November 30, 2017, 6:50am

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

NormTeamT · November 30, 2017, 6:52am

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]

JohnDorlon · November 30, 2017, 7:01am

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:

test current session for transaction. If not in transaction, proceed. If in transaction, create a separate session.
for each link…test it, rollback, close link.
if a separate session was created, close it.

rjesse · November 30, 2017, 7:17am

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

JohnDorlon · November 30, 2017, 8:09am

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.

NormTeamT · November 30, 2017, 8:53am

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]

rjesse · November 30, 2017, 10:00am

Ahh, good call™!

Thanks John!

Rich

NormTeamT · November 30, 2017, 10:48am

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.

Topic		Replies	Views
Testing DBLink returns "ORA-01086: savepoint 'DBLINKTEST' never established in this session or is invalid" Toad for Oracle & DevOps Toolkit	5	5936	September 5, 2013
Commit or rollback when closing connection Toad for Oracle - Beta	3	487	October 10, 2022
DBAs Yelling At My Team for Too Many Open Toad Connections? Toad Data Point & Toad Intelligence Central	5	1226	September 3, 2019
Bug: Unable to close tabs Toad for Oracle & DevOps Toolkit	9	1769	January 17, 2020
Beta Released (13.2.0.151) Toad for Oracle - Beta	0	909	June 6, 2019

DB Links are not closed after testing

IN_TRANSACTION

IN_TRANSACTION

LINK_OPENED

IN_TRANSACTION

IN_TRANSACTION

IN_TRANSACTION

Related topics