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.
