db links

delete from all_db_links where db_link='NOON.VSOFTI.STPH.NET'
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table. The action for error oracle says is Redefine the view or delete it from the underlying base tables. How do we carry this out since all_db_links is a data dictionary view.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

asimkovskyConnect With a Mentor Commented:
The ALL_DB_LINKS view shows all database links in the database that the current user is allowed to access.  The view is joined on LINK$ and USER$ so you can see who owns which link.  

When you delete from SYS.LINK$, you are still getting that "already exists" error, because like I said, you should never mess with the data dictionary.  There are many more dependencies there than you can immediately see, and you could very easily screw up your database.  Oracle provides SQL statements for you to do everything you need with the database so you don't have to mess with the data dictionary.


If you want to get rid of a db_link, use:

DROP DATABASE LINK db_link_name;


The ALL_DB_LINKS is VIEW. You can't delete from this view because It based on two tables:

The view definition is:
select u.name, l.name, l.userid, l.host, l.ctime
from sys.link$ l, sys.user$ u
where l.owner# in ( select kzsrorol from x$kzsro )
  and l.owner# = u.user#;

Bout you need not delete from view as right mentioned asimkovsky!!!!

orakingAuthor Commented:
Andrew & Yaroslav,  When I created a db link this time, I saw an entry in all_db_links and sys.link$ but no extra entry in sys.user$. I drop link is okay but if I delete from sys.link$ and try to create the same link again, oracle says link already exists where as all_db_links no longer has this database link. sys.user$ contains all roles and users etc. What is significance in having all_db_links view based on tables sys.link$ and sys.user$.

--- oraking
orakingAuthor Commented:
andrew asimovsky, yaroslav equally deserved it, but I can acccept only from one expert. I am posting a suggestion to sort out this issue among others.
--- oraking
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.