Why do I get an open transaction when choosing from a database view?

Question

If I execute a simple select statement in the pl / sql developer on the database table, I get a standard set of results, as expected.

Recently, I inserted a request from a stored procedure that was selected from a view, and noticed that the transaction was apparently left open. This has been evaluated using rollback and commit options available in the PL / SQL developer.

A survey of other developers showed that this affects some, but not others, which makes me suspect PL / SQL Developer settings.

Why would this be so? The itelf view has DBLink for another database, but I would not expect this to have any effect.

Any thoughts?

Answer 1

Any SQL statement triggers a transaction in Oracle.

From the manual:

The transaction begins with the first executable SQL statement. A transaction ends when it is completed or canceled, either explicitly with the COMMIT statement, or ROLLBACK, or implicitly when a DDL statement is issued. [...] An SQL statement executable is an SQL statement that generates instance calls, including DML and DDL statements

Most likely, those who do not do this are working in automatic commit mode when the transaction started by the operator is completed immediately after completion.

Others argued that SELECT not DML, but again the manual clearly states :

Data Processing Language (DML) statements query or manipulate data in existing schema objects. They allow you to:
* Get or get data from one or more tables or views (SELECT)
* Add new rows of data to a table or view (INSERT)
[...]

Answer 2

Unlike your expectation, it looks like the database link is the source of an open transaction. I noticed a behavior like this before running SELECT queries on remote tables in PL / SQL Developer.

To quote Tom Kyte ( source ):

distributed material starts the transaction "just in case".

EDIT : "Any SQL statement triggers a transaction in Oracle?" No, this is not so, and here is a demonstration of this. This demo uses the V $ TRANSACTION data dictionary view, which lists the active transactions. All this works on my local Oracle XE database, which has no other users besides me associated with it.

During this demonstration, we will use the following table. It contains only one column:

  SQL> desc test;
  Name Null?  Type
  ----------------------------------------- -------- - ---------------------------
  A NUMBER (38)

 SQL> select count (*) from v $ transaction;

   COUNT (1)
 ----------
          0

There are currently no active transactions. Let run the SQL query on this table:

  SQL> select * from test;

          A
 ----------
          2

 SQL> select count (*) from v $ transaction;

   COUNT (1)
 ----------
          0

There are still no active transactions. Now let's do something that starts the transaction:

  SQL> insert into test values ​​(1);

 1 row created.

 SQL> select count (*) from v $ transaction;

   COUNT (1)
 ----------
          one

As expected, we now have an active transaction.

  SQL> commit;

 Commit complete.

 SQL> select count (*) from v $ transaction;

   COUNT (1)
 ----------
          0

After the transaction, it is no longer active.

Now create a link to the database. I am using Oracle XE, and the following creates a database link from my Oracle XE instance back to myself:

  SQL> create database link loopback_xe connect to user identified by password using 'XE';

 Database link created.

Now let's see what happens when we select from the table by the database link:

  SQL> select count (*) from v $ transaction;

   COUNT (1)
 ----------
          0

 SQL> select * from test@loopback _xe;

          A
 ----------
          2
          one

 SQL> select count (*) from v $ transaction;

   COUNT (1)
 ----------
          one

As you can see, simply selecting from a remote table opens a transaction.

I’m not sure exactly what is needed to complete or rollback here, but I must admit that I don’t know what happens to distributed transactions, which probably contains the answer.

Answer 3

You absolutely cannot open a transaction strictly using a regular request. You can open it using the database link. The guy who posted the link to the doctors intentionally or completely carelessly left the second sentence.

"The operation in the Oracle database begins when the first executable SQL query. The executable SQL statement is the SQL statement that generates instance calls, including DML and DDL statements."

SELECT is neither DML nor DDL. Also TRIVIAL really checks this out. I don’t want to come off like a troll, but it’s really annoying when people just throw answers on the forum to try to get points, and the answers are complete rubbish.

Read the rest of the document and CHECK IT FIRST.

• enter a session
• run selection
• see if you have an open transaction by attaching v$Session (for your session) to v$transaction .

If the record returns, you have a transaction. If not, you will not.

Answer 4

Please note that according to the Oracle 11g Administrator Guide , if you use a plain old SELECT from a database link, you will start a transaction that (or rollback).