How to extract the DB LINKS DDL

Script to extract the DB Links DDL with the encrypted password.


SET LONG 20000

SELECT DBMS_METADATA.GET_DDL('DB_LINK',link.db_link,link.owner)

FROM dba_db_links link;

Another way to get the DDL is as below:

SQL> set pages 500
SQL> set long 20000
SQL> set lin 1000

SQL>SELECT 'SELECT dbms_metadata.get_ddl(''DB_LINK'',''' || db_link || ''',''' || owner || ''') as "DB Link DDL" FROM dual;' AS "Query for DDL"
FROM dba_db_links WHERE db_link IN ('&DB1LINK');
SQL> set long 1000000 linesize 1000 pagesize 0 feedback off trimspool on verify off serveroutput on
SQL> select dbms_metadata.get_ddl('DB_LINK','&link_name') from dual;
SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || A.USERID || ' identified by '
||L.PASSWORD||' using ''' || A.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ A, sys.user$ U
WHERE A.OWNER# = U.USER#;

A script to generate the DDL for all indexes

set heading off;
set echo off;
Set pages 500;
set long 200000;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',UPPER('&table_name'),UPPER('&schema_name'))
from dual
/
set heading on;
set echo on;

Comments

Popular posts from this blog

How to drop index and before dropping it how to get the DDL.

How to set up the Oracle Wallets in Oracle Database 19C

PRVG-11250 : The check "RPM Package Manager database" was not performed because

ORA-00257:archiver error, connect internal only until freed

Linux OL7/RHEL7: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

Verifying Daemon “Avahi-Daemon” Not Configured And Running …FAILED (PRVG-1360)

SKIP DNS RESLOV.CONF CHECK DURING RAC CONFIGURATION

How to write to a CSV file using Oracle SQL*Plus

Change Password in an Oracle Database