“Holy easy stuff, DBAtman!”
Okey, you must to know, but is always useful to remember that… If you don’t want to configure your TNSNAMES, you can connect directly to description of your database.
You can do it in two ways: First one is using Easy Connect. But some tools simply don’t support it. Here is the second way, just like using TNSNAMES but without TNSNAMES:
sqlplus> conn matheus_boesing@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.domain.net)(PORT=1521)))(CONNECT_DATA=(service_name=mydb))) Enter password: ******** Connected. sqlplus>
Based on this, I made two scripts, to connect with the sid (c.sql) or with the service_name (s.sql) and make my life easier. Here the scripts:
sqlplus>get c
1 DEFINE VHOST = &1.
2 DEFINE VPORT = &2.
3 DEFINE VSID = &3.
4 DEFINE VDESC='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&VHOST)(PORT=&VPORT)))(CONNECT_DATA=(SID=&VSID)(server=dedicated)))'
5 disconnect
6 connect dbatman@&&VDESC
7 set linesize 1000
8 set sqlprom '&&VSID> '
9 select instance_name, host_name
10 from v$instance;
11 exec dbms_application_info.SET_MODULE('MATHEUS_BOESING','DBA');
12 alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
13 UNDEFINE VDESC
14 UNDEFINE 1
15 UNDEFINE 2
16* UNDEFINE 3
sqlplus>get s
1 DEFINE VHOST = &1.
2 DEFINE VPORT = &2.
3 DEFINE VSID = &3.
4 DEFINE VDESC='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&VHOST)(PORT=&VPORT)))(CONNECT_DATA=(SERVICE_NAME=&VSID)(server=dedicated)))'
5 prompt &VDESC
6 disconnect
7 connect dbatman@&&VDESC
8 set linesize 1000
9 set sqlprom '&&VSID> '
10 select instance_name, host_name
11 from v$instance;
12 exec dbms_application_info.SET_MODULE('MATHEUS_BOESING','DBA');
13 alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
14 UNDEFINE VDESC
15 UNDEFINE 1
16 UNDEFINE 2
17* UNDEFINE 3
sqlplus>
It can be used like this:
sqlplus>@s mydb.domain.net 1521 mydb (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.domain.net)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=mydb)(server=dedicated))) Enter password: ******** Connected.
Ok, but, let’s suppose you are working in a cluster and wants to connect directly to the another instance. I made the script below (ci.sql). It’s not beautiful, but is a lot hopeful:
sqlplus> get ci 1 DEFINE VINT = &1. 2 undefine VHOST 3 undefine VSID 4 VARIABLE VCONN varchar2(100) 5 PRINT ret_val 6 BEGIN 7 SELECT '@c '||host_name||' 1521 '||INSTANCE_NAME 8 INTO :VCONN 9 FROM gv$instance where INSTANCE_NUMBER=&VINT; 10 END; 11 / 12 set head off; 13 spool auxcon.sql 14 prompt set head on; 15 print :VCONN 16 prompt set head on; 17 spool off; 18* @auxcon sqlplus>
As you see, you inform the inst_id you want to connect. It can be used like:
mydb> @instance INSTANCE_NAME ------------------------------ mydb_2 mydb> @instances INST_NUMBER INST_NAME ----------- --------------------------------------- 1 db2srvr2p.grepora.net:mydb_1 2 db1srvr1p.grepora.net:mydb_2 mydb> @ci 1 @c db2srvr2p.grepora.net 1521 mydb_1 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Enter password: ******** Connected. mydb_1> @instance INSTANCE_NAME ------------------------------ mydb_1
Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!
dbatman.blog 