Obtaining a list of all databases via SQL
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Obtaining a list of all databases via SQL

  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Obtaining a list of all databases via SQL

    Hi guys,

    how can I get a list of all databases that exist in an Oracle database installation by using normal SQL commands?

    Bye and thanks,
    Werner.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    nope

  3. #3
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    On Unix I guess you could write a PL/SQL pipelined function that uses UTL_FILE to parse /etc/oratab and then query it using "normal" SQL (which doesn't mean you can't do something similar on Windows. I just don't know it very well)

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    doesnt mean the database is there, just means there is an oratab entry

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I agree, there's no (sysdatabases - SQL Server) packaged view or procedure to query a list of databases, just convoluted workarounds.

    Here's a few that haven't been mentioned.

    1. External table (etc/oratab) - providing it's up-to-date. If you use DBCA to create/remove your db's, it'll have them.

    2. A java stored procedure. I'm sure there's an class that'll shell/call the OS for "ps -ef|grep _smon|grep -v grep"

    3. If you've got an 9.x OMS (Oracle Management Server) for OEM (Oracle Enterprise Manager) and have "discovered" your databases, you can query the following tables:

    -- Discovered Servers --
    select * from .smp_vdg_node_list

    -- Discovered Databases #1 --
    select * from .SMP_VDN_TARGET_LIST where typeid = 4

    -- Discovered Databases #2 --
    select value from .SMP_VDN_TARGET_PROPERTIES where name = 'oracle_sid'

    4. If you've got 10g Grid Control and have the agent running on the servers of interest, and have the target.xml file properly configured, and cross your eyes just right:

    -- MGMT_TARGETS --
    select * from .MGMT_TARGETS where target_type = 'oracle_database'
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    but simpley there is no way to be sure what databases are on a server. None

  7. #7
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    You must want to be proven wrong. I'd bet there's a big enough brain in the group to figure this one out. Is stecal still around?

    Let's start here:

    The original question was for a SQL query for all SID's under a given installation.

    I know it's not SQL now, but how about:
    ls -lrt $ORACLE_HOME/dbs/init*.ora $ORACLE_HOME/dbs/spfile*

    Just thinking here ... about the rules.
    Is $ORACLE_HOME set?
    Is $ORACLE_HOME/bin in $PATH?
    Is $ORACLE_SID set?
    Is the $ORACLE_SID open and accepting connections?
    Last edited by KenEwald; 10-27-2004 at 05:59 AM.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    I won't be proven wrong on this one :-)

    There may be an spfile or an init.ora file - does that mean there is a database there - no. Or I dont use oracle_home/dbs for my files

    Is ORACLE_HOME set - yes, does that mean there is a database - no

    Is the path set - yes, does that mean there is a database - no

    Can I connect to a database - yes - does that tell me how many databases are on the server - no

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I think I'm with Davey on this. There are lots of indications of what db's MIGHT be around, but it needs more that that to say you have a db.

    It gets rather philosophical, is a corrupt db a db? You will have to define some kind of threshold to determine if a db exists. Perhaps a db is something that you can "startup mount", even if you can't open it?

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    This is one of those yes and no type questions. Generically, no, there is no way you can tell how many databases reside on a host.

    However, if you are in an environment where standards have been enforced, then you could use the oratab file. In my environment, I know that unless there is an entry in the oratab file, the database doesn't exist. I rely on the oratab file for monitoring and make sure it accurately reflects the contents of that server. (Actually, I liked KenEwald's idea of an external table.)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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