Tags

, , , , , ,

This is How to extract the Private and Public DB LINKS DDLs’ with the passwords:

–You should create the package below to create both public and private links without connecting with private db link owners accounts.

CREATE OR REPLACE PACKAGE OWNER.PROCFOREXEC IS
procedure PROCFOREXEC(schema_owner varchar2,SqlText varchar2);
END;
/
CREATE OR REPLACE PACKAGE BODY OWNER.PROCFOREXEC IS

procedure PROCFOREXEC(schema_owner varchar2,SqlText varchar2) is
c integer;
uid number;
begin
c:=sys.dbms_sys_sql.open_cursor();
select user_id into uid from dba_users where username=upper(schema_owner);
sys.dbms_sys_sql.parse_as_user(c,sqltext ,dbms_sql.native,UID );
sys.dbms_sys_sql.close_cursor(c);
exception
when others then
sys.dbms_sys_sql.close_cursor(c);
dbms_output.put_line(substr(sqlerrm,1,100));
END;

SET long 10000
SET linesize 10000
SET wrap off
SET serveroutput on
SET verify off lines 132
SET pagesize 0
SPOOL link_olusturma.sql

DECLARE
v_output             CLOB   := NULL;
hashed_pass_length   NUMBER := 1;
BEGIN
FOR tt IN (SELECT owner, db_link, username, HOST
FROM dba_db_links)
LOOP
SELECT DBMS_METADATA.get_ddl (‘DB_LINK’, tt.db_link, tt.owner)
INTO v_output
FROM DUAL;

hashed_pass_length :=
INSTR (v_output, ””, 1, 2)
– INSTR (v_output, ””, 1, 1);

IF (tt.owner = ‘PUBLIC’)
THEN
DBMS_OUTPUT.put_line (v_output || ‘;’);
ELSE
DBMS_OUTPUT.put_line (   ‘exec spk_dba.sp_runsql(”’
|| tt.owner
|| ””
|| ‘,’
|| ””
|| LTRIM (SUBSTR (v_output,
INSTR (v_output, ‘ ‘, 1, 1),
23
),
‘ ‘
)
|| ‘”‘
|| tt.db_link
|| ‘”‘
|| ‘ ‘
|| ‘CONNECT TO ‘
|| ‘”‘
|| tt.username
|| ‘”‘
|| ‘ IDENTIF
IED BY VALUES ‘
|| ””
|| LTRIM (SUBSTR (v_output,
INSTR (v_output, ‘ ‘, 1, 14),
hashed_pass_length + 1
),
‘ ‘
)
|| ”””
|| ‘ USING ‘
|| ”””
|| tt.HOST
|| ””””
|| ‘);’
);
END IF;
END LOOP;
END;
/

 

 

Create DB Links

Create DB Links

Advertisements