How to extract ddl for oracle db links?
Seems that even as SYS user you can't easity create dblink for another user (except of public dblink).
Even if you run create database link your_user.link3 using "db_alias"
it's owner will be SYS
.
Possible hacks are connect as another user
(you may add conn
into SQL*Plus script if you have credentials)
Or create procedure for user that need to have dblink that run create database link
command with parameters and call it from sys.
homer
Updated on November 15, 2020Comments
-
homer over 3 years
Is there anyway to extract ddl for all database links? I would like to get in sql and recreate them via sql.
I can use below and it works for PUBLIC user but for non public user it doesn't give me db link owner.
Set long 1000 SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db; Sample link owner and name Owner db_link public link1 public link2 user1 link3
If I ran above select it will give me below, #3 doesn't have username in it.
Output from above SELECT 1. create public database link "link1" using "db_alias" 2. create public database link "link2" using "db_alias" 3. create database link "link3" using "db_alias"
I recreate links using SYS and don't want to create #3 as SYS user.