Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

[OT] Get the database name in Oracle

by kulls (Hermit)
on Jan 02, 2006 at 11:12 UTC ( #520376=perlquestion: print w/replies, xml ) Need Help??

kulls has asked for the wisdom of the Perl Monks concerning the following question:

Greetings all,
What is the equvalent of "show databases; (in mysql)" command in the oracle ?. Also "select * from tab" only identify the tables not the database which i logged in. I'm using Oracle 8i and sqlplus for running queries.I'll be grateful,if anybody help me.

Update: Can i get the database details through DBD::ORACLE module ? if so, please help me.

Replies are listed 'Best First'.
Re: [OT] Get the database name in Oracle
by aquarium (Curate) on Jan 02, 2006 at 11:50 UTC
    if you're on unix, go to $ORACLE_HOME directory, then into dbs directory. there'll be a file there for each SID (database) with a file name initTHE_SPECIFIC_SID_NAME.ora
    there'll be one entry for each SID/database (if it's not specified as * = ALL) in /etc/oratab.
    On windows, go to oracle insallation directory, then database directory...there'll be a initSID_NAME.ora for each SID/database. There'll be a windows service for each SID, with a name like oracleserviceSID_NAME.
    the hardest line to type correctly is: stty erase ^H

      Thank you for your information. Can you show me in a sql query format to get the database name ?. I got your points which is relevent to the physical image, whereas i need to get the details like a query. Can you please ?
Re: [OT] Get the database name in Oracle
by erix (Prior) on Jan 02, 2006 at 14:59 UTC

    The v$ tables contain meta information:

    select * from v$database;

    gets you some basic information (which includes the database name, IIRC).

    update (2008-12-22):

    Since we seem to be reviving this thread - here is another one:

    select ora_database_name from dual

    (Oracle 10g)

      Well, from erix response, above, I tried the following in sql*plus and it worked: select name from v$database; It'll print the database you're currently loged in.
        it is not working for me! :( Here is the output when I tried in SQL plus window: SQL> select name from v$database; select name from v$database * ERROR at line 1: ORA-00942: table or view does not exist
Re: [OT] Get the database name in Oracle
by astroboy (Chaplain) on Jan 03, 2006 at 08:28 UTC
    You can't really mix your MySQL "show databases" concept with Oracle as the underlying philosophy is different. In MySQL a single engine is used to maintain all databases, but Oracle has a separate "engine" for each database - i.e. nothing is shared unless you have a clustered db - so the databases are independent of - and have no knowledge of - each other. As someone pointed out, the oratab file has a list of databases, but some may have been deleted and the oratab not changed to reflect it.
      I can able to understand what you're saying and i don't know much about Oracle.
      if you look into the DBD::ORACLE ,
      use DBI; $dbh = DBI->connect("dbi:Oracle:$dbname",$user,$passwd);
      What's the meaning of  $dbname here ? . when i invoke sqlplus usr/pwd , then it entered into some database.i donno which one..Just i want to know before i put my hands on this.

        Well, in this context dbname isn't the name of your database, but a tnsname (which may be in a local tnsnames.ora file, your Oracle Names server or on an LDAP server). In other words the tnsname is a shorthand descriptor for the connection parameters to your database (including protocol, host or ip address, etc) It *may* (and usually does) have tha same name as your database, but this isn't required

        If all you want to do is get your connect name, it should be in $dbh->{Name} (I don't have ready access to Oracle at the moment, but I tested that it works for MySQL). Otherwise, get your DBA to give you access to v$database, and you can query the real db name from there.

Re: [OT] Get the database name in Oracle
by gsiems (Deacon) on Dec 22, 2008 at 16:19 UTC

    There isn't any "show databases" equivalent in Oracle.

    FWIW, I use Postgresql and Oracle, not Mysql, and am guessing that Mysql is similar to Postgresql in this respect. Postgresql supports the "database cluster" part of the SQL standard so you get one cluster per instance with one or more databases per cluster. Oracle does not support the "database cluster" part of the SQL standard so you only get one database per Oracle instance.

    To determine the name of the currently connected database, you should be able to:

    select global_name from global_name;
Re: [OT] Get the database name in Oracle
by thargas (Deacon) on Jun 05, 2012 at 19:04 UTC
    Have you looked at the various metadata methods in DBI, like table_info(), column_info(), data_sources()? I don't have access to an Oracle db so I can't try them, but some DBD implementations have good support for them.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://520376]
Approved by astroboy
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (7)
As of 2021-06-21 12:27 GMT
Find Nodes?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)

    Results (98 votes). Check out past polls.