Okey, you must to know, but is always useful to remmember that… If you don’t want to configure your TNSNAMES, you can connect directly to description of your database. This way:
sqlplus> conn matheus_boesing@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.domain.net)(PORT=1531)))(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 matheus_boesing@&&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 matheus_boesing@&&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 1531 mydb (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.domain.net)(PORT=1531)))(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
These scripts use to help me a lot on daily basis, and it’s exclusive.
I couldn’t find anything like this so far. So, I made it. 🙂
Matheus.