How to Show a List of Databases in PL/SQL?

Managing databases is a fundamental aspect of database administration and development. In Oracle Database, schemas represent logical containers for database objects like tables, views, procedures, and functions. PL/SQL is the procedural extension of and used in Oracle Database and provides powerful capabilities for database programming and management.

In this article, we will learn about How to Show or List Databases by understanding the various methods with the help of examples and so on.

PL/SQL Show/List Databases

Listing all schemas in an Oracle Database can be a common requirement for database administrators and developers. PL/SQL doesn't have a direct command for this task but by querying system views, we can achieve this goal. Below is the method which helps us to Show or List the Databases:

Syntax

DECLARE
  v_schema_name VARCHAR2(100);                                      -- Declare a variable to store the schema name
BEGIN
  FOR user_rec IN (SELECT username FROM all_users) LOOP      -- Iterate over the result set of usernames from all_users view
    v_schema_name := user_rec.username; -- Assign the username to the variable
    DBMS_OUTPUT.PUT_LINE('Schema: ' || v_schema_name);              -- Output the schema name
  END LOOP;
END;
/

Explanation:

• DECLARE keyword is used to the declare variables and other PL/SQL constructs.
• v_schema_name is a variable declared to store schema name and retrieved from query result.
• BEGIN and END keywords are mark the beginning and end of the PL/SQL block.
• FOR user_rec IN (SELECT username FROM all_users) LOOP is a loop that constructs the iterates over the result set returned by query i.e. SELECT username FROM all_users. Each iteration will be retrieves a username and store it in variable i.e. v_schema_name.
• DBMS_OUTPUT . PUT_LINE ('Schema: ' || v_schema_name); line will be output the schema name using DBMS_OUTPUT . PUT_LINE procedure. The " || " operator is used to concatenation.
• " / "symbol is used indicate the end of the PL/SQL block and it will required to be execute the PL/SQL code in the SQL *Plus or SQL Developer.

Method 1: Using the ALL_USERS View

The ALL_USERS view provides a list of all schemas or users accessible in the database. This view is particularly useful for database administrators who need a quick list of available schemas.

Example Script to List Schemas Using ALL_USERS

DECLARE
  v_schema_name VARCHAR2(100);
BEGIN
  FOR user_rec IN (SELECT username FROM all_users) LOOP
    v_schema_name := user_rec.username;
    DBMS_OUTPUT.PUT_LINE('Schema: ' || v_schema_name);
  END LOOP;
END;
/

Output:

Schema: SYS
Schema: AUDSYS
Schema: SYSTEM
Schema: SYSBACKUP
Schema: SYSDG
Schema: SYSKM
Schema: SYSRAC
Schema: OUTLN
Schema: XS$NULL
Schema: GSMADMIN_INTERNAL
Schema: GSMUSER
Schema: GSMROOTUSER
Schema: DIP
Schema: REMOTE_SCHEDULER_AGENT
Schema: DBSFWUSER
Schema: ORACLE_OCM
Schema: SYS$UMF
Schema: DBSNMP
Schema: APPQOSSYS
Schema: GSMCATUSER
Schema: GGSYS
Schema: XDB
Schema: ANONYMOUS
Schema: WMSYS
Schema: MDDATA
Schema: OJVMSYS
Schema: CTXSYS
Schema: ORDSYS
Schema: ORDDATA
Schema: ORDPLUGINS
Schema: SI_INFORMTN_SCHEMA
Schema: MDSYS
Schema: OLAPSYS
Schema: DVSYS
Schema: LBACSYS
Schema: DVF
Schema: HR
Schema: JAGAN
Schema: QWERTY

PL/SQL procedure successfully completed.

The above output shows the names of the schemas available in Oracle Database instance.

Note: The ALL_USERS view shows only those schemas accessible to the user running the query.

Method 2: Using the DBA_USERS View for Enhanced Information

The DBA_USERS view provides more detailed information on each schema, including the account status (e.g., LOCKED, OPEN). This view is ideal for administrators who need both the schema name and account status for comprehensive management.

Example Script to List Schemas with Account Status Using DBA_USERS

DECLARE
  v_username VARCHAR2(100);
  v_account_status VARCHAR2(100);
BEGIN
  FOR user_rec IN (SELECT username, account_status FROM dba_users) LOOP
    v_username := user_rec.username;
    v_account_status := user_rec.account_status;
    DBMS_OUTPUT.PUT_LINE('Schema: ' || v_username || ', Status: ' || v_account_status);
  END LOOP;
END;
/

Output:

Schema: GSMADMIN_INTERNAL, Status: LOCKED
Schema: MDSYS, Status: LOCKED
Schema: OLAPSYS, Status: LOCKED
Schema: XDB, Status: LOCKED
Schema: WMSYS, Status: LOCKED
Schema: GSMCATUSER, Status: LOCKED
Schema: MDDATA, Status: LOCKED
Schema: SYSBACKUP, Status: LOCKED
Schema: REMOTE_SCHEDULER_AGENT, Status: LOCKED
Schema: QWERTY, Status: OPEN
Schema: GSMUSER, Status: LOCKED
Schema: SYSRAC, Status: LOCKED
Schema: GSMROOTUSER, Status: LOCKED
Schema: SI_INFORMTN_SCHEMA, Status: LOCKED
Schema: AUDSYS, Status: LOCKED
Schema: DIP, Status: LOCKED
Schema: ORDPLUGINS, Status: LOCKED
Schema: SYSKM, Status: LOCKED
Schema: ORDDATA, Status: LOCKED
Schema: ORACLE_OCM, Status: LOCKED
Schema: SYSDG, Status: LOCKED
Schema: ORDSYS, Status: LOCKED
Schema: JAGAN, Status: OPEN

PL/SQL procedure successfully completed.

Explanation:

• By the querying DBA_USERS view, you will get a more comprehensive view of all the user accounts along with the current status.
• The above output shows the schema name and account status to the console.

Note: Accessing DBA_USERS may require administrative privileges since it includes more comprehensive details.

Conclusion

Overall , using the PL/SQL to interact with the schemas in Oracle Database provides the powerful means to manage and manipulate the database objects within the different logical containers. While PL/SQL doesn't have offer to direct command to list all databases, it allowed to us query system views like ALL_USERS to retrieve the information about the schemas. By PL/SQL procedural capabilities, we can iterate the result set returned by these queries and process each schema name as needed.