Solved

db links

Posted on 2002-04-12
5
942 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
  • 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 25 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Syntax 8 56
Determine Who is Runnig my Bash Shell Script 4 65
Consolidating oracle query results to a single line 8 53
PL SQL Search Across Columns 4 22
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now