Create db links without tnsnames

Oracle create dblink without tnsname.ora entry   ..

dblink with password value !!!!

SQL> def
DEFINE _DATE = "04-11-2017 15:40:33" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ora12" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1201000200" (CHAR)



SQL> CREATE PUBLIC DATABASE LINK test1 CONNECT TO c##anuj IDENTIFIED BY vihaan123 USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 9.18.114.253)(PORT = 1521))(CONNECT_DATA = (SID = vihcdbd)))';

Database link created.

with passwd value 
CREATE DATABASE LINK "TEST1" CONNECT TO "CXXX" IDENTIFIED BY VALUES '06811B86B62C6709EBEA6E9DFBE8C5E350B8A3A8FDBF83A1A286757AD7AEF7A1FF729527AA6AED45AB2197EE6BEA3C3F7CC3304A982D7198E6FD13504F46B79854276A7BE1B2BA5D9F0409247B4EB9892A05B01A66C76C7445C032B545BFA98A31D22DFDDDB70' USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dX01.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RXC)))'; 

to test .... 

set head off verify off echo off pages 1500 linesize 120 feedback off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select
 'INSTANCE_NUMBER.............................................: '|| INSTANCE_NUMBER ,
 'INSTANCE_NAME...............................................: '|| INSTANCE_NAME ,
 'HOST_NAME...................................................: '|| HOST_NAME ,
 'VERSION.....................................................: '|| VERSION ,
 'STARTUP_TIME................................................: '|| STARTUP_TIME ,
 'STATUS......................................................: '|| STATUS ,
 'PARALLEL....................................................: '|| PARALLEL ,
 'THREAD#.....................................................: '|| THREAD# ,
 'ARCHIVER....................................................: '|| ARCHIVER ,
 'LOG_SWITCH_WAIT.............................................: '|| LOG_SWITCH_WAIT ,
 'LOGINS......................................................: '|| LOGINS ,
 'SHUTDOWN_PENDING............................................: '|| SHUTDOWN_PENDING ,
 'DATABASE_STATUS.............................................: '|| DATABASE_STATUS ,
 'INSTANCE_ROLE...............................................: '|| INSTANCE_ROLE ,
 'ACTIVE_STATE................................................: '|| ACTIVE_STATE ,
 'BLOCKED.....................................................: '|| BLOCKED 
from Gv$instance@test1;-----<<<<< dblink !!!!!!!!!!!!!!!!!!
set head on verify on echo on feedback on 



INSTANCE_NUMBER.............................................: 1
INSTANCE_NAME...............................................: vihcdbd
HOST_NAME...................................................: cloud-ora
VERSION.....................................................: 12.1.0.2.0
STARTUP_TIME................................................: 04-11-2017 15:12:43
STATUS......................................................: OPEN
PARALLEL....................................................: NO
THREAD#.....................................................: 1
ARCHIVER....................................................: STARTED
LOG_SWITCH_WAIT.............................................:
LOGINS......................................................: ALLOWED
SHUTDOWN_PENDING............................................: NO
DATABASE_STATUS.............................................: ACTIVE
INSTANCE_ROLE...............................................: PRIMARY_INSTANCE
ACTIVE_STATE................................................: NORMAL
BLOCKED.....................................................: NO

column owner    format a15
column username format a15
column password format a15
column host     format a20
column db_link  format a30
select u.name owner, l.name db_link, l.userid username, l.password password, l.host host 
from sys.link$ l, sys.user$ u 
where l.owner# = u.user# 
order by l.name;

Database link created.

with passwd value 
CREATE DATABASE LINK "TEST1" CONNECT TO "CXXX" IDENTIFIED BY VALUES '06811B86B62C6709EBEA6E9DFBE8C5E350B8A3A8FDBF83A1A286757AD7AEF7A1FF729527AA6AED45AB2197EE6BEA3C3F7CC3304A982D7198E6FD13504F46B79854276A7BE1B2BA5D9F0409247B4EB9892A05B01A66C76C7445C032B545BFA98A31D22DFDDDB70' USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dX01.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RXC)))'; 

https://www.ludovicocaldara.net/dba/ora-02153-create-database-link/

metalink note

ORA-02153: Invalid VALUES Password String When Creating a Database Link Using BY VALUES With Obfuscated Password After Upgrade To 11.2.0.4 (Doc ID 1905221.1)

-- open session 

set linesize  300 pageize 300

col origin for a30

 col "GTXID" for a35

 col lsession for a10

 col username for a20

 col waiting for a50

 Select /*+ ORDERED */

 substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",

 substr(g.K2GTITID_ORA,1,35) "GTXID",

 substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,

 s2.username,

 s2.sql_id,

 decode(bitand(ksuseidl,11),

 1,'ACTIVE',

 0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),

 2,'SNIPED',

 3,'SNIPED',

 'KILLED'

 ) "State",

 substr(w.event,1,30) "WAITING"

 from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2

 where  g.K2GTDXCB =t.ktcxbxba

 and   g.K2GTDSES=t.ktcxbses

 and  s.addr=g.K2GTDSES

 and  w.sid=s.indx

 and s2.sid = w.sid;  

 

 

set linesie 300

COL OWNER FORMAT a20

COL DB_LINK FORMAT A30

COL HOST FORMAT A120 HEADING "SERVICE"

COL USERNAME FORMAT A8 HEADING "USER"

SELECT * FROM DBA_DB_LINKS ;

How To Audit Usage Of Database Links (Doc ID 2071002.1)

 set lines 1000

 column userid format a15

 column ntimestamp# format a30

 column sqltext format a40

 column comment$text format a200

 select userid, ntimestamp#, sqltext, comment$text from aud$ where comment$text like '%DBLINK%';