Getting database name?

Is there a select statement that you can do with dual to get the database name?

You can get the database name from

select * from v$instance;

Sam

Select name from v$database ;
also give you the info

Badrinath

Is there a way I can get the name without querying a view?

svrmgrl
connect internal
sho parameter db_name


Cheers

Quote:

---

Originally posted by lesstjm
Is there a way I can get the name without querying a view?

---

Well, you'll have to query something, either dual or some other table or view ;).

The suggested method with V$DATABASE and V$INSTANCE are OK, but one must have DBA role or some other quite strong privilege, or explicite privilege on those views. I for example don't like to give my users any privilege that they don't realy need for their work.

Well, to get the database name out from the sistem without having any special privilege, one can use *publicly available* view GLOBAL_NAME with the single column named GLOBAL_NAME, which contains (gues what!) global database name. So it behaves exactly as DUAL: it is a single_row/single_column, and everybody has gotten select privilege on it!

SELECT * FROM global_name;

HTH,

just tried what jmodic has given on my database,

on my databases, one 8i and another 7.3 thru sql*plus

* in 8i i am getting the database name as "ABCD" thru the statement
select name from v$database
BUT when i give the statement
select * from global_name
i am getting the database name as "ORACLE"

* in 7.3 i am getting the database name as "XYZ" thru the statement
select name from v$database
BUT when i give the statement
select * from global_name
i am getting the database name as "XYZ.WORLD"

can u tell me how the global name gets generated, i.e. does the name come from the name in v$database table or from somewhere else.

oracle 8i
Give you a SYS_CONTEXT function that give you access to the value of a lot of user environment variables.
eg. DB_NAME, IP_ADDRESS, TERMINAL, SESSION_ID...
The sql to get the DB_NAME is
select sys_context('USERENV','DB_NAME') from dual;

before oracle 8i,
you can connect as sys and create a view for use
create view DB_NAME_V as select name from v$database;
create public synonym DB_NAME_V for DB_NAME_V;
grant select on DB_NAME_V to public;

The value for GLOBAL_NAME on 8i or 7.3.x will depend on the INIT.ORA of yr ddatabase and also depend if someone has changed.

Check what is it about "global name" from metalink

Doc ID : 115499.1


Hope it is helpful to you.