set echo off feed off head off
set linesize 999
set serveroutput on size 999999
set trimspool on
spool create_dblinks.sql
declare
  v_sql varchar2(999);
  cursor c_dblink is select owner, db_link, dbms_metadata.get_ddl('DB_LINK',db_link,owner) ddl from dba_db_links
                      where db_link like 'FILFIS%';
begin
  for c1 in c_dblink loop
    v_sql := replace(replace(c1.ddl,'''',''''''),'"');
    dbms_output.put_line('grant create database link to '||c1.owner||';');
    dbms_output.put_line('create or replace procedure '||c1.owner||'.create_link as');
        dbms_output.put_line('begin');
        dbms_output.put_line('execute immediate '''||v_sql||''';');
        dbms_output.put_line('end;');
        dbms_output.put_line('/');
    dbms_output.put_line('exec '||c1.owner||'.create_link;');
    dbms_output.put_line('drop procedure '||c1.owner||'.create_link;');
    dbms_output.put_line('revoke create database link from '||c1.owner||';');
  end loop;
end;
/
spool off
