DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Getting database name?

  1. #1
    Join Date
    Jan 2001
    Posts
    515
    Is there a select statement that you can do with dual to get the database name?

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    You can get the database name from

    select * from v$instance;

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jan 2001
    Posts
    642
    Select name from v$database ;
    also give you the info

    Badrinath

  4. #4
    Join Date
    Jan 2001
    Posts
    515

    Not a view

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

  5. #5
    Join Date
    Oct 2000
    Posts
    467
    svrmgrl
    connect internal
    sho parameter db_name


    Cheers

    Vinit

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Not a view

    [QUOTE][i]Originally posted by lesstjm [/i]
    [B]Is there a way I can get the name without querying a view? [/B][/QUOTE]
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2000
    Posts
    14
    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.




  8. #8
    Join Date
    Mar 2001
    Posts
    22

    DB NAME

    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.




    hptse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width