login.sql
Sudhakar, March 21, 2001 - 11:36 pm UTC
Hi Tom,
just came to know this place for oracle doubts and clarifications, a few days ago. its been very useful.
The login.sql way for sqlprompt was obvious but i woul like to kno can we have any trigger which will give me sqlprompt when i connect to some other user or database. Because login.sql will get accesed when we login to sqlplus first time.
For instance
first login
system/manager@test
system@test>
'well this is fine'
system@test> connect scott/tiger@test1
system@test>
' the prompt doesnt change to scott@test1'
Hope u understood what i am trying to say
Thanks
Sudhakar
sql prompt
mo, August 20, 2002 - 9:17 pm UTC
Tom:
1. Can you change the prompt if you connect internally to a second instance? do you have to write ONCOnnect trigger?
thanks
August 20, 2002 - 9:40 pm UTC
2 prompts instead of One SQL> Prompt
Vikas Khanna, September 06, 2002 - 7:41 am UTC
Hi Tom,
I have been using this for quite some time, but everytime I work with it, found that instead of one sql prompt we get 2 sql prompts if the glogin.sql or login.sql has that text written for changing that SQL prompt.
Instead of regular SQL> prompt just one which we get by default,I always get something like this :
SCOTT@Oracle8.1.7>
SCOTT@Oracle8.1.7>
Where I am wrong in my script? Please Clarify my doubt.
Thanks
September 06, 2002 - 7:46 am UTC
Well, my super special eye goggles that let me see long distances (all of the way to your monitor) are broken right now. Hence, I cannot comment on what might be wrong with your script.
I can only say that if you cut and paste what I have AS IS and use it to start - it shouldn't happen (never has for me)
that and it is nothing more then a very tiny, minor "annoyance" at best....
using CASE instead of DECODE
Paul Druker, September 06, 2002 - 9:21 am UTC
I've modified Tom's script a little bit -- to remove the second part (.WORLD) of the global_name value. This is the modified select statement:
select lower(user) || '@' ||
case when instr(global_name,'.')>1
then substr(global_name,1,instr(global_name,'.')-1)
else global_name end
global_name from global_name;
Customising SQL prompt !
Rama, September 06, 2002 - 11:32 am UTC
Tom,
I have Oracle 9 on Win 2000 on my personal comp. Now, I searched for login.sql, instead I found glogin.sql and there's no connect.sql there. Is login.sql replaced with glogin.sql in Oracle 9. And what about connect.sql ?? Your response would be highly appreciated.
September 06, 2002 - 1:34 pm UTC
login.sql is a "personal" login.sql. glogin.sql is a GLOBAL login.sql. They both have existed for as long as I can remember.
connect.sql is something I wrote that just does:
connect &1
@login
A Reader, September 16, 2002 - 7:21 am UTC
Tom,
If we do the changes in login.sql or glogin.sql to customise SQL Prompt but the effects of it do appear in the SQL Worksheet when we do use it.
How could we eliminate that script not to run when we are executing SQL Worksheet and to run when user is connecting to database via SQL*PLUS.
I am working with two Oracle Homes 8.0.5 and 8.1.7. In 8.0.5 for Sql Worksheet we get the results of the execute via fetch in one line / record.
But in 8.1.7 even in SQL Worksheet it does behave like SQL*PLUS as the results to appear are distorted and one record can consume many lines depending upon the length of the record.
Please clarify.
September 16, 2002 - 9:27 am UTC
I never use sqlworksheet. Suggest you refer to the documentation for sqlworksheet.
A reader, January 15, 2003 - 6:58 pm UTC
Hi Tom,
I am new to unix.
How can I customize sql prompt on UNIX.
Thanks,
January 16, 2003 - 8:11 am UTC
depends on your shell. In the csh I use
set prompt = "\
`/bin/date`\
($USER@`/usr/ucb/hostname`) `pwd`\
> "
Other version of connect.sql
Chuck Jolley, January 16, 2003 - 9:46 am UTC
The prompt part of my connect.sql looks like this:
connect &1
set termout off
col prom new_value prom
select lower(sys_context('USERENV','SESSION_USER'))||
'@'||
lower(sys_context('USERENV','DB_NAME'))||
'.'||
lower(machine) prom
from SYS.V_$SESSION where sid = 1;
set sqlprompt "&prom> "
set termout on
The way things are set up here, this works fine.
But, are there any pitfalls in getting the server name the way I do?
For instance when we go from 8.1.7 to 9i?
thanks!
January 16, 2003 - 10:05 am UTC
it'll work but I would use DUAL instead of v$session personally.
I didn't aks clearly
Chuck Jolley, January 16, 2003 - 10:15 am UTC
I guess my real question was:
Is there a better way to get the name of the DB server than
select machine
from SYS.V_$SESSION where sid = 1;
and will sid = 1 always give the correct session?
Thanks,
Chuck
January 16, 2003 - 10:43 am UTC
select host_name from v$instance
Next lines
Wit, February 07, 2003 - 10:03 am UTC
Thanks to all.
Is there a way to form the prompt next to first one (with line numbers)? It's not handy to use mouse for copying. Thanks.
Prompt get stuck
Lucky lucky, March 31, 2003 - 4:20 pm UTC
Tom,
I have your script for glogin.sql and login.sql for customizing my sql prompt. This script makes use of v$instance (or v$thread) to read the instance name, so I have a prompt like:
myname@myinstance>
However, when I log in and the instance is not up, then the prompt gets "stuck" and I have to ctl-c to go on -- since the v$ views are not accessible in shutdown mode. That is when we usually get the "connected to an idle instance" message.
I saw in a recent posting </code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8913476988509 <code>,you wrote:
idle>
Is there any workaround so my prompt doesn't get stuck at this point ?
March 31, 2003 - 4:55 pm UTC
define gname=idle
at the top -- after the set termout off, before the query.
Prompt Does Not Change
Hien Luong, April 01, 2003 - 5:22 am UTC
Hi Tom,
I have noticed that in 8i(8.15-8.17) the prompt get changed correctly when you first started sqlplus but once you have been connected to a database and then switch to another the prompt remains the same.
For example,
$sqlplus scott/tiger@TEST
scott@TEST >
It works fine so far ..
then in sqlplus you switch to another database, say
scott@TEST > connect scott/tiger@PROD
the you would get
connected.
scott@TEST >
Note that the prompt has not been changed at all.
Can you please tell us how to change the prompt dynamically.
Thanks
Hien Luong
April 01, 2003 - 7:36 am UTC
see the first two comments at the top.
sqlprompt that works
Hien Luong, April 01, 2003 - 8:55 am UTC
Tom,
Yeah, a little trick that works beautifully. Hahhah!
Thanks
Hien Luong
excellent...
James Blanding, January 20, 2004 - 11:20 am UTC
> define gname=idle
> at the top -- after the set termout off, before the query.
very cool. this helps prevent confusion when sql*plus fails to connect to the instance you specify. my prompt variation looks like:
instance_name(host_name) >
or
NOT CONNECTED >
column sqlprompt_col new_value sqlprompt_value
set termout off
define sqlprompt_value='NOT CONNECTED'
select instance_name || '(' || host_name || ')'
as sqlprompt_col
from v$instance;
set termout on
set sqlprompt '&sqlprompt_value > '
Check out 10g!
Mark J. Bobak, March 04, 2004 - 1:47 pm UTC
oracle@dvlpq1 [/home/admin/oracle]. oraenv
ORACLE_SID = [ora10g] ?
oracle@dvlpq1 [/home/admin/oracle]sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Mar 4 13:45:24 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SYS@ora10g>connect adds@dvl1
Enter password:
Connected.
ADDS@dvl1>connect adds@pep1
Enter password:
Connected.
ADDS@pep1>connect system@prd1
Enter password:
Connected.
SYSTEM@prd1>show sqlprompt
sqlprompt "_user"@"_connect_identifier>"
SYSTEM@prd1>
Cool, eh? Seems to me, this should have been a feature
of SQL*Plus years ago!
March 04, 2004 - 2:34 pm UTC
you can even "spool filename append" in 10g ;)
Spool append?
Mark J. Bobak, March 05, 2004 - 2:34 am UTC
Excellent, I did not know that. Now, the real bummer is
that we won't be on 10g for any substantial systems for
at least 2 years! ;-)
Removing feedback info when connecting within SQL*Plus
ht, March 17, 2004 - 6:05 pm UTC
Tom,
I'm using a shell script to connect to an instance and setting a shell env variable.
I used to connect with "sqlplus -s user/password" but am now connecting within a sql script to prevent command line password sniffing.
I can use awk to get rid of the "Connected." string but is there a SQL*Plus setting that will allow me to not display the connection confirmation?
conn scott/tiger;
Connected.
TIA
March 17, 2004 - 6:34 pm UTC
if you are using a script, set termout off will surpress the display.
set termout off doesn't suppress the "Connected." display within a script.
ht, March 17, 2004 - 6:42 pm UTC
Tom,
It doesn't seem that "set termout off" suppresses the display when connecting within the shell script. I'm using 2 connections (the first one uses scott/tiger but the second one is the id/password that I don't want snooped on the command line).
Besides using awk to remove "Connected.", is there another solution?
$ versiontest1.sh > versiontest1.log
$ cat versiontest1.sh
#!/bin/ksh
function check_db_version {
DBVERSION=$(
sqlplus -s scott/tiger <<- eof8
set linesize 300;
set echo on;
set heading off;
set feedback off;
set verify off;
set termout off;
connect user2/pass2
select substr(&_O_RELEASE,1,1) from dual;
exit;
eof1
)
}
check_db_version
echo "xyz:$DBVERSION:123"
exit
if [ $DBVERSION = "7" ];
then
echo v7 db
else
echo not a v7 db
fi
# ---------------------------------------------------------------- #
$ cat versiontest1.log
xyz:Connected.
9:123
$
March 17, 2004 - 6:49 pm UTC
it works if SQLPLUS is running a script.
so, either put the sets and all in a script like /tmp/$$.sql and run that or
select 'LOOK_FOR_ME' || substr( .... ) from dual;
and "grep" that in the sqlplus command... I'd offer to script it -- but your script doesn't see to run "as is" on linux? (and I don't do ksh...)
login.sql
robert james hanrahan, April 14, 2004 - 7:19 pm UTC
hi Tom,
first of all I wanted to say that your site is very
helpful for the many DBAs across the world :)
anyway,
I changed the login.sql according to the setting about the
global_name and it works fine until I founded another
instance that had the same global_name so I work it around
like this:
column INSTANCE_NAME new_value gname
set termout off
select lower(user) || '@' ||
UPPER(decode(INSTANCE_NAME, 'ORACLE8.WORLD', '8.0', 'ORA8I.WORLD', '8i', 'ORA8IDEV.WORLD', 'dev8i', INSTANCE_NAME ))
INSTANCE_NAME
from v$instance;
set termout on
set sqlprompt '&gname> '
this way I get the instance_name instead of global_name.
Maybe this Devel DB was made from the Production one.
cheers
rjh
April 15, 2004 - 8:16 am UTC
instance_name = ORACLE_SID = same issue in general as global name :)
use what works for you of course, I keep my global names globally unique (and the same as the sid in fact)...
Using decode to dynamically print version info?
ht, April 19, 2004 - 8:20 pm UTC
Hi Tom,
How can we convert the output of "902000300" to "9.2.0.3" dynamically? In other words, I can use "decode" to reformat the output of &_O_RELEASE but I would like to dynamically print "." instead of "0" so "902000400" wouldn't need a decode to print "9.2.0.4".
Thanks,
ht
>select &_O_RELEASE from dual;
old 1: select &_O_RELEASE from dual
new 1: select 902000300 from dual
902000300
----------
902000300
1 row selected.
select decode(902000300,'902000300','9.2.0.3') from dual
DECODE(
-------
9.2.0.3
April 20, 2004 - 8:18 am UTC
ops$tkyte@ORA9IR2> select trunc( (&O/100000000)) || '.' ||
2 trunc( mod((&O/1000000),100)) || '.' ||
3 trunc( mod((&O/10000),100)) || '.' ||
4 trunc( mod((&O/100),100))
5 from dual;
old 1: select trunc( (&O/100000000)) || '.' ||
new 1: select trunc( (902000300/100000000)) || '.' ||
old 2: trunc( mod((&O/1000000),100)) || '.' ||
new 2: trunc( mod((902000300/1000000),100)) || '.' ||
old 3: trunc( mod((&O/10000),100)) || '.' ||
new 3: trunc( mod((902000300/10000),100)) || '.' ||
old 4: trunc( mod((&O/100),100))
new 4: trunc( mod((902000300/100),100))
TRUNC((
-------
9.2.0.3
A reader, April 20, 2004 - 9:55 am UTC
A reader, May 14, 2004 - 12:38 pm UTC
Oracle10g
Laurent Schneider, June 29, 2004 - 5:36 am UTC
Hi,
In Oracle10g, you can do :
set sqlp "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "
Which is simple and dynamic.
SQL> set sqlp "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "
SYS@LSC62 AS SYSDBA> conn scott/tiger
SCOTT@LSC62 > disc
@ > quit
However, the prompt buffer is limited to 50 characters, so if you have "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE" in your prompt, you have only 12 character left.
Please note that _USER is not equivalent to &_USER because &_USER is executed only during the "set sqlp" and _USER is executed for each prompt. _USER may use more space then &_USER.
Ok, I give my colorfull login.sql for unix terminals in 10g, largely inspired from this thread !
Regards
Laurent
col "_myprompt" new_value myprompt
set termout off
select
chr(27)||'[31m'||
substr('&_USER@&_CONNECT_IDENTIFIER'||rtrim(' &_PRIVILEGE')||'/'||
trunc(&_O_RELEASE/100000000) || '.' ||
mod(trunc(&_O_RELEASE/1000000),100) || '.' ||
mod(trunc(&_O_RELEASE/10000),100) || '.' ||
mod(trunc(&_O_RELEASE/100),100),
1, 36)
||chr(27)||'[m'||chr(10)||'SQL> ' "_myprompt"
from dual;
set termout on
set sqlp "&myprompt"
take care of those login.sql
Laurent Schneider, June 29, 2004 - 7:47 am UTC
Hi,
I have meet some side effects ...
If you use "set termout off" and then retrieve a &variable with a select and then use that variable in your prompt, all that in your login.sql script, then Oracle Grid Control will no longer be able to discover your nodes :-(
To survive, you can do "export SQLPATH=invalid" before starting the agent/deamon.
Regards
Laurent
sqlplus /nolog
Laurent Schneider, June 30, 2004 - 7:59 am UTC
Hi Tom,
Did you try "sqlplus /nolog"? Don't you get a
Enter value for gname:
To bypass this problem, I give a default value to my prompt before the select
def gname="SQL> "
select ...
Regards
Laurent
June 30, 2004 - 10:20 am UTC
yes, i've added that over time.
save last command
Laurent Schneider, July 21, 2004 - 9:30 am UTC
Hi,
If you have a 10g client, then your login will be read any time you log in (with connect). In order to keep the sql buffer, I did add
def gname=""
column global_name new_value gname
set termout off
save /tmp/saved
select lower(user) || '@' ||
decode(global_name, 'ORACLE8.WORLD', '8.0', 'ORA8I.WORLD',
'8i', 'ORA8IDEV.WORLD', 'dev8i', global_name ) global_name from global_name;
del 1 last
get /tmp/saved
host rm 2>/dev/null /tmp/saved.sql
set termout on
set sqlprompt '&gname> '
HTH
Laurent
add a point after the get
Laurent Schneider, July 21, 2004 - 10:45 am UTC
get /tmp/saved
.
Warning !
Laurent Schneider, July 22, 2004 - 8:28 am UTC
This kind of "set termout off" + Select generating discrete ORA- errors may have side effects.
Imagine someone run the following script on a mounted database on his 10g sqlplus
whenever sqlerror exit 1
connect sys/pw@db as sysdba
alter database open;
His script will fail, because the connect will generate an error, but it will be very difficult for him to identify the "hidden" select in glogin.sql.
Have a great day!
Laurent
One more problem
Arun Gupta, July 23, 2004 - 11:44 am UTC
Tom,
The SQL prompt customization works perfect. In some of our databases, some user accounts do not have access to v$instance . Such accounts always show as idle though they are connected. Is there a workaround?
Thanks
July 23, 2004 - 4:23 pm UTC
default gname to "you don't have access to v$instance"?
what would you like it to do?
you could try
select global_name from global_name;
instead of v$instance, i use global name myself. that has public access.
how to surpress & variable substitution message in SQL*Plus
Jianhui, September 09, 2004 - 4:25 pm UTC
Hi Tom,
in SQL*Plus, I dont want to see & variable substitution messages in my spool file, how can i do it? For example, running following commands,
SQL> spool my.txt
SQL> @test.sql TEST
SQL> spool off
I dont want to see following lines in my spool file my.txt
old 4: TNSNAME VARCHAR2(32) := '&1';
new 4: TNSNAME VARCHAR2(32) := 'TEST';
September 09, 2004 - 5:47 pm UTC
is it possible to surpress "connected"
Jianhui, September 13, 2004 - 12:48 pm UTC
SQL> connect system/pwd@sverice
Connected.
SQL>
Tom,
I dont want to see "Connected" in the spool file, while it dumps all output from multiple oracle instances to one spool file in SQL*Plus. Is it possible?
For example,
from command line:
sqlplus -S /nolog script_for_all_instances.sql
In the script, it looks like:
spool consolidate_report
connect user/password@instance1
@@generate_report.sql
connect user/password@instance2
@@generate_report.sql
...
connect user/password@instanceN
@@generate_report.sql
spool off
I want to see this in the spool file:
result of instance 1
result of instance 2
...
result of instance n
instead of:
connected
result of instance 1
connected
result of instance 2
...
connected
result of instance n
If it's impossible, i'll have to write shell script to redirect the output instead of doing it in SQL*Plus script. Just wanted to know if it's possible so i can take a shortcut.
Thanks
September 13, 2004 - 3:23 pm UTC
In 10g, you can do this:
set echo off
connect /
spool foo
select * from dual;
spoo off
connect /
spool foo append
select * from dual;
spoo off
but in 9i and before, i do not know of anyway to surpress it.
You might spool each to a separate file and then cat (or copy) them all into one
RE: is it possible to surpress "connected"
Laurent Schneider, September 20, 2004 - 5:50 am UTC
in your script just add set term on/off
spool consolidate_report
set term off
connect user/password@instance1
set term on
@@generate_report.sql
set term off
connect user/password@instance2
set term on
@@generate_report.sql
...
September 20, 2004 - 8:44 am UTC
did you actually try it? (i did)
[tkyte@xtkyte-pc tkyte]$ cat test.sql
spool x.lst
set termout off
connect /
set termout on
select * from dual;
spool off
exit
[tkyte@xtkyte-pc tkyte]$ sqlplus -S /nolog @test.sql
D
-
X
[tkyte@xtkyte-pc tkyte]$ cat x.lst
Connected.
D
-
X
[tkyte@xtkyte-pc tkyte]$
oops
Laurent Schneider, October 18, 2004 - 6:54 am UTC
ok tom, sorry about this, I did test it to quickly, and did look the result only on the screen, not in the spool file!
Still thinking about it ...
termout off in login.sql
Laurent Schneider, March 15, 2005 - 10:24 am UTC
After the connect, login.sql is turning on the terminal output.
login.sql:
set termout off
col p new_v p
select instance_name||'/'||version||chr(10)||'> ' p from v$instance;
set sqlp "&p"
set termout on
x.sql:
set termout off
connect scott/tiger
select * from dual;
$ sqlplus "/ as sysdba" @x
D
-
X
LSC68/10.1.0.3.0
>
Any idea to reset the original setting of termout?
March 15, 2005 - 10:26 am UTC
other then echoing the result of
SQL> show termout
out to a temp file, no.
how about the following lines
sql, March 15, 2005 - 6:59 pm UTC
in SQL*PLUS, "set sqlprompt" command is used to customize sql prompt..but is there a command to format the next following lines (Read ` as space character below)
I am looking for output like this
CUSTOM_PROMPT> select 1
`````````````> from
`````````````> dual
but what I always get is
CUSTOM_SQL_PROMPT> select 1
``> from
``> dual
So each time when I have to fine tune any sql I would have to revert to some sqlprompt 3 chars wide ,so that my copy paste activities is not interrupted
Looking for the command please
Thanks in advance
March 15, 2005 - 9:33 pm UTC
I've grown so used to
:1,$s/.*PT>/xxxx/
when cutting and pasting myself - makes everything "5" spaces in vi...
not that I am aware of, i didn't see anything in a quick glance.
Possible option for spacing
Mark A. Williams, March 15, 2005 - 9:51 pm UTC
If you "set sqlnumber off" in SQL*Plus, it will continue to use the value for "sqlprompt" on subsequent lines. Is that an option to keep everything "evenly spaced over" for easier copy and paste?
- Mark
March 15, 2005 - 9:56 pm UTC
there you go.... excellent idea, that would do what he wants I believe... It looks a little confusing, but makes a "square" cut and paste like in a windows cmd.exe session workable.
there goes my last hope
Venkat, March 15, 2005 - 9:56 pm UTC
Now..now..exactly now..I curse myself for *still* being a "windows" person..and when I was typing this message that "I would move to vi atleast for this" , I see another update posted by Mark..which would perfectly solve my need
Thanks Mark.for your nice tip
Thanks Tom..always been a pleasure visiting your site
thanks mark
Venkat, March 15, 2005 - 10:20 pm UTC
sorry..forgot to change the subject in previous post. I started composing "lost hope" soon I read Tom's reply..Your response/tip is highly appreciated.
Its almost been 6 years now..and still I am learning something new with oracle (again main credit goes to this site, flooded with valuable info)
:1,$s
Matthias Rogel, March 16, 2005 - 3:15 am UTC
:%s saves 1 keystroke ;-)
sorry, even 2
Matthias Rogel, March 16, 2005 - 3:17 am UTC
:-D
sqlprompt customization
Royal Ketchup, April 06, 2005 - 9:18 am UTC
Thank you Tom, this is most useful. I had to make one minor adjustment in the script body:
conn &1 &2 &3
this allowed me to do:
@conn / as sysdba
April 06, 2005 - 1:17 pm UTC
I always just
@conn "/ as sysdba"
RE: is it possible to suppress "connected"
Laurent Schneider, April 26, 2005 - 8:41 am UTC
you can delete it afterwards.
spool x.lst
connect scott/tiger
select * from dual;
spool off
host perl -ne 'unless(/^Connected./){print}' -i.bak x.lst
_CONNECT_IDENTIFIER
Yousef, June 08, 2005 - 8:24 am UTC
Dear Tom:
How do Oracle fill the value of the _connect_identifier? Is there a view that I can query to see it? Since I need to read this value in Oracle 8i, but _connect_identifier is available in Oracle 9i and above.
Thanks in advance
June 08, 2005 - 9:13 am UTC
it is not available in 8i, suggest you use the global name or instance name as demonstrated above. the tnsnames.ora entry is just a string, global name and instance name really mean something.
A reader, June 08, 2005 - 9:18 am UTC
Thank you for you advice.
On unix (Soalris) I keep getting &gname as the prompt
Paresh, August 14, 2005 - 8:08 pm UTC
Hi Tom,
Always a pleasure visiting your site. Recently I tried to make the sqlprompt trick work on unix(Soalris-ksh) at a client site but I keep getting &gname as the prompt (I am pretty sure this worked in the past).
Here is my login.sql
define gname=idle
column global_name new_value gname
set termout off
select lower(user) || '@' ||
substr(global_name,1,instr(global_name,'.') - 1) global_name from global_name;
set termout on
set sqlprompt "&gname>"
Thanks,
Paresh
August 14, 2005 - 9:03 pm UTC
works for me on solaris (shell doesn't matter)
do you have "set define off" or anything like that elsewhere?
GLOBAL_NAME & DB_NAME
Deepak, August 23, 2005 - 2:34 am UTC
Hi Tom,
I read somewhere that GLOBAL_NAME is a combination of DB_NAME and DB_DOMAIN. To check this I executed the following...
SQL> sho parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ---------------
db_domain string
SQL> sho parameter global_name
NAME TYPE VALUE
------------------------------------ ----------- ---------------
global_names boolean TRUE
SQL> select name from v$database;
NAME
---------
MYDB1
SQL> select global_name from global_name;
GLOBAL_NAME
----------------------------------------------------------------
HPDB1
But the above results made me confused.
Please help.
August 24, 2005 - 4:01 am UTC
the global name is whatever you would like it to be plus the domain of the last global name, or whatever you would like.
ops$ora9ir2@ORA9IR2> alter database rename global_name to xxx.yyy;
Database altered.
ops$ora9ir2@ORA9IR2> select global_name from global_name;
GLOBAL_NAME
-------------------------------------------------------------------------------
XXX.YYY
ops$ora9ir2@ORA9IR2> alter database rename global_name to ora9ir2;
Database altered.
ops$ora9ir2@ORA9IR2> select global_name from global_name;
GLOBAL_NAME
-------------------------------------------------------------------------------
ORA9IR2.YYY
ops$ora9ir2@ORA9IR2> alter database rename global_name to ora9ir2.hello.world;
Database altered.
ops$ora9ir2@ORA9IR2> select global_name from global_name;
GLOBAL_NAME
-------------------------------------------------------------------------------
ORA9IR2.HELLO.WORLD
Deepak you have the answer right in your post!!
Paresh, August 23, 2005 - 3:10 am UTC
Hi Deepak,
GLOBAL_NAME DB_NAME||.||DB_DOMAIN
You wrote...
SQL> sho parameter db_domain
NAME TYPE VALUE
----------- ----------- ---------------
db_domain string
so DB_DOMAIN = '' (above) and hence you got the expected result.
Cheers,
Paresh
Hey Tom I just saved you sometime ;).
Please review my post
Deepak, August 23, 2005 - 8:12 am UTC
Hi Paresh,
Thanks for your reply. But I am still unclear about your explanation. I am getting different results when I queried for the GLOBAL_NAME and the DB_NAME (Please check the output of the queries again). Why this discrepancy?
August 24, 2005 - 4:19 am UTC
the db_name is the database name, the global name is a globally (within your network) name you assign, it is whatever you like it to be.
suppress the Connected. output
Laurent Schneider, March 07, 2006 - 11:38 am UTC
Here is a method which works in 10gR2
sqlplus 10.1.0.5
$ echo connect / as sysdba | sqlplus -s /nolog
Connected.
sqlplus 10.2.0.2
$ echo connect / as sysdba | sqlplus -s /nolog
[no output]
:-)
Ben, April 10, 2006 - 11:14 am UTC
Tom,
I added this to glogin.sql on my Oracle 10gR2 client:
column global_name new_value gname
set termout off
select lower(user) || '@' || global_name into gname from global_name;
set termout on
set sqlprompt '&gname> '
and I get this:
C:\>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 10 10:43:04 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sadst04@uni0
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Enter value for gname:
>
I also tried it while adding a "set define off" command to the glogin.sql script, which gave me this:
C:\>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 10 10:39:44 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sadst04@uni0
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
&gname>
Is there something wrong with my script?
Thanks!
April 11, 2006 - 10:25 am UTC
take away the set termout off so you can debug this and see what you see.
but actually, in reading this - where did the "into gname" come from. Where do you see that on any of the above examples?
you have set define off
Reader, April 11, 2006 - 1:53 am UTC
You have
"set define off"
before you change the prompt!!!
eg---------
set define on
..toms bit...
set defin off
--------------
April 11, 2006 - 2:27 pm UTC
(in addition to using "select into" which isn't right...)
A reader, April 11, 2006 - 4:11 pm UTC
column global_name new_value gname
--set termout off
select lower(user) || '@' || global_name from global_name;
set termout on
set sqlprompt '&gname> '
gives this result:
C:\>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 11 15:45:19 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sadst04@uni0
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
LOWER(USER)||'@'||GLOBAL_NAME
--------------------------------------------------------------------------------
sadst04@DB_UNI0.WORLD
Enter value for gname:
>
April 11, 2006 - 7:24 pm UTC
you didn't alias the column - the column name isn't global_name, the column name is:
LOWER(USER)||'@'||GLOBAL_NAME
you might want to cut and paste my stuff without change to see that it works and then modify it from there.
How to get %ORACLE_HOME% from SQL
Rustam Kafarov, July 27, 2006 - 11:13 am UTC
How to get value of %ORACLE_HOME% from SQL In Oracle 9(!)
Following example works only in 10g:
SQL> set autopri on
SQL> var oracle_home varchar2(255)
SQL> exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME)
How to solve this problem in 9i?
July 27, 2006 - 12:17 pm UTC
a java stored procedure may be able to get it.
Re: How to get %ORACLE_HOME% from SQL
Laurent Schneider, August 11, 2006 - 7:48 am UTC
you can eventually get the oracle_home of the client with HOST in sqlplus
SQL> var oracle_home varchar2(255)
SQL> host echo exec :oracle_home:='%ORACLE_HOME%'>oracle_home.sql
SQL> @oracle_home
PL/SQL procedure successfully completed.
SQL> print oracle_home
ORACLE_HOME
-----------------------------
/opt/oracle/product/10.2/db_1
prompt
sam, September 30, 2006 - 3:08 pm UTC
Tom:
I have two databases with same name on 2 machines. One database is 8i and the other is 9i. I want the prompt to show me 8iDBNAME> or 9iDBNAME> depending on which database i log into.
IS there a way to do that? I was trying to get the service name into the prompt since one has a service nam "DBANAME" and the other is "NEWDBNAME" but I could not find the TNS service name in the data dicitonary views.
thanks,
sql prompt
sam, October 02, 2006 - 12:13 pm UTC
Tom:
Do you have any suggestions for the above question.
October 02, 2006 - 12:36 pm UTC
other v$ views have db version information. v$version for example
ops$tkyte%ORA9IR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
ops$tkyte%ORA9IR2> select substr( banner, 1, instr(banner,' ')-1 )
2 from v$version where banner like 'Oracle%';
SUBSTR(BANNER,1,INSTR(BANNER,'')-1)
----------------------------------------------------------------
Oracle9i
Putting TNS Service name in SQL Prompt
Tak Tang, October 05, 2006 - 10:21 am UTC
SQL*Plus 9.2 onwards maintains a substitution variable "_CONNECT_IDENTIFIER".
Here is a fragment of my "login.sql" script:
SET TERMOUT OFF
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT USER || '@' || NVL('&_CONNECT_IDENTIFIER', global_name ) sqlprompt
FROM global_name;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON
You can read all about predefined substitution variables in the SQL*Plus manual:
</code>
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12017.htm#sthref2141 <code>
You can list the current substitution variables by typing:
TANGT@opal> define
DEFINE _CONNECT_IDENTIFIER = "opal" (CHAR)
DEFINE _SQLPLUS_RELEASE = "902000600" (CHAR)
DEFINE _EDITOR = "vim" (CHAR)
DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production" (CHAR)
DEFINE _O_RELEASE = "902000400" (CHAR)
TANGT@opal>
Tak
Issues with running scripts
rp, February 09, 2007 - 6:59 am UTC
Hi Tom,
this is sort of related but apologies if not.
I have written some scripts that are run by my local (windows) sqlplus against a db on a unix box.
Halfway through running the scripts, i change user and continue:
conn &1/&2@&3
the variables were passed on the command line when sqlplus was called.
This works fine. The DBAs however run the scripts on the actual Unix box where the db is installed. They encounter an error with the '@&3' part. In fact, for them the scripts only work if it is like this:
conn &1/&2
This seems odd to me, how can SQLPlus on the server ever connect to any other instance? Is the server version of sqlplus hardwired or something?
TIA
R
February 09, 2007 - 8:26 am UTC
the @tnsnames bit is resolved by the client on the local machine (typically, you can use ldap to centralize this and not use a tnsnames.ora).
Your personal tnsnames.ora has the database alias you use
Theirs does not.
Re: Issues with running scripts
Stew Ashton, February 09, 2007 - 8:46 am UTC
On our Oracle server on AIX, before connecting locally we have to define the instance using an environment variable:
export ORACLE_SID=<instance_name>
If we want to change instances, we have to exit sqlplus, change the variable and then go back in. Using the "host" command from within sql*plus will not work.
Customizing sql prompts
Abraham, July 25, 2007 - 10:04 am UTC
Tom,
In multi user environment, I want to use "my own" customized sql prompt. If I modify glogin.sql, it will affect all users of that particular ORACLE_HOME. Is there anything that I can set in my .profile (name of a different glogin.sql, say) that will help me do this?
thank you.
July 26, 2007 - 5:24 pm UTC
just use the login.sql - set your SQLPATH environment variable to some directory, put a login.sql file there and do anything you want.
A reader, January 30, 2008 - 11:49 am UTC
Tom,
If I need to display, the below information, whenever I login to a new sql*plus session, what am I supposed to do?
INSTANCE_NAME HOST SCHEMA_NAME
---------------------------------------
TEST ABC.NET DEMO
January 30, 2008 - 1:50 pm UTC
create a login.sql or a glogin.sql file - read the sqlplus documentation for information on those files (or search for them on this site, i have examples here and there)
RE: Putting TNS Service name in SQL Prompt
Duke Ganote, January 31, 2008 - 11:12 am UTC
Sweet! I like showing server:database\user in my SQLprompt. However, some accounts don't have access to V$INSTANCE; SQL*PLUS used to prompt me for the SQLPrompt when V$INSTANCE wasn't accessible.
Now I can default to the _CONNECT_IDENTIFIER. My glogin.sql includes:
column COUNT(*) format 999,999,999,999,999;
column CNT format 999,999,999,999,999;
SET SERVEROUTPUT ON;
set pagesize 50000;
set linesize 132;
set termout off;
DEFINE sqlprompt=SQL;
COLUMN sqlprompt NEW_VALUE sqlprompt;
select
'&_CONNECT_IDENTIFIER'
||'\'||lower(user)
as sqlprompt -- default to TNS connectString+user
from dual;
select
host_name||':'
||SUBSTR( global_name
, 1
, case instr(global_name,'.')
when 0
then 99
else instr(global_name,'.')-1
end
) -- just first portion of global_name
||'\'||lower(user)
as sqlprompt
from global_name, v$instance;
set termout on;
set sqlprompt '&sqlprompt> ';
alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
On limited-authority accounts, it defaults to the connect identifier, otherwise I get the server:database name, as below.
SQL> connect huontest@dw_proto;
Enter password:
Connected.
Session altered.
<b>dw_proto\huontest> </b>connect cidw@cidwdev
Enter password:
Connected.
Session altered.
<b>dwdev1:CIDW\cidw> </b>connect cidwwrite@cidwprod
Enter password:
Connected.
Session altered.
<b>dwprod1:CIDW\cidwwrite> </b>
SYS_CONTEXT
SeánMacGC, February 01, 2008 - 6:40 am UTC
As mentioned further up in this thread, SYS_CONTEXT can be used to avoid the system views altogether:
SELECT LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_NAME') || '.' || SYS_CONTEXT('USERENV','HOST') global_name FROM DUAL;
@SeánMacGC. SYS_CONTEXT vs v$ views
Duke Ganote, February 01, 2008 - 11:00 am UTC
SeánMacGC, thank you! I'd not recently looked at all that SYS_CONTEXT now offers.
http://pages.citebite.com/q2b0u6g4jbpm This is what I now use in my glogin:
SELECT SYS_CONTEXT('USERENV','SERVER_HOST')
||':'
|| SYS_CONTEXT('USERENV','DB_NAME')
||'\'
|| LOWER(SYS_CONTEXT('USERENV','CURRENT_USER'))
as sqlprompt
FROM DUAL
;
login.sql in 11.1.0.6.0
Rajeshwaran, Jeyabal, June 27, 2010 - 5:51 am UTC
Tom:
I created a
login.sql file and placed that in BIN folder and it seems not working properly. Please help me to setup this
login.sql correctly.
D:\>sqlplus scott/tiger@10GR2
SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jun 27 16:06:59 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Now the SQL prompt is not set correctly. Below is my environment variable setting.
D:\>echo %path%
C:\Program Files\Business Objects\common\3.5\bin\NOTES\;
C:\Program Files\Business Objects\common\3.5\bin\NOTES\DATA\;
D:\oracle\product\10.2.0\bin\;
C:\WINDOWS\system32;C:\WINDOWS;
C:\WINDOWS\System32\Wbem;
C:\Program Files\TortoiseSVN\bin;
C:\Program Files\Windows Imaging\
D:\>echo %oracle_home%
D:\oracle\product\10.2.0\
D:\oracle\product\10.2.0\BIN>dir login.sql
Volume in drive D is DATA
Volume Serial Number is 3690-C12D
Directory of D:\oracle\product\10.2.0\BIN
09/04/2009 03:20 PM 2,641 login.sql
1 File(s) 2,641 bytes
0 Dir(s) 19,253,395,456 bytes free
But, If i navigate to BIN folder and connect through sqlplus the
login.sql script executes sucessfully.
D:\oracle\product\10.2.0\BIN>sqlplus scott/tiger@10GR2
SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jun 27 16:09:27 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
scott@10GR2>
Please help me to correct this.
-- Rajeshwaran, Jeyabal
Thanks for the question regarding "customizing the SQLprompt", version 9i
armelonzoukson, December 18, 2012 - 8:49 am UTC
Hi,
In oracle 9i tochange my SQL prompt displaying "user@instance" ,do that:
on linux or unix os go to glogin.sql file on $ORACLE_HOME/sqlplus/admin directoy using vi editor for example and copy this sql code:
SET TERMOUT OFF
COL login_prompt new_value prompt_dbname
SELECT SYS_CONTEXT('USERENV','CURRENT_USER')||'@'||SYS_CONTEXT('USERENV','DB_NAME') login_prompt FROM DUAL;
set sqlprompt "&&prompt_dbname>"
SET TERMOUT ON .
So it's ok.
regards
Updated for 12c?
Richard C. Evans, July 19, 2013 - 2:52 pm UTC
I've been using the SQLPROMPT for years now and with the introduction of CDBs and PDBs how would we update your original statement?
I would love to have my prompt be something like "user@cdb:pdb>" so we know exactly what we are connected to.
I investigated updates to the SQLPROMPT variables such as _CONNECT_IDENTIFIER but it does not get updated when we issue a connection to the PDB as "alter session set container = pdb1;"
Regards,
Rich
July 31, 2013 - 3:03 pm UTC
I don't see the use of alter session like that to set a container as something you'd be doing.
Rather, you would just connect to the database - pluggable or not - the "old fashioned way". The alter session command wouldn't be practical in life - you don't want a single credentialed user to be able to log in all over the place.
You'd be using good old connect. the alter session set container is good for a connection pooled thing perhaps - but not really for day to day use in my opinion.
Updated for 12c?
Richard C. Evans, August 01, 2013 - 3:44 pm UTC
Thanks Tom,
For better or worse, I use system or / as sysdba for almost every connection I make to any DB I maintain. I rarely login as an application user. In a 12c environment, where we have multiple PDBs for developers I see myself switching between them often to execute scripts, check jobs, create/unlock users, etc. I see us using something like "alter session switch container" quite frequently as DBAs -- maybe there is a more effective way to work on this but as I'm testing this is what I'm leaning on right now. I'm all ears :-)
What I do now is I wrote a couple short scripts to give me information on "where" I am and to "switch" between environments. The switch.sql is still a work in progress because I can't see any other PDBS/CDBS from v$pdbs if I'm in a PDB (i.e. in pdb1 I can't see that pdb2 exists). I'm probably querying the wrong view but I haven't had a chance to delve into it further yet.
[oracle]$ cat where.sql
column where format a30
SELECT 'USER: '||SYS_CONTEXT('USERENV','CURRENT_USER')
||chr(10) ||'SCHEMA: '||SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
||chr(10) ||'CONTAINER DB: '||SYS_CONTEXT('USERENV','CON_NAME')
||chr(10) ||'CONTAINER: '||SYS_CONTEXT('USERENV','CDB_NAME')
"WHERE"
FROM DUAL;
[oracle]$ cat switch.sql
prompt Choose which PDB/CDB you would like to switch to...
SELECT NAME, OPEN_MODE from v$pdbs;
alter session set container = &PDB_NAME;
prompt Show your new environment...
@where.sql
SYS@testdb> connect system
Enter password:
Connected.
SYSTEM@testdb> @where
WHERE
------------------------------
USER: SYSTEM
SCHEMA: SYSTEM
CONTAINER DB: CDB$ROOT
CONTAINER: testdb
SYSTEM@testdb> @switch
Choose which PDB/CDB you would like to switch to...
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 READ WRITE
Enter value for pdb_name: pdb2
old 1: alter session set container = &PDB_NAME
new 1: alter session set container = pdb2
Session altered.
Show your new environment...
WHERE
------------------------------
USER: SYSTEM
SCHEMA: SYSTEM
CONTAINER DB: PDB2
CONTAINER: testdb
SYS@testdb> @switch
Choose which PDB/CDB you would like to switch to...
NAME OPEN_MODE
------------------------------ ----------
PDB2 READ WRITE
Enter value for pdb_name: pdb1
old 1: alter session set container = &PDB_NAME
new 1: alter session set container = pdb1
Session altered.
Show your new environment...
WHERE
------------------------------
USER: SYS
SCHEMA: SYS
CONTAINER DB: PDB1
CONTAINER: testdb
Because I'm using _CONNECT_IDENTIFIER each time I switch PDBs my SQLPROMPT stays the same. I'm thinking I could utilize your command and tweak it to show the cdb:pdb each time. I'll play with the SQL logic more...
Thanks,
Rich
August 02, 2013 - 7:21 pm UTC
... For better or worse, I use system or / as sysdba for almost every connection I
make to any DB I maintain. ....
there is no better to it, it is all for the worse. sysdba should *hardly* ever be used. It is just way too dangerous.
you can either
a) use connect
b) use alter
they consume about the same number of characters. If you want sqlplus to behave the way you want sqlplus to behave - use connect.
and seriously- being 100% honest here - get out of the very very bad habit of using sysdba and system day to day, use your OWN DBA account, or maybe not even a DBA account.
Prakash, May 27, 2014 - 5:41 am UTC
try this
vi $ORACLE_HOME/sqlplus/admin/glogin.sql
now type this inside,
set sqlprompt "&_user>"
define_editor=vi
this will definitely work.
Alf, March 26, 2018 - 5:27 pm UTC
Hello Gurus,
For Non sys user custom prompt
The below custom prompt is to help regular users (no sys/system) I’ve shaped this to the point which shows me enough enough information. But, for a reason I can’t yet, the sqlp complains it too long the max is 50.
1) The actual numbers of characters for user-SID-Version-host is 39
2) the number the select returns is 68, there seems to be lost of added un-seem characters.
Tried use TRIM (L and R) did not make any difference.
Any idea what function would help to either undercover the the garage/space or spacial character and formatted with correct light? Thanks in advance for any insight..
rem #
set timing on
set heading off;
set termout off;
DEF p1= "SQL>"
col "p1" new_value gp noprint;
set lines 69
WITH g2 AS
( select chr(27) ||'[93m'|| substr((select user from dual),1,16)||chr(27)||'[m' || '@'||
chr(27) ||'[95m'|| substr(i.instance_name,1,6) ||chr(27)||'[m' || '-'||
chr(27) ||'[96m'|| substr(i.version,1,4) ||chr(27)||'[m' || '-'||
chr(27) ||'[91m'|| substr(i.host_name,1,11) ||chr(27)||'[m' unvh
from v$instance i
) SELECT trim(g2.unvh) p1 FROM g2;
set sqlp "&gp";
set termout on;
set heading on;
SQL> @../s/g6g.sql;
..
Elapsed: 00:00:00.13
string beginning “”xxx…” is too long. maximum size is 50 characters.
SQL>
--
SQL> @../s/g6g.sql;
P1
--------------------------------------------------------------------
XXXXXXXXXXXXXXXX-psup2-11.2-xxx-sdb-xxx = 39 characters
March 27, 2018 - 5:37 am UTC
SQL> WITH g2 AS
2 ( select chr(27) ||'[93m'|| substr((select user from dual),1,16)||chr(27)||'[m' || '@'||
3 chr(27) ||'[95m'|| substr(i.instance_name,1,6) ||chr(27)||'[m' || '-'||
4 chr(27) ||'[96m'|| substr(i.version,1,4) ||chr(27)||'[m' || '-'||
5 chr(27) ||'[91m'|| substr(i.host_name,1,11) ||chr(27)||'[m' unvh
6 from v$instance i
7 ) SELECT length(g2.unvh) p1 FROM g2;
P1
----------
56
When *finally* the characters get processed by the terminal, they are transposed into color commands etc, but *before* that, you have 56 characters of information. That is all that SQL Plus sees - it does not know that much of these "characters" are actually terminal control characters.
for Non sys user custom prompt
Alf, March 27, 2018 - 1:29 pm UTC
Correct and brilliant insight.
I've substr and removed color codes and chr(27) for some of the values to fit it 50 max chars.
Having said. Isn't "def p1=gp" suppose to bypass the 50 max characters limit? Can you spot a typo or something I keep missing. Thanks in advance.
....
def p1=gp
col "p1" new_value gp noprint
WITH g2 AS
( select substr((select user from dual),1,8)||'@'||
chr(27)||'[95m'||substr(i.instance_name,1,6)||chr(27)||'[m'||'-'||
substr(i.version,1,4)||'-'||
chr(27)||'[91m'||substr(i.host_name,1,9)||chr(27)||'[m' unvh
from v$instance i
) SELECT g2.unvh p1 FROM g2;
set sqlp "&gp SQL>"
set termout on
set heading on
March 28, 2018 - 1:42 am UTC
amper variables are simply text substitution.
So
set sqlprompt "&blah"
is expanded to:
set sqlprompt "my big piece of text which might be too long"
and you are in the same predicament.