How to see list of databases in Oracle?

  • Is there an equivalent to MySQL SHOW DATABASES statement?

    Is it possible to find databases in a cluster? i.e. databases present on the network on some other system?

    Could I analyze the files present on an Oracle installation to find the same?

    Given complete access credentials to an Oracle system how would you go about enumerating all the databases that exist?

    Note also there is a terminology mismatch between MySQL and Oracle Database. In terms of architecture, Oracle has table->schema->database, and at the same time also table->tablespace->database. MySQL has simply table->database. So maybe you are really searching for Oracle schema list (think: SQL namespaces for tables) or Oracle tablespace list (think: groups of OS files for tables).

  • Mat

    Mat Correct answer

    8 years ago

    Is there an equivalent to MySQL SHOW DATABASES statement?

    There is no such thing. You can query listeners on a machine (lsnrctl status) to see what services are registered there, but that doesn't map one-to-one to database (and there could be multiple listeners on the same machine). Otherwise, the tools commonly used connect to one database instance, and an instance belongs to a single database.

    If you're talking about Oracle RAC clusters, then each instance knows of its peers (other instances that service the same database) and you can find the other instances currently started for that database using the gv$instance view.
    You can also use the crsctl utility to list the services (including databases) that are registered in the cluster, and their status.
    If you're talking about another vendor's clustering software, I'm pretty sure they all have these types of resource management utilities to query.

    If you're talking about just a bunch of machines, then no, there's no 100% reliable way of enumerating all databases on a network.

    To find active (i.e. started) databases, look for *_pmon_* processes on Unix (there's one per database instance), and Oracle services on Windows.

    To locate installations of Oracle database software, look at /etc/oratab on Unix. This should contain all the ORACLE_HOMEs installed. You can look inside each of those in $ORACLE_HOME/dbs for spfile<SID>.ora and/or init<SID>.ora files - there will be one for each database.

    (I believe you can find the equivalent of the information in oratab in the Windows registry keys below HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, but I don't know its structure.)

    Now, of course, if you've registered all your database with an OEM (Enterprise Manager) server when you installed them, you can find the complete list there - but I guess if you're asking that's not the case.

  • Oracle has no databases but schemas, you can list them with

    SELECT USERNAME FROM ALL_USERS ORDER BY USERNAME; 
    

    or something like this :

    SELECT TABLESPACE_NAME FROM USER_TABLESPACES;
    

    Tablespaces are not linked to schemas in general.

    ALL_USERS? USER_TABLESPACES?

    "Is there an equivalent to MySQL SHOW DATABASES statement?" im trying to answers that question. dont say u dont know

    @DevYudh: a tablespace in Oracle is something **completely** different than a "database" in MySQL. The first query if correct, the second query is plain wrong

  • Simply speaking, there is no direct analogy for MySQL 'databases' or a 'cluster' on Oracle: the closest match is a 'schema' but that is still very different.

    This is apparently going to change in 12c with the introduction of pluggable databases:

    Dividing the database operations cleanly from the user's content is a major transformation from traditional database architectures. Oracle 11g, and all its predecessors, could only run one database at a time, Kyte said. If an organization wanted to run multiple databases on a single server, it would need to run multiple instances of Oracle 11g, one for each database. As the name implies, pluggable databases allow multiple tenant databases to run under one copy of Oracle 12c.

    Uh great, more complication, more job security...

  • I think an answer for future browsers on *nix could be:

    cat /etc/oratab

  • Just connect to ASM and check database client.

    set pages 999 lines 120
    col SOFTWARE_VERSION for A15
    col INSTANCE_NAME for A20
    col DB_NAME for A20 trunc
    select INSTANCE_NAME, DB_NAME, STATUS, SOFTWARE_VERSION as "version"
    from  V$ASM_CLIENT;
    

    This assumes ASM is being used! What about databases stored on a filesystem or SAN?

  • If you have Pluggable Databases (recommended) on Oracle 12, you can do following:

    SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
    
  • For standalone database to get the list of databases which are started automatically after host rebooting:

    cat /etc/oratab | grep -i ":y" | grep -v "^#"
    

    or just to get the list of all databases:

    cat /etc/oratab | grep -v "^#"
    

    For RAC databases following method can be useful:

    crsctl stat res -t | grep "\.db"
    

    Also as it was already mentioned the database in MySQL is not the same as the database in Oracle. In Oracle it is more closer to schema - that is named container for user's objects. To get schemes list you may use following SQL statement:

    select username from dba_users order by 1;
    

    or to get list not system related schemes (available in Oracle RDBMS starting from the version 12c):

    select username from dba_users where ORACLE_MAINTAINED='N' order by 1;
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM