Oracle: Database Link

Closing/opening database links

begin

  for open_db_link in (
    select
      db_link,
      in_transaction
    from
      v$dblink
  ) loop

    if open_db_link.in_transaction <> 'NO' then
       dbms_output.put_line('Cannot close db link ' || open_db_link.db_link || ' as it is in a transaction.');
    else

       execute immediate 'alter session close database link ' || open_db_link.db_link;
       dbms_output.put_line('db link ' || open_db_link.db_link || ' was closed.');

    end if;

  end loop;

end;
/

Executing a PL/SQL procedure in the remote database

select
  func_name @ link_name('param one', 'param two')
from
  dual;

Executing a DDL statement in the remote database

begin
   dbms_utility.exec_ddl_statement@remote_db('create table t1 (id number)');
end;
/

Execution plans

explain plan for
select count(*) from xyz@db_far_away;
select * from dbms_xplan.display();
----------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Cost (%CPU)| Time     | Inst   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|              |     1 | 12034  (14)| 00:00:01 |        |
|   1 |  SORT AGGREGATE        |              |     1 |            |          |        |
|   2 |   INDEX FAST FULL SCAN | XYZ_IX       |    33M| 12034  (14)| 00:00:01 | XINST~ |
----------------------------------------------------------------------------------------
Note
-----
   - fully remote statement

See also