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
Komentarze
Prześlij komentarz