1 2 Previous Next 18 Replies Latest reply: Jun 7, 2013 7:28 AM by John Thomas RSS

    Drop database link & ORA-02082 Error

    413257
      Hello,

      I have created db link. Now, I would like to DROP it but I got "ORA-02082 a loopback database link must have a connection qualifier".
      I have tried every possible combination but without success.
      Thanks for help

      sasa
        • 1. Re: Drop database link & ORA-02082 Error
          74402
          Can you show us the output from your sqlplus (cut n paste) please so we can see exactly what you are doing ?
          • 2. Re: Drop database link & ORA-02082 Error
            413257
            SQL> select db_link, username, host from user_db_links;

            DB_LINK USERNAME HOST
            ------------------------- ------------ ------------
            CARGODB.IN.CSCARGO.CZ CARGO_ADM cargodb
            LMDB.IN.CSCARGO.CZ CARGO_ADM lmdb
            QWER.IN.CSCARGO.CZ CURRENT_USER speisdb
            SPEISDB.IN.CSCARGO.CZ CARGO_ADM speisdb_rac

            SQL> drop database link qwer;
            drop database link qwer
            *
            ERROR at line 1:
            ORA-02082: a loopback database link must have a connection qualifier

            • 3. Re: Drop database link & ORA-02082 Error
              Barbara Boehmer
              How about?:

              drop database link QWER.IN.CSCARGO.CZ;
              • 4. Re: Drop database link & ORA-02082 Error
                165379
                if you have created public then
                you have to do
                drop public database link 'dsfdsf'
                • 5. Re: Drop database link & ORA-02082 Error
                  413257
                  Thanks for advices but any of your tries didn't work.
                  I also tried some additional combination but without success.
                  Any other help?
                  • 6. Re: Drop database link & ORA-02082 Error
                    74402
                    Can't quite remember this area and don't have access to a db to try it but can you try:

                    drop database link qwer@xxx;

                    or

                    drop public database link qwer@xxx;

                    - It's late, I'm tired and I've had a beer or two so forgive me if this doesn't make sense.

                    Cheers
                    Jeff
                    • 7. Re: Drop database link & ORA-02082 Error
                      Barbara Boehmer
                      The error that you are getting indicates that you are trying to drop a loopback database link without the part that says loopback, but oddly the loopback does not display when you select from user_db_links. I have been able to reproduce your error below and then resolve it. In the following demonstration, I have created a loopback database link, demonstrated that the link works by selecting using it, shown how the link appears when selecting from user_db_links, reproduced your error by attempting to drop the link without the loopback, then successfully dropped it by adding @loopback. Perhaps this will help you find some combination that works. Notice that I did not include the .global when dropping.
                      scott@ORA92> create database link ora92@loopback
                        2  connect to scott
                        3  identified by tiger
                        4  using 'ora92'
                        5  /
                      
                      Database link created.
                      
                      scott@ORA92> select * from dual@ora92@loopback
                        2  /
                      
                      D
                      -
                      X
                      
                      scott@ORA92> select db_link, username, host from user_db_links
                        2  /
                      
                      DB_LINK
                      ----------------------------------------------------------------------------------------------------
                      USERNAME
                      ------------------------------
                      HOST
                      ----------------------------------------------------------------------------------------------------
                      ORA92.GLOBAL@LOOPBACK
                      SCOTT
                      ora92
                      
                      
                      scott@ORA92> drop database link ora92
                        2  /
                      drop database link ora92
                                             *
                      ERROR at line 1:
                      ORA-02082: a loopback database link must have a connection qualifier
                      
                      
                      scott@ORA92> drop database link ora92@loopback
                        2  /
                      
                      Database link dropped.
                      
                      scott@ORA92> select db_link, username, host from user_db_links
                        2  /
                      
                      no rows selected
                      
                      scott@ORA92>
                      • 8. Re: Drop database link & ORA-02082 Error
                        413257
                        Thanks for your example but I still cann't drop the db_link.
                        But I still want to drop it. So could I have some questions?
                        Do I need existence of remote DB to drop db link?
                        Do I need a record in tnsnames.ora for the remote db? (if so on the server or on the client?)
                        • 9. Re: Drop database link & ORA-02082 Error
                          Barbara Boehmer
                          As far as I know, if the database link exists in the data dictionary, you should be able to drop it, whether the database exists or not. There might be a privileges issue. Can you create and drop another link as a test to confirm that you have privileges to do so? Do you know what user created the link that you are trying to drop? Can you connect as that user and then try dropping? What do you see in all_db_links? Is there a link with a similar name? The following are the only combinations that I can think of to try:

                          drop database link qwer;
                          drop database link qwer.cscargo.cz;
                          drop database link qwer.in.cscargo.cz;
                          drop database link qwer@loopback;
                          drop database link qwer.cscargo.cz@loopback;
                          drop database link qwer.in.cscargo.cz@loopback;
                          drop public database link qwer;
                          drop public database link qwer.cscargo.cz;
                          drop public database link qwer.in.cscargo.cz;
                          drop public database link qwer@loopback;
                          drop public database link qwer.cscargo.cz@loopback;
                          drop public database link qwer.in.cscargo.cz@loopback;



                          • 10. Re: Drop database link & ORA-02082 Error
                            413257
                            Hello Barbara,
                            I agree with you that if db link exist I should be able to drop it. If you look into my first reply there is complete select from user_db_links. Select from dba_db_links are the same because I haven't any others db links. I try to drop the db link as owner or as sys. I do not need to think about drop with name "qwer.cscargo.cz" because is wrong (our inner domain is "in.cscargo.cz"). The only difference among others db links is that qwer have "current_user".

                            DB_LINK USERNAME HOST
                            ----------------------- ------------ ------------
                            CARGODB.IN.CSCARGO.CZ CARGO_ADM cargodb
                            LMDB.IN.CSCARGO.CZ CARGO_ADM lmdb
                            QWER.IN.CSCARGO.CZ CURRENT_USER speisdb
                            SPEISDB.IN.CSCARGO.CZ CARGO_ADM speisdb_rac

                            There could be one problem. I created the db link when my db have name "speisdb". I rename my db (using rman - duplicate db) to "speisdev". Could be this source of problems?

                            Here is output of your drop script:

                            drop database link qwer
                            *
                            ERROR at line 1:
                            ORA-02082: a loopback database link must have a connection qualifier


                            drop database link qwer.cscargo.cz
                            *
                            ERROR at line 1:
                            ORA-02024: database link not found


                            drop database link qwer.in.cscargo.cz
                            *
                            ERROR at line 1:
                            ORA-02084: database name is missing a component


                            drop database link qwer@loopback
                            *
                            ERROR at line 1:
                            ORA-02024: database link not found


                            drop database link qwer.cscargo.cz@loopback
                            *
                            ERROR at line 1:
                            ORA-02024: database link not found


                            drop database link qwer.in.cscargo.cz@loopback
                            *
                            ERROR at line 1:
                            ORA-02084: database name is missing a component


                            drop public database link qwer
                            *
                            ERROR at line 1:
                            ORA-02082: a loopback database link must have a connection qualifier


                            drop public database link qwer.cscargo.cz
                            *
                            ERROR at line 1:
                            ORA-02024: database link not found


                            drop public database link qwer.in.cscargo.cz
                            *
                            ERROR at line 1:
                            ORA-02084: database name is missing a component


                            drop public database link qwer@loopback
                            *
                            ERROR at line 1:
                            ORA-02024: database link not found


                            drop public database link qwer.cscargo.cz@loopback
                            *
                            ERROR at line 1:
                            ORA-02024: database link not found


                            drop public database link qwer.in.cscargo.cz@loopback
                            *
                            ERROR at line 1:
                            ORA-02084: database name is missing a component



                            Thanks SASA
                            • 11. Re: Drop database link & ORA-02082 Error
                              Barbara Boehmer
                              The current_user just means that it was created as connect to current_user instead of connecting to a specific user. Is qwer a username? If it is, can you try to drop it from that schema, even if you have to re-create that schema? It doesn't seem like it should matter that you renamed your database, but maybe it does. You certainly don't want to have to rename it again just to drop your database link and you definitely don't want to edit your data dictionary. The following is the closest that I can come to reproducing your situation, where qwer is the user, ora92 is the database, which exists, and I have specified connect to current_user:
                              scott@ORA92> create user qwer identified by qwer
                                2  /
                              
                              User created.
                              
                              scott@ORA92> grant connect, resource to qwer
                                2  /
                              
                              Grant succeeded.
                              
                              scott@ORA92> grant create database link to qwer
                                2  /
                              
                              Grant succeeded.
                              
                              scott@ORA92> connect qwer/qwer
                              Connected.
                              scott@ORA92> @ login
                              scott@ORA92> SET ECHO OFF
                              
                              GLOBAL_NAME
                              ----------------------------------------------------------------------------------------------------
                              qwer@ORA92
                              
                              qwer@ORA92> alter session set global_names=false
                                2  /
                              
                              Session altered.
                              
                              qwer@ORA92> create database link qwer.ora92@loopback
                                2  connect to current_user
                                3  using 'ora92'
                                4  /
                              
                              Database link created.
                              
                              qwer@ORA92> select db_link, username, host from user_db_links
                                2  /
                              
                              DB_LINK
                              ----------------------------------------------------------------------------------------------------
                              USERNAME
                              ------------------------------
                              HOST
                              ----------------------------------------------------------------------------------------------------
                              QWER.ORA92@LOOPBACK
                              CURRENT_USER
                              ora92
                              
                              
                              qwer@ORA92> drop database link qwer.ora92@loopback
                                2  /
                              
                              Database link dropped.
                              
                              qwer@ORA92> select db_link, username, host from user_db_links
                                2  /
                              
                              no rows selected
                              
                              qwer@ORA92>
                              • 12. Re: Drop database link & ORA-02082 Error
                                413257
                                Hello Barbara,

                                thanks for your help but definitely oracle won :-( and the db links exist for ever :-).

                                sasa
                                • 13. Re: Drop database link & ORA-02082 Error
                                  465139
                                  try this:

                                  select * from global_name;

                                  -- write down the current name;

                                  do:

                                  Alter database rename global_name to tttt.xxx;

                                  drop public database link xxxxxx;

                                  Rename global name back to original from the query above.
                                  • 14. Re: Drop database link & ORA-02082 Error
                                    133834
                                    I have the same problem and the last solution works for me.
                                    1 2 Previous Next