Re: SID on sqlplus prompt?
Hi You have already seen many post with the answer to this, but if you use connect glogin.sql will not be run again. I created a very basic script with a very short name and put it in the directory where my SQL_PATH registry key points to. This script does the connect (after prompting for the username/password SID) and than runs glogin.sql for me. accept username prompt Type username :; accept password prompt Type Password (wordt niet getoond) : hide; accept sid prompt Type instance naam (SID) :; connect username/password@sid; @d:\oracle\ora80\Plus80\glogin.sql; undefine username; undefine password; undefine sid; CLEAR SCREEN; * So at the prompt I type @c and answer the questions. Jack Walter K alden14004@ya To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hoo.com cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: SID on sqlplus prompt? [EMAIL PROTECTED] om 08-06-2001 22:35 Please respond to ORACLE-L Hi, Is there a way to get the SID or database name displayed in the command prompt of SQL*Plus? Can this be generated dynamically if I perform a CONNECT user/pw ? Thanks! -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information
RE: SID on sqlplus prompt?
Title: RE: SID on sqlplus prompt? I use this as well but it only works going through Sqlplus. If you want to change connections while you are in sqlplus by entering sql Connect username@dbname, then the sid/username will not change on the sql prompt. To make the connection change show, add a member in the Bin directory called Connect.sql and add the following code: connect 1 set termout off pause off arraysize 1 rem rem Store username rem col user_id new_value user_id col user_name new_value user_name select user_id, lower(username) user_name from user_users where username = user; rem rem Store database name rem col db_name new_value db_name select lower(substr(global_name,1,(instr(global_name,'.',1,1)-1))) db_name from global_name; set sqlprompt db_name:user_name Then in sqlplus enter sql @Connect username@dbname and the sid/username should change to the new connection. The glogin and connect members are very helpful when wanting to see what database you are connected too. They originated from someone on this list, but I don't remember who. Laura -Original Message- From: Gene Sais [mailto:[EMAIL PROTECTED]] Sent: Friday, June 08, 2001 5:36 PM To: Multiple recipients of list ORACLE-L Subject: Re: SID on sqlplus prompt? I use this: glogin.sql file found in $OH/sqlplus/admin on the db server. hth. gene snip
RE: SID on sqlplus prompt?
If the db_name will do just as well you can use sys_context('USERENV', 'DB_NAME') to obtain the database name and not have to grant access to any of the v$ tables. There's also ora_database_name which is a function that takes no arguments and returns db_name.domain_name Both of these can selected from dual Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, June 08, 2001 2:30 PM To: Multiple recipients of list ORACLE-L How about something like this? Of course, it requires that the user have select access to v$instance -- maybe there's another place to find this. You could do some sort of batch to reference the $ORACLE_SID instead, but the advantage of this is you can put it into glogin.sql or login.sql -- or maybe not. I just tried doing that and it didn't affect anything. Hmm. Oh, well, this may help you... set heading off set term off set feedback off spool set_sqlprompt.lst select 'set sqlprompt ' || instance_name || ' ' from v$instance / spool off @set_sqlprompt.lst set heading on set term on set feedback on Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Walter K alden14004@yTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ahoo.comcc: Sent by: Fax to: root@fatcity.Subject: SID on sqlplus prompt? com 06/08/2001 04:35 PM Please respond to ORACLE-L Hi, Is there a way to get the SID or database name displayed in the command prompt of SQL*Plus? Can this be generated dynamically if I perform a CONNECT user/pw ? Thanks! -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public
RE: SID on sqlplus prompt?
Neat! I knew there was some way of selecting that, but couldn't remember it and also couldn't find them in the FM. Didn't someone complain about the terrible indexes on Oracle manuals? Let me add my voice to that particular clamor. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] MacGregor, Ian A. To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: nford.EDU Fax to: Sent by: Subject: RE: SID on sqlplus prompt? root@fatcity. com 06/09/2001 12:40 PM Please respond to ORACLE-L If the db_name will do just as well you can use sys_context('USERENV', 'DB_NAME') to obtain the database name and not have to grant access to any of the v$ tables. There's also ora_database_name which is a function that takes no arguments and returns db_name.domain_name Both of these can selected from dual Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, June 08, 2001 2:30 PM To: Multiple recipients of list ORACLE-L How about something like this? Of course, it requires that the user have select access to v$instance -- maybe there's another place to find this. You could do some sort of batch to reference the $ORACLE_SID instead, but the advantage of this is you can put it into glogin.sql or login.sql -- or maybe not. I just tried doing that and it didn't affect anything. Hmm. Oh, well, this may help you... set heading off set term off set feedback off spool set_sqlprompt.lst select 'set sqlprompt ' || instance_name || ' ' from v$instance / spool off @set_sqlprompt.lst set heading on set term on set feedback on Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Walter K alden14004@yTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ahoo.comcc: Sent by: Fax to: root@fatcity.Subject: SID on sqlplus prompt? com 06/08/2001 04:35 PM Please respond to ORACLE-L Hi, Is there a way to get the SID or database name displayed in the command prompt of SQL*Plus? Can this be generated dynamically if I perform a CONNECT user/pw ? Thanks! -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name
SID on sqlplus prompt?
Hi, Is there a way to get the SID or database name displayed in the command prompt of SQL*Plus? Can this be generated dynamically if I perform a CONNECT user/pw ? Thanks! -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: SID on sqlplus prompt?
I use this: glogin.sql file found in $OH/sqlplus/admin on the db server. hth. gene -- -- $Header: /plus/v3/spam/precious/files/ACTIVE/glogin.sql,v 1.6 1995/07/25 02:33:26 cjones Exp $ -- Copyright (c) Oracle Corporation 1988, 1994, 1995. All Rights Reserved. -- -- SQL*Plus Global Login startup file. -- -- This is the global login file for SQL*Plus. -- Add any sqlplus commands here that are to be -- executed when a user invokes sqlplus -- Used by Trusted Oracle column ROWLABEL format A15 -- Used for the SHOW ERRORS command column LINE/COL format A8 column ERRORformat A65 WORD_WRAPPED -- For backward compatibility set pagesize 14 -- Defaults for SET AUTOTRACE EXPLAIN report column id_plus_exp format 990 heading i column parent_id_plus_exp format 990 heading p column plan_plus_exp format a60 column object_node_plus_exp format a8 column other_tag_plus_exp format a29 column other_plus_exp format a44 -- Set SQLPLUS Prompt to Database Name column instance_name noprint new_value dbname column passout new_value dbname set term off -- Set default date format Y2K alter session set nls_date_format=DD-MON-; select instance_name, lower(instance_name) passout from gv$instance; set term on set sqlprompt 'dbname' column name print -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Sais INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: SID on sqlplus prompt?
How about something like this? Of course, it requires that the user have select access to v$instance -- maybe there's another place to find this. You could do some sort of batch to reference the $ORACLE_SID instead, but the advantage of this is you can put it into glogin.sql or login.sql -- or maybe not. I just tried doing that and it didn't affect anything. Hmm. Oh, well, this may help you... set heading off set term off set feedback off spool set_sqlprompt.lst select 'set sqlprompt ' || instance_name || ' ' from v$instance / spool off @set_sqlprompt.lst set heading on set term on set feedback on Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Walter K alden14004@yTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ahoo.comcc: Sent by: Fax to: root@fatcity.Subject: SID on sqlplus prompt? com 06/08/2001 04:35 PM Please respond to ORACLE-L Hi, Is there a way to get the SID or database name displayed in the command prompt of SQL*Plus? Can this be generated dynamically if I perform a CONNECT user/pw ? Thanks! -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).