unable to recreate the dblink in another db/schema [message #448803] |
Thu, 25 March 2010 04:41 |
madhavi babburi
Messages: 117 Registered: May 2009 Location: Hyderabad
|
Senior Member |
|
|
Hi,
I'm unable to recreate the db link (private) in another schema/database. Even its a schema object, its come without schema name while extracting from database using metadata api.
code is below (just for extracting)
CREATE OR REPLACE FUNCTION DDI.DBLINK_DDL
RETURN CLOB
AUTHID CURRENT_USER
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;
V_Next_Doc CLOB;
V_LOB CLOB;
BEGIN
v_meta_handle := DBMS_METADATA.OPEN('DATABASE_EXPORT');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'INCLUDE_PATH_EXPR','IN''DB_LINK''');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'NAME_EXPR','IN''EMACH''','SCHEMA');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'NAME_EXPR','IN''TESTDB.REGRESS.RDBMS.DEV.US.ORACLE.COM''','DB_LINK');
v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
LOOP
v_doc := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
EXIT WHEN v_doc IS NULL;
IF V_Lob is null then
v_lob := v_doc;
ELSE
v_lob := V_LOB || v_doc;
END IF;
-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
END LOOP;
return v_lob;
end;
Output is...
CREATE DATABASE LINK "TESTDB.REGRESS.RDBMS.DEV.US.ORACLE.COM"
CONNECT TO "TEST" IDENTIFIED BY VALUES '05840660603E8D639998C728374A8BEA8F'
USING '(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 10.91.40.8)
(Port = 1521)
)
(CONNECT_DATA =
(SID = TESTDB)
)
)'
How to do that?
Regards,
Madhavi.
|
|
|
|
|
Re: unable to recreate the dblink in another db/schema [message #449253 is a reply to message #448803] |
Sun, 28 March 2010 22:43 |
madhavi babburi
Messages: 117 Registered: May 2009 Location: Hyderabad
|
Senior Member |
|
|
Hi,
yes...owner is missing while extracting the metadata of db link. I'm unable to recreate the object(dblink) in a particular schema of same database or diffrent schema.
Unlike other schema objects, why we get the dblink metadata without owner name?
Is there any solution for recreate the dblink in a particular schema?
One solution for above is connect with the user (in which we want to recreate it) and run the below function.
Apart from that i need a solution.
Regards,
Madhavi.
|
|
|
|
|
|
Re: unable to recreate the dblink in another db/schema [message #449263 is a reply to message #448803] |
Mon, 29 March 2010 00:02 |
madhavi babburi
Messages: 117 Registered: May 2009 Location: Hyderabad
|
Senior Member |
|
|
Hi Blackswan,
my actual requirement is....
In one schema (assume schema 1), i have a database table (table has metadata of all objects of another database) and a procedure (recreate the objects in any schema (remap) using metadata api). I need to execute the procedure in the same schema but object should get create with in another schema (SCHEMA 2).
For remaining schema objects like table, view, sequence.. i got metadata like owner.object name. So i remap the schema name with target schema (schema 2).
But in case of dblink (private), i didn't get the schema/owner before the object name. What i should do to recreate it in any other schema?
When i execute my procedure, the object is created in the schema (schema 1) where i execute it. But i want it in another schema (schema 2).
Regards,
madhavi.
|
|
|
|