| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL-Plus
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFD0CA.5D9F1F46
Content-Type: text/plain;
charset="windows-1252"
Put the following two scripts in your SQLPATH, and also add all the code from the dba_login.sql script to your glogin.sql or link the glogin.sql to it. When you start SQL*Plus the glogin.sql will run, in SQL*Plus when you reconnect to a different user instead of doing "connect u/p" do it via the script as in "@connect u/p". The resulting SQL*Plus prompt is outlined at the top of the dba_login.sql script. The scripts are as follows:
REM
REM connect.sql
REM
connect &1
@@dba_login.sql
REM END-OF-SCRIPT
REM
REM dba_login.sql
REM
REM Required grants be made public or to the user:
REM
REM grant select on v_$instance to public; REM grant select on v_$session to public;REM
REM | Machine REM | | Instance REM | | | Oracle Version# REM | | | | UserName REM | | | | | Sid REM | | | | | | Serial# REM | | | | | | |REM SQL_at_dev:DBDEV:8.1.5:OPS$ORACLE:34,6424> REM set termout off
REM AUTOTRACE EXPLAIN COLUMNS
REM plan_plus_exp change f/60 t/100
column plan_plus_exp format a100 column other_plus_exp format a44column other_tag_plus_exp format a29 column object_node_plus_exp format a8 column parent_id_plus_exp format 990 heading 'p' column id_plus_exp format 990 heading 'i'
alter session set nls_date_format = 'YYYYMMDD HH24:MI:SS';
column machine noprint new_value nv_machine
column instance noprint new_value nv_instance
column version noprint new_value nv_version
column sid noprint new_value nv_sid
column serial noprint new_value nv_serial
column usr noprint new_value nv_usr
select substr(rtrim(A.host_name),1,10) machine,
rtrim(A.instance_name) instance,
substr(A.version,1,(instr(A.version,'.',1,3) - 1)) version,
ltrim(B.sid) sid,
ltrim(B.serial#) serial,
rtrim(user) usr
from sys.v_$instance A,
sys.v_$session B
column gl_prompt noprint new_value nv_gl_prompt
select '&nv_machine' || ':' ||
'&nv_instance' || ':' ||
'&nv_version' || ':' ||
'&nv_usr' || ':' ||
'&nv_sid' || ',' ||
'&nv_serial' gl_prompt
from dual; set sqlprompt 'SQL@&nv_gl_prompt> ' col machine print
define _EDITOR = vi
define _editor = vi
set pagesize 36
set linesize 130
set pause off
set pause 'Hit Enter To Continue...'
set termout on
REM END-OF-SCRIPT
Brian P. Mac Lean
Senior Oracle Database Administrator
OCPv8/Oracle Master
HomeBid.Com
8700 N. Gainey Center Drive
Scottsdale, AZ 85258
Tel:480.609.4624 Cel:602.617.6075 Fax:480.609.4646 Net:brian.maclean_at_homebid.com Who needs friends when you can sit alone in your room and drink?
-----Original Message-----
From: salu Ullah [mailto:salu_ullah_at_hotmail.com]
Sent: Tuesday, June 06, 2000 2:50 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL-Plus
Hello,
Is there a script or code that i can add in my glogin file so that when ever
i connect to sql plus shows both the user name & database name. Thanks in advance.
Salu
--
Author: salu Ullah
INET: salu_ullah_at_hotmail.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
------_=_NextPart_001_01BFD0CA.5D9F1F46
Content-Type: text/html;
charset="windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dwindows-1252">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: SQL-Plus</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2 FACE=3D"Arial">Put the following two scripts in your =
SQLPATH, and also add all the code from the dba_login.sql script to =
your glogin.sql or link the glogin.sql to it. When you start =
SQL*Plus the glogin.sql will run, in SQL*Plus when you reconnect to a =
different user instead of doing "connect u/p" do it via the =
script as in "@connect u/p". The resulting SQL*Plus =
prompt is outlined at the top of the dba_login.sql script. The =
scripts are as follows:</FONT></P>
<P><FONT SIZE=3D1 FACE=3D"Courier New">REM</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM connect.sql</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">connect &1</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">@@dba_login.sql</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM END-OF-SCRIPT</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=3D1 FACE=3D"Courier New">REM</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM dba_login.sql</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM Required grants be made =
public or to the user:</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New">REM grant =
select on v_$instance to public;</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New">REM grant =
select on v_$session to public;</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM Changes prompt to the =
following:</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM SQL </FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM | Machine =
</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM | =
| Instance </FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM | =
| | Oracle Version#</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM | =
| | | =
UserName</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM | =
| | | =
| Sid</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM | =
| | | =
| | =
Serial#</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM | =
| | | =
| | =
|</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM =
SQL_at_dev:DBDEV:8.1.5:OPS$ORACLE:34,6424></FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM</FONT>
</P>
<P><FONT SIZE=3D1 FACE=3D"Courier New">set termout off</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">set pause off</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">set numwidth 9</FONT>
</P>
<P><FONT SIZE=3D1 FACE=3D"Courier New">REM AUTOTRACE EXPLAIN =
COLUMNS</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM plan_plus_exp change f/60 =
t/100</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column =
plan_plus_exp =
format a100 </FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column =
other_plus_exp format =
a44</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column =
other_tag_plus_exp format a29</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column =
object_node_plus_exp format a8</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column =
parent_id_plus_exp format 990 heading =
'p'</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column =
id_plus_exp =
format 990 heading 'i'</FONT>
</P>
<P><FONT SIZE=3D1 FACE=3D"Courier New">alter session set =
nls_date_format =3D 'YYYYMMDD HH24:MI:SS';</FONT>
</P>
<P><FONT SIZE=3D1 FACE=3D"Courier New">column machine noprint =
new_value nv_machine</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column instance noprint =
new_value nv_instance</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column version noprint =
new_value nv_version</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column =
sid noprint new_value nv_sid</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column serial =
noprint new_value nv_serial</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">column =
usr noprint new_value nv_usr </FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">select =
substr(rtrim(A.host_name),1,10)  =
;  =
; machine,</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> =
rtrim(A.instance_name) &n=
bsp; &n=
bsp; instance,</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> =
substr(A.version,1,(instr(A.version,'.',1,3) - 1)) version,</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> =
ltrim(B.sid) =
=
=
sid,</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> =
ltrim(B.serial#) &n=
bsp; &n=
bsp; &n=
bsp; serial,</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> =
rtrim(user) &=
nbsp; &=
nbsp; &=
nbsp; usr </FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New"> from sys.v_$instance =
A,</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> sys.v_$session B</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New"> where B.audsid =3D =
userenv('sessionid');</FONT>
</P>
<P><FONT SIZE=3D1 FACE=3D"Courier New">column gl_prompt noprint =
new_value nv_gl_prompt</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">select '&nv_machine' =
|| ':' ||</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> '&nv_instance' || ':' =
||</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> '&nv_version' || =
':' ||</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> =
'&nv_usr' || ':' ||</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> =
'&nv_sid' || ',' ||</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier =
New"> '&nv_serial' =
gl_prompt</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New"> from dual;</FONT>
</P>
<P><FONT SIZE=3D1 FACE=3D"Courier New">set sqlprompt =
'SQL@&nv_gl_prompt> '</FONT>
</P>
<P><FONT SIZE=3D1 FACE=3D"Courier New">col machine =
print</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">col instance print</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">col version =
print</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">col =
sid print</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">col serial =
print</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">col =
usr print</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">col gl_prompt print</FONT>
</P>
<P><FONT SIZE=3D1 FACE=3D"Courier New">define _EDITOR =3D vi</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">define _editor =3D vi</FONT>
</P>
<P><FONT SIZE=3D1 FACE=3D"Courier New">set pagesize 36 </FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">set linesize 130 </FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">set pause off </FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">set pause 'Hit Enter To =
Continue...'</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">set termout on</FONT>
<BR><FONT SIZE=3D1 FACE=3D"Courier New">REM END-OF-SCRIPT</FONT>
</P>
<BR>
<P><B><FONT COLOR=3D"#0000FF" FACE=3D"Comic Sans MS">Brian P. Mac =
Lean</FONT></B>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">Senior =
Oracle Database Administrator</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">OCPv8/Oracle Master</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">HomeBid.Com</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">8700 N. =
Gainey Center Drive</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">Scottsdale, =
AZ 85258</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Tel:480.609.4624</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Cel:602.617.6075</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Fax:480.609.4646</FONT>
<BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans =
MS">Net:brian.maclean_at_homebid.com</FONT>
</P>
<P> <FONT COLOR=3D"#008080" =
SIZE=3D1 FACE=3D"Comic Sans MS">Who needs friends when you can sit =
alone in your room and drink?</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">-----Original Message-----</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">From: salu Ullah [<A =
HREF=3D"mailto:salu_ullah_at_hotmail.com">mailto:salu_ullah_at_hotmail.com</A>=
]</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Sent: Tuesday, June 06, 2000 2:50 =
PM</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">To: Multiple recipients of list =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Subject: SQL-Plus</FONT>
</P>
<BR>
<P><FONT SIZE=3D2 FACE=3D"Arial">Hello,</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Is there a script or code that i can =
add in my glogin file so that when ever </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">i connect to sql plus shows both the =
user name & database name.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Thanks in advance.</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Salu</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial">_________________________________________________________=
_______________</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Get Your Private, Free E-mail from =
MSN Hotmail at <A HREF=3D"http://www.hotmail.com" =
TARGET=3D"_blank">http://www.hotmail.com</A></FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">-- </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Author: salu Ullah</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> INET: =
salu_ullah_at_hotmail.com</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network =
Services -- (858) 538-5051 FAX: (858) =
538-5051</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">San Diego, =
California -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial">---------------------------------------------------------=
-----------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">To REMOVE yourself from this mailing =
list, send an E-Mail message</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">to: ListGuru_at_fatcity.com (note EXACT =
spelling of 'ListGuru') and in</FONT>
Received on Wed Jun 07 2000 - 16:50:16 CDT
 |
 |