Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

db links

Posted on 2002-04-12
5
Medium Priority
?
957 Views
Last Modified: 2012-06-21
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
Comment
Question by:oraking
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6936856
NEVER MODIFY THE DATA DICTIONARY!!!

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

DROP DATABASE LINK db_link_name;


Andrew
0
 
LVL 3

Expert Comment

by:p_yaroslav
ID: 6937569
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
 

Author Comment

by:oraking
ID: 6941209
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
 
LVL 4

Accepted Solution

by:
asimkovsky earned 100 total points
ID: 6941928
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
 

Author Comment

by:oraking
ID: 6943874
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question