DBlink s

select owner,db_link,username,host from dba_db_links where owner='DdBMON';

select owner,db_link, host from all_db_links;


select instance_name from v$instance@OMD_CACHE;

select max(lengthb(app_lic_nbr)) from v_omdirect_cutadr@omd_cache;

set heading off;

set echo off;

Set pages 999;

set long 90000;

SELECT DBMS_METADATA.GET_DDL('DB_LINK','OMD_CACHE','PUBLIC') from dba_db_links db;

select DB_LINK,owner from dba_db_links where db_link='OMD_CACHE';

$ sqlplus "/as sysdba"
DBLINKS DETAILS
---------------
set pages 50000 lines 32767
col owner for a15
col username for a10
col host for a20
col created for a20
COL DB_LINK FORMAT A30
select owner, db_link, username, host, to_char(created,'dd-mon-yyyy hh24:mi:ss') CREATED from DBA_DB_LINKS order by owner, db_link;

set head off
set pages 0
set long 9999999

select dbms_metadata.get_ddl('DB_LINK','PMF_LINK','STAT_PRODGOV') from dual;


select DB_LINK,owner from dba_db_links where db_link='PM_LINK';


select dbms_metadata.get_ddl ('DB_LINK', db_link, owner) from dba_db_links where owner like 'DBdMON';

or


SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) Text from dba_db_links db WHERE owner like 'DBMON';


CREATE DATABASE LINK POEMDB_BACKUP_REPORT connect to DBdMON identified by "Mokk" using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OMIP.omi.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = POEddMDB)))';

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select * from global_name;
SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# and U.NAME in ( 'PRINCEADMIN');



create database link PRINCEADMIN.OARTEST.OMI.COM
connect to APPSQ identified by "OMAPPSQTEST#13" using '(DESCRIPTION=(ADDRESS=(PR
OTOCOL=tcp)(HOST=omidrl031.omi.com)(PORT=1524)) (CONNECT_DATA= (SERVER = DEDICAT
ED) (SERVICE_NAME = omtest)))'
;

Drop database link
==============

select * from dba_db_links where OWNER='SCOTT';
OWNER DB_LINK USERNAME HOST CREATED
------------------------------ -------------------- ------------------------------ -------------------- ---------
SCOTT LINK1 SCOTT testdb 04-NOV-11




SQL>drop database link scott.LINK1;
drop database link scott.LINK1
*
ERROR at line 1:
ORA-02024: database link not found


ORA-02021: DDL operations are not allowed on a remote database





create a procedure

SQL> CREATE PROCEDURE scott.drop_db_link AS

BEGIN

EXECUTE IMMEDIATE 'drop database link LINK1';

END drop_db_link; 2 3 4

5 /

Procedure created

SQL> exec scott.drop_db_link

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='SCOTT';

no rows selected

Troubleshooting Dblinks issues.

1. What is the local and remote database version involved in database link? local database version 11.2.0.3.0 remote database version 18.0.0.0.0 2.For below query uploaded HTML format. set markup html on spool dist_queries.html show user select name from v$DATABASE; SELECT * FROM DBA_DB_LINKS; SELECT * FROM GLOBAL_NAME; select * from DBA_2PC_PENDING; select * from DBA_2PC_NEIGHBORS; select * from sys.pending_trans$; select * from SYS.PENDING_SESSIONS$; select * from SYS.PENDING_SUB_SESSIONS$; select * from V$GLOBAL_TRANSACTION; SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status, KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE'; select * from v$active_services; spool off set markup html off 3. current setting for SQLNET.EXPIRE_TIME on both local and remote database. local database sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ADR_BASE = /oraclebase/app/oracle Remote database # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10 NAMES.DEFAULT_DOMAIN = HQ.DAL.OMI.COM SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10 SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

Now check the DBA_2PC_PENDING view for the LOCAL_TRAN_ID.

SET LINESIZE 500;
COL LOCAL_TRAN_ID FORMAT A13
COL GLOBAL_TRAN_ID FORMAT A50
COL STATE FORMAT A8
COL MIXED FORMAT A3
COL HOST FORMAT A10
COL COMMIT# FORMAT A10

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
   FROM DBA_2PC_PENDING
/