-
Move this to the appropriate forum if necessary.
Sambavan posted a question earlier about firing a message to the users screen when they login in. (http://www.dbasupport.com/forums/showthread.php?threadid=16324)
Here is a piece of code I added to my glogin.sql. I frequently logon to multiple databases on multiple machines. I was having a hard time keeping track of which username/instance/machine I was connected to. (Multiple windows all with the SQL>). This piece of code will automatically put the username followed by the machine I connected to as the sql prompt.
set termout off
spool set_prompt.sql
select 'set sqlprompt'||' "' || a.username ||'-'|| b.terminal ||'> "'
from v$session a, v$process b
where a.paddr = b.addr
and a.sid = (select sid from v$session_connect_info);
spool off
start set_prompt.sql
set termout on
Hope you like it. Hope it helps.
-
Thanx for the posting. Yeah, I have something similar to this one.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Originally posted by ocp2b
set termout off
spool set_prompt.sql
select 'set sqlprompt'||' "' || a.username ||'-'|| b.terminal ||'> "'
from v$session a, v$process b
where a.paddr = b.addr
and a.sid = (select sid from v$session_connect_info);
spool off
start set_prompt.sql
set termout on
I have Just Joined this forum today and find for the GLOGIN.SQL
and find this thread
and when i issue these statments then it gives me error
and a.sid = (select sid from v$session_connect_info)
*
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row
Will be thank full for Helping
Last edited by KashifDBA; 12-19-2003 at 08:26 AM.
-
Jus issue the "select sid from v$session_connect_info" command and look at the result. The result should be 1 row. The error states there are more rows returned.
I guess, when you'll issue the statement, you'll understand the problem & the solution.
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
-
For SID u can use global variable (9i)
set sqlprompt '&_connect_identifier'
-
Originally posted by efrijters
Jus issue the "select sid from v$session_connect_info" command and look at the result. The result should be 1 row. The error states there are more rows returned.
I guess, when you'll issue the statement, you'll understand the problem & the solution.
I have already issued this statment in SQL. then its return more then 80 rows. actually it is NETWORK Database so there are too many users are connected at one TIME. (i'm using Oracle 8i)
Last edited by KashifDBA; 12-20-2003 at 04:46 AM.
-
hi,
i am also getting similar error.
my "select sid from v$session_connect_info" gets me 790 rows.
what do i do?
with thanks
Raja
-
DUH! Use distinct clause in sub query
select distinct sid from v$session_connect_info
-
This thread is getting painful to watch . . . try this:
Code:
select 'set sqlprompt'||' "' || a.username ||'-'|| b.terminal ||'> "'
from v$session a, v$process b
where a.paddr = b.addr
and a.audsid = (SELECT USERENV('SESSIONID') FROM Sys.Dual)
-
Code:
set line 130
set serverout on
set pagesize 40
set termout off
col plan_plus_exp for a96
column gname new_value gname
variable server varchar2(64)
declare
l_role varchar2(36);
l_user varchar2(30) := user;
begin
begin
select role
into l_role
from session_roles
where role='DBA';
exception
when no_data_found then null;
end;
if l_role = 'DBA' or l_user = 'SYS'
then
select lower(user) || '@' || instance_name || '-' || upper(host_name)
into :server
from v$instance;
else
select lower(user) || '@' || substr(global_name, 1, decode(instr(global_name, '.', -1, 1), 0, length(global_name) + 1,
instr(global_name, '.', -1, 1) ) - 1)
into :server
from global_name;
end if;
end;
/
select :server gname
from dual;
set sqlprompt '&gname>'
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';
set termout on
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|