CODEWITHSUNDEEP
oracle-databaseplsqldeveloper
ML ML
ML ML

Reputation: 61

Select * from table@dblink in PL/SQL Developer

When I run query select * from table@dblink in PL/SQL Developer,transaction commit/rollback icons are activated, but then if I use Fetch last page these icons are disabled. Why is this happening?

Upvotes: 1

Views: 4773

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

I think that PL/SQL is trying to remove useless transactions to help avoid session errors. It seems that whenever you press the "Fetch last page" button, PL/SQL Developer runs commit write batch if the statement contains a database link, if there are no transactions currently open in the session, and if the statement does not include FOR UPDATE.

Those are a lot of weird conditions, but they seem to ensure that the program won't commit when it shouldn't. I assume PL/SQL Developer is using commit write batch to use less resources than a normal commit. That guess is based on the number returned by this query increasing when I hit the button. (There's another statistic for user commits, and that number does not increase.)

select value
from v$mystat
join v$statname on v$mystat.statistic# = v$statname.statistic#
where lower(display_name) = 'commit batch performed';

This behavior is a little odd, but it could help prevent some errors in the session. For example, if you later try to run alter session enable parallel dml the session would throw the error ORA-12841: Cannot alter the session parallel DML state within a transaction. By committing the (worthless) transaction, you avoid some of those errors.

Upvotes: 0

thatjeffsmith
thatjeffsmith

Reputation: 22427

Querying over a db_link flips the 'we have a transaction' switch in the data dictionary

In most tools, you'll get a prompt for COMMIT or an indicator of an open transaction whenever you query against a DB_LINK.

That's because you're doing 'something' that's not clear to us in a different database. Your 'SELECT' could have side effects which require a COMMIT/ROLLBACK, or as Tom would say

'If you are distributed, you would want to commit to finish off anything that was implicitly started on the remote site.'

Upvotes: 1

Related Questions