Oracle SQL | Category Archives: remote statements

It is well known thing and you can even find it on MOS, but I have a little more simple script for it, so I want to show little example.

First of all we need to start script on local database:

SQL>                                                                                                                                                                   
SQL> @transactions/global.sql
Enter filters(empty for any)...
Sid           :
Globalid mask :
Remote_db mask:

 INST_ID  SID    SERIAL# USERNAME REMOTE_DB REMOTE_DBID TRANS_ID         DIRECTION   GLOBALID                                           EVENT                      
-------- ---- ---------- -------- --------- ----------- ---------------- ----------- -------------------------------------------------- ---------------------------
       1  275       4469 XTENDER  BAIKAL     1742630060 8.20.7119        FROM REMOTE 4241494B414C2E63616336656437362E382E32302E37313139 SQL*Net message from client

Then we need to copy GLOBALID of interested session and run script on database that shown in column REMOTE_DBID, but with specifieng GLOBALID:

SQL>                                                                                                                                                                                                 
SQL> conn sys/syspass@baikal as sysdba
Connected.

======================================================================
=======  Connected to  SYS@BAIKAL(baikal)(BAIKAL)
=======  SID           203
=======  SERIAL#       38399
=======  SPID          6536
=======  DB_VERSION    11.2.0.4.0
======================================================================

SQL> @transactions/global.sql
Enter filters(empty for any)...
Sid           :
Globalid mask : 4241494B414C2E63616336656437362E382E32302E37313139
Remote_db mask:

INST_ID   SID    SERIAL# USERNAME  REMOTE_DB  REMOTE_DBID TRANS_ID   DIRECTION   GLOBALID                                            STATE                     
------- ----- ---------- --------- ---------- ----------- ---------- ----------- --------------------------------------------------  --------------------------
      1     9      39637 XTENDER   BAIKAL      1742630060 8.20.7119  TO REMOTE   4241494B414C2E63616336656437362E382E32302E37313139  [ORACLE COORDINATED]ACTIVE

It’s quite simple and fast.

Recently on our russian forum we discussed about distributed transaction: is “insert /*+ append */ into x select * from t@dblink” a distributed transaction or not?
According to the documentation – no:

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database

And Tom Kyte said the same:

In the following, we do not have a distributed transaction when we just insert /*+ append */ into local_table select * from remote@table; – we just have a single site transaction.

But v$global_transaction(but v$transaction), another part of documentation and v$lock(type=’DX’) assure that it is.

Distributed statement: A statement that accesses data on two or more distinct nodes/instances of a distributed database.
A remote statement accesses data on one remote node of a distributed database.

Who is right?

But it not so interesting as my another finding: commit after simple “select * from dblink” would be “read-only” commit on local db, but on remote it became “read-only” rollback! Except cases when there was any dml in that transaction(with no difference local or not). But how Oracle determining: read-only or not, execute commit or rollback?

Yet another interesting thing: If we do a few times “commit” after “select from dblink”, then, as expected, rollback will be executed only once on remote. But when we closing our session, there is another commit on remote(real read-only commit).

Tests below: Continue reading→

Category Archives: remote statements

How to find out session info about session that comes from remote database through dblink

Deceptive commit after select from dblink.