?
Solved

db links

Posted on 2002-04-12
5
Medium Priority
?
955 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

752 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