Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 968
  • Last Modified:

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.

0
oraking
Asked:
oraking
  • 2
  • 2
1 Solution
 
asimkovskyCommented:
NEVER MODIFY THE DATA DICTIONARY!!!

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

DROP DATABASE LINK db_link_name;


Andrew
0
 
p_yaroslavCommented:
Hi!

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!!!!

Regards.
Yaroslav.
0
 
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
0
 
asimkovskyCommented:
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.

Andrew
0
 
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now