Tags
DBMS_METADATA.GET_DDL database link password, Generate DDL for all database links with password, Get create script for Private Database Links, How to extract the DB LINKS DDL with the password, Oracle Public and Private Links Script, recreate database links using DBMS_METADATA, Script to Generate DDL for Database Links
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;
/