1 2 Previous Next 16 Replies Latest reply: Nov 8, 2013 3:57 AM by Anar Godjaev RSS

    How could I know which db_link is linked to which remote DB resources ?

    teradata0802

      we use oracle 11g on win2008R2.

      How could I know which db_link is linked to which remote DB resources ?

        • 1. Re: How could I know which db_link is linked to which remote DB resources ?
          Ramin Hashimzadeh

          select * from dba_db_links

           

          ----

          Ramin Hashimzade

          • 2. Re: How could I know which db_link is linked to which remote DB resources ?
            DK2010

            Hi,

             

            You can generate the DDL for the Db link and get the connection string from there, match with your TNS Entry.

             

            set long 10000  lin 200 pages 100

            select dbms_metadata.get_ddl('db_link',a.db_link,a.owner) from dba_db_links a;

             

            HTH

            • 3. Re: How could I know which db_link is linked to which remote DB resources ?
              Anar Godjaev

              Hi,

               

              select * from dba_db_links

               

              And

               

              SET LONG 9000 — to print the complete string

              SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,a.db_link,a.owner) FROM dba_db_links a;

               

              More Informatin please check below link:

               

              Validate Database Links – Script » Biju's Oracle Notes

               

              Thank you

              • 4. Re: How could I know which db_link is linked to which remote DB resources ?
                Mark D Powell

                DK, only a 10.2.0.5 system the use of lower case for db_link results in an error being returned from dbms_meatadata.  Your sql works with 'DB_LINK' specified.  Why dbms_metadata does not do an upper on the object type parameter is beyond me.  To bad I do not have a 11.2 system handy right now.

                 

                HTH -- Mark D Powell --

                • 5. Re: How could I know which db_link is linked to which remote DB resources ?
                  teradata0802

                  Thank you.

                  How could I know the definition of DB_LINK ?

                   

                  About duplicate posts, I can not post properly in specific network environment.That is why posts are duplicate. I am very sorry about dupulicate post.

                  I will be more careful to not to do that.Sorry.

                  • 6. Re: How could I know which db_link is linked to which remote DB resources ?
                    sb92075

                    teradata0802 wrote:

                     

                    Thank you.

                    How could I know the definition of DB_LINK ?

                     

                    About duplicate posts, I could not post properly in specifice network environment . I am very sorry about that.

                    If you can directly access tnsnames.ora file on the DB Server, you can obtain the answer you desire.

                    • 8. Re: How could I know which db_link is linked to which remote DB resources ?

                      There is no side effect to executed environment.

                       

                      We are making sensitive system so I would like to know about the side effect of the below validation script.

                      Validate Database Links – Script » Biju's Oracle Noteshttp://bijoos.com/oraclenotes/2013/222

                      • 9. Re: How could I know which db_link is linked to which remote DB resources ?
                        sb92075

                        840105 wrote:

                         

                        There is no side effect to executed environment.

                         

                        We are making sensitive system so I would like to know about the side effect of the below validation script.

                        Validate Database Links – Script » Biju's Oracle Noteshttp://bijoos.com/oraclenotes/2013/222

                         

                        Some, many, most professionals would test on a test system & database to determine any side effects.

                        What is your excuse for not doing so yourself?

                        there is no side effect when the script is not run.

                        what possible side effects are you concerned about?

                        • 10. Re: How could I know which db_link is linked to which remote DB resources ?

                          Thank you.

                          We have two Oracle Insight environments.

                          In one Oracle Insight environment, we had browser login error one day and we could not loging that Insight by browser and we were told from Oracle Support that the cause of login error  was DB_LINK is not functioning properly.

                           

                          I did above DB_LINK Validate script in two Insight environment and  result were like the below.

                           

                          Verifying Database Links APR_APP@DEV1
                          ========================================================
                          Checking Link: 1
                          Link Name    : DB_LINK_ARGUS
                          Link Owner   : APR_APP
                          Connect User : INSIGHT_RO_USER
                          Connect To   : SAFE1_ARG
                          $$$$ DB LINK SUCCESSFULLY connected to SAFE1_ARG
                          ---------------------------------------
                          Tests Completed.
                          Other Private Links in the Database
                          Connect as respective owner to validate these.
                          ----------------------------------------------
                          APR_MART :: DB_LINK_ARGUS
                          APR_STAGE :: DB_LINK_ARGUS
                          DSPD_MART :: DB_LINK_ARGUS_APP
                          SYSTEM :: DB_LINK_ARGUS_APP

                          PL/SQL procedure successfully completed.

                          currently spooling to c:\checklinks_APR_APP_.txt

                           

                          Verifying Database Links APR_APP@DEV2
                          ========================================================
                          Checking Link: 1
                          Link Name    : DB_LINK_ARGUS
                          Link Owner   : APR_APP
                          Connect User : INSIGHT_RO_USER
                          Connect To   : SAFE2_ARG
                          $$$$ DB LINK SUCCESSFULLY connected to SAFE2_ARG
                          ---------------------------------------
                          Tests Completed.
                          Other Private Links in the Database
                          Connect as respective owner to validate these.
                          ----------------------------------------------
                          APR_MART :: DB_LINK_ARGUS
                          APR_STAGE :: DB_LINK_ARGUS

                          PL/SQL procedure successfully completed.

                          currently spooling to c:\checklinks2_APR_APP_.txt

                           

                           

                           

                          declare
                          *
                          ERROR at line 1:
                          ORA-00942: table or view does not exist
                          ORA-06512: at line 19


                          currently spooling to c:\checklinks_APR_STAGE_.txt

                           

                          declare
                          *
                          ERROR at line 1:
                          ORA-00942: table or view does not exist
                          ORA-06512: at line 19


                          currently spooling to c:\checklinks2_APR_STAGE_.txt

                           

                           

                          Verifying Database Links APR_MART@DEV1
                          ========================================================
                          Checking Link: 1
                          Link Name    : DB_LINK_ARGUS
                          Link Owner   : APR_MART
                          Connect User : INSIGHT_RO_USER
                          Connect To   : SAFE1_ARG
                          $$$$ DB LINK SUCCESSFULLY connected to SAFE1_ARG
                          ---------------------------------------
                          Tests Completed.
                          Other Private Links in the Database
                          Connect as respective owner to validate these.
                          ----------------------------------------------
                          APR_APP :: DB_LINK_ARGUS
                          APR_STAGE :: DB_LINK_ARGUS
                          DSPD_MART :: DB_LINK_ARGUS_APP
                          SYSTEM :: DB_LINK_ARGUS_APP

                          PL/SQL procedure successfully completed.

                          currently spooling to c:\checklinks_APR_MART_.txt

                           

                          Verifying Database Links APR_MART@DEV2
                          ========================================================
                          Checking Link: 1
                          Link Name    : DB_LINK_ARGUS
                          Link Owner   : APR_MART
                          Connect User : INSIGHT_RO_USER
                          Connect To   : SAFE2_ARG
                          $$$$ DB LINK SUCCESSFULLY connected to SAFE2_ARG
                          ---------------------------------------
                          Tests Completed.
                          Other Private Links in the Database
                          Connect as respective owner to validate these.
                          ----------------------------------------------
                          APR_APP :: DB_LINK_ARGUS
                          APR_STAGE :: DB_LINK_ARGUS

                          PL/SQL procedure successfully completed.

                          currently spooling to c:\checklinks2_APR_MART_.txt

                          • 11. Re: How could I know which db_link is linked to which remote DB resources ?

                            Thank you.

                            I could see the definition of DB_LINK by

                            SET LONG 9000 — to print the complete string

                            SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,A.DB_LINK,A.OWNER),A.OWNER FROM dba_db_links A;

                             

                            two db_link were updated on Oct 18th (next day the trouble happened and both db_link do not exist in the other functioning Oracle Insight DB)

                             

                             

                            • 12. Re: How could I know which db_link is linked to which remote DB resources ?
                              Anar Godjaev

                              Hi,

                              Can you please paste here result :  select * from dba_db_links

                              Thank you


                              • 13. Re: How could I know which db_link is linked to which remote DB resources ?
                                teradata0802

                                Thank you.

                                SQL> select * from DBA_db_links;
                                SYSTEM                                                                         
                                DB_LINK_ARGUS_APP                                                              
                                ARGUS_APP                                                                      
                                safe1_arg                                                                        
                                18-OCT-13                                                                      
                                                                                                               
                                APR_MART                                                                       
                                DB_LINK_ARGUS                                                                  
                                INSIGHT_RO_USER                                                                
                                SAFE1_ARG                                                                        
                                18-JUN-13                                                                      
                                                                                                               
                                APR_APP                                                                        
                                DB_LINK_ARGUS                                                                  
                                INSIGHT_RO_USER                                                                
                                SAFE1_ARG                                                                        
                                18-JUN-13                                                                      
                                                                                                               
                                APR_STAGE                                                                      
                                DB_LINK_ARGUS                                                                  
                                INSIGHT_RO_USER                                                                
                                SAFE1_ARG                                                                        
                                18-JUN-13                                                                      
                                                                                                               
                                TKD_MART                                                                      
                                DB_LINK_ARGUS_APP                                                              
                                ARGUS_APP                                                                      
                                safe1_arg                                                                        
                                18-OCT-13                                                                      
                                                                                                               

                                SQL> spool off

                                • 14. Re: How could I know which db_link is linked to which remote DB resources ?
                                  Anar Godjaev

                                  Current database you run the SQL> select * from DBA_db_links query then all the results you get are linked from this database to the shown database (colum_name : Host).


                                  For example: I'm connected AZDB database

                                   

                                  select name from v$database

                                   

                                  NAME
                                                AZDB

                                   

                                  And I'm runing this query: select * from dba_db_links in AZDB database.

                                   

                                  OWNERDB_LINKUSERNAMEHOSTCREATED
                                  PUBLICABS_INQOCEAN_TO_ABS_INQAZER04/25/2013

                                   

                                  Result: AZDB database linked to AZER DB through ABS_INQ dblink

                                  1 2 Previous Next