Copy Database Links Oracle | Database Link Replace Oracle | Link Modify Oracle | db_link clone migrate move edit

COPY DATABASE LINKS DB_LINKS DB_LINK / MIGRATE / MOVE / CLONE


 DECLARE
--kamil.piorekk@gmail.com
--tool for migrate/copy/modify/colone database links
--this procedure runs in four steps
--1.get ddl of db link in loop select
--2.create porcedure in dblink owner schema to create db link
--3.exec create db link script in owner schema
--4.drop procedure
v1   VARCHAR(1000);
v2   VARCHAR (100);
ba   VARCHAR(8) DEFAULT q'['[]';        --'
ea   varchar(8) DEFAULT q'[']';          --'
ea2  varchar(8)   ;
BEGIN
ea2:=']' ;
ea:=ea2||q'[']';
--------------------------------------------------------------------
-- IF YOU WANT TO MODIFY THE DB LINK
-- / password / user / ip
-- JUST USE REPLACE IN LOOP SELECT ( put some code there )
--------------------------------------------------------------------
FOR i IN ( SELECT  dbms_metadata.get_ddl('DB_LINK',db_link,owner)  AS col1,owner  FROM dba_db_links )
  LOOP
  -- IF YOU WANT TO copy/links JUST put WHERE owner in LOOP and CHANGE i.owner TO YOUR USER
--------------------------------------------------------------------
    v1:='CREATE OR REPLACE PROCEDURE '||i.owner||'.cre_db_lnk AS v4 VARCHAR(500); BEGIN v4:=q'||ba||' '||i.col1||' '||ea||' ; EXECUTE IMMEDIATE v4;  END cre_db_lnk'  ;
    --EXECUTE IMMEDIATE v1;
    v2:=' DROP PROCEDURE '||i.owner||'.cre_db_lnk';
    --EXECUTE IMMEDIATE v2;
    --P4ADM.cre_db_lnk ;
    END LOOP;
END;



Kamil Piórek
-- kamil.piorekk@gmail.com