oraking
asked on
db links
delete from all_db_links where db_link='NOON.VSOFTI.STPH.
*
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.
*
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.
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.
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.
ASKER
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
--- oraking
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
--- oraking
If you want to get rid of a db_link, use:
DROP DATABASE LINK db_link_name;
Andrew