Examples of How to Show a Database in PL/SQL
Let us say that we have an Oracle database with various numbers of Pluggable Databases (PDBs), users, and database objects. We’ll do the examples using sample queries to fetch information about those databases, users, and objects.
1. Retrieving information about Pluggable Databases (PDBs)
-- Example: Retrieving information about Pluggable Databases (PDBs)
SELECT name, open_mode FROM v$pdbs;
Output:
NAME |
OPEN_MODE |
---|---|
PDB1 |
READ WRITE |
PDB2 |
READ ONLY |
PDB3 |
READ WRITE |
Explanation: The output displays the names and open modes of the Pluggable Databases (PDBs) in the Oracle database. For each PDB, the name of the PDB is listed along with its open mode, indicating whether it is in read-write or read-only mode.
2. Retrieving Information About Database Users
-- Example: Retrieving information about database users
SELECT username, account_status FROM dba_users;
Output:
USERNAME |
ACCOUNT_STATUS |
---|---|
SYS |
OPEN |
SYSTEM |
OPEN |
HR |
OPEN |
SCOTT |
OPEN |
Explanation: The output lists the usernames and account statuses of the database users. Each row represents a user, with the username displayed in the first column and the account status (such as “OPEN“) displayed in the second column.
3. Retrieving Information About all objects in the Database
-- Example: Retrieving information about all objects in the database
SELECT object_name, object_type FROM all_objects;
Output:
OBJECT_NAME |
OBJECT_TYPE |
---|---|
EMPLOYEES |
TABLE |
DEPARTMENTS |
TABLE |
JOBS |
TABLE |
Explanation: The output provides details about database objects accessible to the current user. Each row represents an object, with the object name displayed in the first column and the object type (e.g., “TABLE“) displayed in the second column. This information helps users understand the types of objects present in the database
How to Show Database in PL/SQL
PL/SQL is the Procedural Language/Structured Query Language and serves as a procedural language built-in extension to SQL language, which allows seamless integration of procedural constructs with SQL. One of the most common functions of a DBMS is the retrieval of information about databases which is fundamental for administrators and programmers alike.
In this manual, we will explain how to perform database display via PL/SQL, including methods for retrieving information on PDBs, users, and objects.