.
\nA 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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Let’s see an example:<\/p>\n
SQL> alter system set open_links=0 scope=spfile;\nSystem altered.\n<\/code><\/pre>\nI restart my instance:<\/p>\n
startup force\nORACLE instance started.\nTotal System Global Area 943718400 bytes\nFixed Size 2931136 bytes\nVariable Size 641730112 bytes\nDatabase Buffers 188743680 bytes\nRedo Buffers 5455872 bytes\nIn-Memory Area 104857600 bytes\nDatabase mounted.\nDatabase opened.\n<\/code><\/pre>\nAnd here is the result:<\/p>\n
SQL> select * from dual@LOOPBACK_DB_LINK;\nselect * from dual@LOOPBACK_DB_LINK\n *\nERROR at line 1:\nORA-02020: too many database links in use\n<\/code><\/pre>\nWith that you prevent any connection through database links until you change them to address the test environment. Then:<\/p>\n
SQL> alter system reset open_links;\nSystem altered.\n\nSQL> shutdown immediate;\nSQL> startup\n<\/code><\/pre>\nand then:<\/p>\n
SQL> show parameter open_links\n\nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\nopen_links integer 4\nopen_links_per_instance integer 4\n\nSQL> set autotrace on explain\nSQL> select * from dual@LOOPBACK_DB_LINK;\n\nD\n-\nX\n\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 272002086\n\n----------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |\n----------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT REMOTE| | 1 | 2 | 2 (0)| 00:00:01 | |\n| 1 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 | DB1 |\n----------------------------------------------------------------------------------------\n\nNote\n-----\n - fully remote statement\n\nSQL> set autotrace off\nSQL> select * from V$DBLINK;\n\nDB_LINK\n----------------------------------------------------------------------------------------------------\n OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH CON_ID\n---------- --- --- ------ ------------ --- --- --------------------- ----------\nLOOPBACK_DB_LINK\n 0 YES YES UNKN 0 YES YES 1 0\n\n\n<\/code><\/pre>\nYes, that was the occasion to see how to check dblink usage from the execution plan and from V$DBLINK.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,59],"tags":[96],"type_dbi":[],"class_list":["post-4079","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-oracle"],"acf":[],"yoast_head":"\n
How to disable all database links - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to disable all database links\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-11-04T16:36:58+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/how-to-disable-all-database-links\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/how-to-disable-all-database-links\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"How to disable all database links\",\"datePublished\":\"2014-11-04T16:36:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/how-to-disable-all-database-links\\\/\"},\"wordCount\":216,\"commentCount\":0,\"keywords\":[\"Oracle\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/how-to-disable-all-database-links\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/how-to-disable-all-database-links\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/how-to-disable-all-database-links\\\/\",\"name\":\"How to disable all database links - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2014-11-04T16:36:58+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/how-to-disable-all-database-links\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/how-to-disable-all-database-links\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/how-to-disable-all-database-links\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to disable all database links\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to disable all database links - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/","og_locale":"en_US","og_type":"article","og_title":"How to disable all database links","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/","og_site_name":"dbi Blog","article_published_time":"2014-11-04T16:36:58+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"How to disable all database links","datePublished":"2014-11-04T16:36:58+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/"},"wordCount":216,"commentCount":0,"keywords":["Oracle"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/","url":"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/","name":"How to disable all database links - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-11-04T16:36:58+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-disable-all-database-links\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to disable all database links"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4079","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4079"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4079\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4079"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4079"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4079"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4079"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}