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.

Showing Databases

We are going to understand databases in PL/SQL using processing the next methods – querying data dictionary views, dynamic SQL implementation, and others. The abovementioned, we will cover the methods particular to the PDBs retrieval data, users, and even objects.

Using Data Dictionary Views

Oracle provides a rich set of data dictionary views containing metadata about database objects. These views are invaluable for retrieving information about databases, tablespaces, users, and objects. Here are some commonly used views:

• DBA_DATA_FILES: It Gives information about data files, such as tablespace, file name, and size.
• DBA_TABLESPACES: It Contains information about tablespaces, like their name, size, and the way of extent management.
• DBA_USERS / ALL_USERS: It Provides information about database users, such as their usernames, roles, and default tablespaces.
• ALL_OBJECTS: It Contains details of database objects accessible to the current user, which include their names, types, and owners.

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

Best Practices

When showing databases in PL/SQL, adhere to best practices to ensure efficiency, security, and maintainability:

• Privilege management: Make certain that the executing user has required privileges for he/she will have access to required database objects.
• Parameterized queries: Parameterization of queries will help in evading vulnerabilities such as SQL injection attacks and also help in code reusability.
• Exception handling: Institute an error handling design to smoothly contain and deal with errors.
• Performance optimization: Frequently index frequently queried columns and SQL queries are ed by optimization to enhance performance

Conclusion

Overall using PL/SQL the database which will provide information on database structure, usage, and performance is essential. Using data dictionary views, system tables, and dynamic SQL queries, users can have access to information about databases, PDBs, users, and objects relevant to their needs. Observing procedures for good habits guarantees effective and safe database maintenance in the PL/SQL environment. Having a well-grasped understanding of these approaches, the users can get around and handle Oracle databases efficiently.