Home » SQL & PL/SQL » SQL & PL/SQL » unable to recreate the dblink in another db/schema (oracle 10.2.0.1)
unable to recreate the dblink in another db/schema [message #448803] Thu, 25 March 2010 04:41 Go to next message
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 #448987 is a reply to message #448803] Fri, 26 March 2010 02:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't understand your problem.
What did you expect as output? In other words, what is missing or included unexpectedly in your current output?
Re: unable to recreate the dblink in another db/schema [message #448992 is a reply to message #448987] Fri, 26 March 2010 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe the owner of the database link...

Regards
Michel
Re: unable to recreate the dblink in another db/schema [message #449253 is a reply to message #448803] Sun, 28 March 2010 22:43 Go to previous messageGo to next message
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 #449256 is a reply to message #449253] Sun, 28 March 2010 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>One solution for above is connect with the user (in which we want to recreate it) and run the below function.
Only way to create "private" DBLINK is above.
Alternative is to CREATE PUBLIC DATABASE LINK
Re: unable to recreate the dblink in another db/schema [message #449259 is a reply to message #448803] Sun, 28 March 2010 23:22 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
In case of public dblink, the PUBLIC keyword came along with metadta. So there is no problem. In case of private, I didn't get the user name in case of private dblink. How to recreate it in another schema?
Regards,
Madhavi.
Re: unable to recreate the dblink in another db/schema [message #449260 is a reply to message #449259] Sun, 28 March 2010 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>How to recreate it in another schema?
CONNECT to another schema &
CREATE DATABASE LINK

Re: unable to recreate the dblink in another db/schema [message #449263 is a reply to message #448803] Mon, 29 March 2010 00:02 Go to previous messageGo to next message
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.
Re: unable to recreate the dblink in another db/schema [message #449266 is a reply to message #449263] Mon, 29 March 2010 00:24 Go to previous message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>my actual requirement is....
I choose to not respond since I refuse to be continue to be deceived what is actual problem.

Previous Topic: Ora-00907 - missing right paranthesis
Next Topic: PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here
Goto Forum:
  


Current Time: Sun Sep 25 02:38:11 CDT 2016

Total time taken to generate the page: 0.28602 seconds