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.

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:

1. Using Querying the ALL_USERS View
2. Using Querying the DBA_USERS View

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:

1. DECLARE keyword is used to the declare variables and other PL/SQL constructs.
2. v_schema_name is a variable declared to store schema name and retrieved from query result.
3. BEGIN and END keywords are mark the beginning and end of the PL/SQL block.
4. 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.
5. 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.
6. ” / “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.

Let’s Create a PL/SQL script to list all the schemas in an Oracle Database. The script should query the `ALL_USERS` view to fetch the schema names and iterate through the result set and output each schema name.

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.

The DBA_USERS view provides a more comprehensive view of all user accounts in the database, along with their statuses. Let’s Develop a PL/SQL script to retrieve and display the names and account statuses of all user schemas in an Oracle Database. The script should query the DBA_USERS view, iterate through the results and output each schema name along with its corresponding account status.

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: To execute the PL/SQL code, make sure that we need to appropriate privileges typically granted by administrator of the database.

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.