Re: show user command useful in sqlplus

A number of ways exists. If you are on version 6 then just create a table on all your databases which holds the sid and maybe a title. Make it public access and give it a public synonym. select * from dbname;
SID TITLE

----- --------------------

Or

Under 6 there is an internal Oracle table which holds this infomation. But since it's internal and you would need DBA privs to read it it's not any good for all users.

Or

Version 7 has a dba view called v$database. I think if you grant v_$database to public anyone can select * from v$database.

or (v7 again)

create a storaged procedure owned by SYS which selects from v$database and uses dbms_output.put_line to print the database name. Then from sqlplus say:
((requires the dbmsotpt.sql loaded on the database.))

set serveroutput on
execute db_name

Example might be like this. (Not tested it)

connect to sys/?????
create or replace procedure db_name as
dbname varchar2(10);
begin

   select name into dbname from v$database;    dbms_output.put_line(dbname);
end;
/
grant execute on db_name to public;
create public synonym db_name for sys.db_name;

Because a non dba has access to the procedure they don't need access on the view v$database.

Final note, The last few methods give you the database name and not the sid. Remember they are don't have to be the same thing,

Regards

Nigel Noble.

Denis Langlais (ag831_at_FreeNet.Carleton.CA) wrote:

: I find the "show user" command in sqlplus useful
: to tell me what I'm logged on as. However it would
: be nice to have a show dbname command to tell
: me what database I'm logged on. I know in
: unix that you can issue !echo $ORACLE_SID but this
: may not be reliable if your using sqlnet to another host.
 

: Any ideas?

--
                                  _____________
    Nigel Noble                       churchill        Tel: +44 81 313 5677
    Database Administrator        =============        Fax: +44 81 313 5699
    Churchill Technology Ltd
    15-17 London Rd, Bromley BR1 1DE, UK.    Email: nigel_at_churchill.co.uk