How to Get PL/SQL Database Size?

In PL/SQL Databases, sometimes you need to know the database size to manage its performance and plan for the future. Retrieving database size information helps us make informed decisions about storage optimization strategies.

Understanding storage requirements aids in optimizing storage and planning resource allocation effectively. There are different ways to do this.

In this article, we will learn about How to Get PL/SQL Database Size, by understanding various methods along with the examples.

How to Get PL/SQL Database Size

To get the size of a PL/SQL database, we use SQL queries on system views or tables that contain information about database storage. The primary approach uses querying tables/views that provide details about data files or segments.

Syntax:

SELECT SUM(bytes) / (1024 * 1024) AS database_size_mb
FROM dba_segments;

Explanation:

• SUM(bytes): Calculates the total size of segments in bytes.
• / (1024 * 1024): Converts the total size from bytes to megabytes.

Examples of How to get PL/SQL Database Size

Following are the examples to get PL/SQL Database size, Table size Schema size, and Tablespace size. Each example is shown with a query, output, and explanation of the query.

Note: Ensure appropriate privileges to access system views/tables for retrieving database size information.

Example 1: Retrieving Database Size

To get the database size, we can use the below mentioned Query:

SELECT SUM(bytes) / (1024 * 1024) AS database_size_mb
FROM dba_segments;

Output:

Retrieving Database Size in PL/SQL

Explanation: This query calculates the total size of all segments in the database and presents it in megabytes, which is essential for understanding the overall database size.

Example 2: Retrieving Table Size

To get the size of specific tables within the database.

Query:

 SELECT SUM(bytes) / (1024 * 1024) AS table_size_mb
 FROM dba_segments
 WHERE segment_name = 'STUDENT';

Output:

Retrieving Table Size in PL/SQL

Explanation:

• user_segments: This system view contains information about the storage allocated to segments (tables, indexes, etc.) owned by the current user.
• SUM(bytes): Calculates the total size of the segments in bytes.
• / (1024 * 1024): Converts the total size from bytes to megabytes.
• WHERE segment_name = ‘STUDENT’: Filters the segments to only include those belonging to the ‘STUDENT’ table.

This query will provide you with the size of the “STUDENT” table in megabytes.

Example 3: Retrieving Schema Size

To get the schema size within a database grouped by owner.

Query:

SELECT OWNER, SUM(bytes) / (1024 * 1024) AS schema_size_mb
FROM dba_segments
GROUP BY OWNER;

Output:

Retrieving Schema size in PL/SQL

Explanation:

• OWNER: Represents the schema name in the dba_segments view.
• SUM(bytes) / (1024 * 1024): Calculates the total size of segments owned by each schema in megabytes.
• GROUP BY OWNER: Groups the segments based on their schema owner.

This query will provide you with the size of each schema in the database in megabytes.

Example 4: Retrieving Tablespace Size

Tablespace in Oracle Database are logical storage units to store all the data. Logical storage units help users locate specific data and help in the retrieval of data.

If you need to analyze the size of each tablespace within the PL/SQL database to understand space allocation across tablespaces.

To get the Tablespace size we can use the below query.

Query:

SELECT tablespace_name, SUM(bytes) / (1024 * 1024) AS tablespace_size_mb
FROM dba_segments
GROUP BY tablespace_name;

This query retrieves the size of each tablespace within the database by grouping segments based on their tablespace. It calculates the total size occupied by each tablespace in megabytes.

Output:

Retrieving Tablespace Size in PL/SQL

Explanation:

• tablespace_name: This column in the dba_segments view represents the name of the tablespace.
• SUM(bytes): Calculates the total size of segments in bytes for each tablespace.
• GROUP BY tablespace_name: Groups the segments based on their tablespace, allowing us to analyze the size of each tablespace separately.

This example provides information about the space usage of different tablespaces within the PL/SQL database.

Conclusion

Retrieving the size of a PL/SQL database is important for effective database management and optimization. By using SQL queries on system views/tables, the administrators can get the size of database, tables, schemas available, which helps in decision-making for storage allocation and usage patterns and for managing its performance.