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:
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:
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:
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:
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.
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.