How to disable all database links

By Franck Pachot

.
A frequent scenario: you refresh test from production with a RMAN duplicate. Once the duplicate is done, you probably change dblinks so that they address the test environment instead of the production one. But are you sure that nobody will connect in between and risk to access production from the test environement? You want to disable all db links until you have finished your post-duplicate tasks.

I know two solutions for that. The first one is for 12c only. You can add the NOOPEN to the duplicate statement. Then the duplicate leaves the database in MOUNT and you can open it in restricted mode and do anything you want before opening it to your users.

But if you’re still in 11g you want to be able to disable all database links before the open. That can be done in the instance, steeing the open_links parameter to zero in your spfile.

Let’s see an example:

SQL> alter system set open_links=0 scope=spfile;
System altered.

I restart my instance:

startup force
ORACLE instance started.
Total System Global Area  943718400 bytes
Fixed Size                  2931136 bytes
Variable Size             641730112 bytes
Database Buffers          188743680 bytes
Redo Buffers                5455872 bytes
In-Memory Area            104857600 bytes
Database mounted.
Database opened.

And here is the result:

SQL> select * from dual@LOOPBACK_DB_LINK;
select * from dual@LOOPBACK_DB_LINK
                   *
ERROR at line 1:
ORA-02020: too many database links in use

With that you prevent any connection through database links until you change them to address the test environment. Then:

SQL> alter system reset open_links;
System altered.

SQL> shutdown immediate;
SQL> startup

and then:

SQL> show parameter open_links

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                           integer     4
open_links_per_instance              integer     4

SQL> set autotrace on explain
SQL> select * from dual@LOOPBACK_DB_LINK;

D
-
X


Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |     1 |     2 |     2   (0)| 00:00:01 |        |
|   1 |  TABLE ACCESS FULL     | DUAL |     1 |     2 |     2   (0)| 00:00:01 |    DB1 |
----------------------------------------------------------------------------------------

Note
-----
   - fully remote statement

SQL> set autotrace off
SQL> select * from V$DBLINK;

DB_LINK
----------------------------------------------------------------------------------------------------
  OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH     CON_ID
---------- --- --- ------ ------------ --- --- --------------------- ----------
LOOPBACK_DB_LINK
         0 YES YES UNKN              0 YES YES                     1          0

Yes, that was the occasion to see how to check dblink usage from the execution plan and from V$DBLINK.