2 Replies Latest reply: Dec 14, 2012 9:21 AM by 926398 RSS

    drop or alter database link question...

    926398
      I have a database that uses a dblink to connect to a remote database to use a sequence for id's. The remote database is a 12 node rac cluster and when the database link was initially created the admin that created it only specified one of the nodes in the rac cluster in the comnnection description for the database link.
      We want to change the link to include both rac nodes but we have a bunch of synonyms that point to the database link and I do not want to invalidate the synonyms.
      If I alter the dblink as opposed to drop and recreate then will the synonyms become unusable or invaildated?
      Thanks.
        • 1. Re: drop or alter database link question...
          JustinCave
          First, you cannot change the connection information for a database link using the ALTER DATABASE LINK statement. You'll need to drop and recreate the database link to do so. From the documentation
          You cannot use this statement to change the connection or authentication user associated with the database link. To change user, you must re-create the database link.
          Second, are you saying that the TNS entry was specified explicitly in the CREATE DATABASE LINK statement rather than specifying a TNS alias (i.e. an entry in a tnsnames.ora file)? Normally, you'd specify a TNS alias when you create the database link in which case you could simply modify that alias rather than changing the database link.

          Third, synonyms do not become unusable or invalid just because the underlying object does not exist
          SQL> create synonym invalid_synonym
            2    for not_a_user.not_a_table@not_a_link;
          
          Synonym created.
          
          SQL> select status from dba_objects where object_name = 'INVALID_SYNONYM';
          
          STATUS
          -------
          VALID
          You'll get an error if you try to use the synonym, of course, but it won't be invalid. As long as you fix the database link before someone tries to use the synonym, there is no issue.

          Justin
          • 2. Re: drop or alter database link question...
            926398
            Thank you Justin for that I really appreciate you taking the time to explain that for me.
            Have a great day.