How do I retrieve available schemas in database?

The examples below show you how to get the available schema names in Oracle database. The available schema names can be obtained from the DatabaseMetaData object by calling the getSchemas() method.

This method returns a ResultSet object. After obtaining the ResultSet object we need to iterate it and get the schema name by calling the result set getString() method and pass TABLE_SCHEM as the column name.

package org.kodejava.example.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MetadataGetSchema {

    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();

            // Gets DatabaseMetaData
            DatabaseMetaData metadata = connection.getMetaData();

            // Retrieves the schema names available in this database
            ResultSet rs = metadata.getSchemas();

            while ( {
                String schema = rs.getString("TABLE_SCHEM");
                System.out.println("Schema: " + schema);
        } catch (Exception e) {
        } finally {
            try {
            } catch (SQLException e) {

     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
    private static Connection getConnection() throws Exception {
        String url = "jdbc:oracle:thin:@localhost:1521:xe";        
        return DriverManager.getConnection(url, "kodejava", "welcome");

     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {

Here an a list of schema names available in the Oracle XE database:

Schema: CTXSYS
Schema: DBSNMP
Schema: DIP
Schema: FLOWS_020100
Schema: HR
Schema: MDSYS
Schema: OUTLN
Schema: SYS
Schema: SYSTEM
Schema: TSMSYS
Schema: XDB

Wayan Saryada

Founder at Kode Java Org
I am a programmer, a runner, a recreational diver, currently live in the island of Bali, Indonesia. Mostly programming in Java, Spring Framework, Hibernate / JPA. You can support my works by donating here. Thank you 🙂.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.