RE: SQL*Plus Client on Windows

From: Ted Coyle <oracle-l_at_webthere.com>
Date: Mon, 10 Sep 2007 09:34:42 -0400
Message-ID: <000901c7f3af$5a88a740$3921a8c0@medecision.com>

Here's a script that I've used for 10.2 databases. I call it manually using @l. It's for fast logon into development environments; otherwise I'd not have the logon embedded in the script. The exception handler is also not robust.

Read this for better exception handling... http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html

The format allows for nicely formatted db info cut-n-paste into e-mail.

----------------Begin l.sql ------------------------------------------------

clear buffer
accept 1 prompt ' What db? > '
connect system/hardtoguesspassword42@&1

set sqlprompt "_user'@'_connect_identifier> "

host title &_user@&_connect_identifier

set feedback off

begin
for x in

(SELECT

      INSTANCE_NUMBER                                 INSTANCE_NUMBER


     ,UPPER(INSTANCE_NAME)                            INSTANCE_NAME

     ,HOST_NAME                                       HOST_NAME

     ,VERSION                                         VERSION

     ,TO_CHAR(STARTUP_TIME,'MM/DD/YYYY HH24:MI:SS')   STARTUP_TIME

     ,STATUS                                          STATUS

     ,SUBSTR(PLATFORM_NAME,1,30)                      PLATFORM_NAME

     ,TO_CHAR(RESETLOGS_TIME,'MM/DD/YYYY HH24:MI:SS') RESETLOGS_TIME

     ,CURRENT_SCN                                     CURRENT_SCN

     ,OPEN_MODE                                       OPEN_MODE

     ,LOG_MODE                                        LOG_MODE

     ,(select substr(value,1,30) from v$parameter where name =

'service_names') SERVICE_NAME


     ,sys_context('USERENV','SID')                    SID

FROM v$instance
NATURAL JOIN v$database)
loop

dbms_output.put_line('---------------------------------------------------');

dbms_output.put_line('| INST_NUMBER: ' || x.instance_number); dbms_output.put_line('| INST_NAME: ' || x.instance_name); dbms_output.put_line('| SERVICE_NAME ' || x.service_name); dbms_output.put_line('| DB_VERSION: ' || x.version); dbms_output.put_line('| HOST: ' || x.host_name); dbms_output.put_line('| PLATFORM: ' || x.platform_name); dbms_output.put_line('| STARTUP: ' || x.startup_time); dbms_output.put_line('| STATUS: ' || x.status); --dbms_output.put_line('| RESETLOG: ' || x.resetlogs_time); --dbms_output.put_line('| CURRENT_SCN: ' || x.current_scn); dbms_output.put_line('| OPEN_MODE: ' || x.open_mode); dbms_output.put_line('| LOG_MODE: ' || x.log_mode); dbms_output.put_line('| CURRENT_SID: ' || x.sid);

dbms_output.put_line('---------------------------------------------------');

end loop;

exception

when others then

dbms_output.put_line(dbms_utility.format_error_backtrace); end;
/

set feedback on

----------------End l.sql --------------------------------------------------

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder
Sent: Sunday, September 09, 2007 12:20 AM To: Michael.Coll-Barth_at_VerizonWireless.com; 'oracle-l' Subject: RE: SQL*Plus Client on Windows

If you are using the command prompt version of Sqlplus, then you can use the cmd.exe's TITLE command for that.

Here's my i.sql which I use both for identifying where I'm logged on before doing any changes to database, plus it is called through login.sql. The window title change happens on the last line:

cut here ----------------------

define mysid=unknown

col username for a12
col i_sid head SID for a6 new_value mysid col serial# for 999999
col opid for 999999
col spid for 999999
col host_name for a25
col i_ver head VER for a10
col i_startup_day head STARTED for a8

select

	s.username, 
	i.instance_name, 
	i.host_name, 
	(select substr(banner, instr(banner, 'ease ')+5,10) from v$version
where rownum =1) i_ver,
	to_char(startup_time, 'YYYYMMDD') i_startup_day, 
	to_char(s.sid)		i_sid, 
	s.serial#, 
	p.spid, 
	p.pid			opid, 
	s.saddr, 
	p.addr			PADDR
from 
	v$session s, 
	v$instance i, 
	v$process p
where 
	s.paddr = p.addr
and 
	sid = (select sid from v$mystat where rownum = 1);

host title &_user@&_connect_identifier [&mysid]

cut here ----------------------

And the output is:

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Sep 9 12:16:15 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

USERNAME     INSTANCE_NAME    HOST_NAME                 VER        STARTED
SID    SERIAL# SPID            OPID SADDR    PADDR
------------ ---------------- ------------------------- ---------- --------
------ ------- ------------ ------- -------- --------
SYS          prod01           WINDOWS01                 10.2.0.3.0 20070907
148       2003 3480              19 343371B4 3425173C

SQL>

--
Regards,
Tanel Poder
http://blog.tanelpoder.com
 

> -----Original Message-----

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 10 2007 - 08:34:42 CDT