This was first published on https://blog.dbi-services.com/how-to-disable-all-database-links (2014-11-04)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
Hi Franck,
Brilliant tip!. Although, I believe it would work when there aren’t any on-going transactions?
Regards, Suntrupth
Ah, Sorry. missed the ” disable all database links before the open” part :).
Thanks, Suntrupth
The better way is to take the listener down
If you stop the remote database listener, nobody will access to it, which is not the goal.