Infrastructure at your Service

Franck Pachot

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 [email protected]_DB_LINK;
select * from [email protected]_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 [email protected]_DB_LINK;


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 |

   - fully remote statement

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

---------- --- --- ------ ------------ --- --- --------------------- ----------
         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.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist