How to Check PDB Size in Oracle 19c
Oracle 19c introduces a new architecture called Pluggable Databases (PDBs). PDBs are essentially individual databases that exist within a larger container database (CDB). This architecture offers flexibility and scalability, but it also raises questions about managing and monitoring PDB sizes.
Knowing the size of your PDBs is crucial for various reasons:
- Capacity Planning: Understanding the space occupied by your PDBs helps you plan for future storage requirements.
- Performance Optimization: Large PDBs can impact query performance. Identifying oversized PDBs allows you to optimize their structure or potentially move data to other databases.
- Troubleshooting: Monitoring PDB sizes helps identify potential space issues that may lead to database errors.
How to Check PDB Size in Oracle 19c
There are several ways to check the size of a PDB in Oracle 19c:
1. Using SQL*Plus
This method allows you to directly query the database using SQL commands.
-
Connect to your CDB as SYSDBA:
sqlplus / as sysdba -
Execute the following SQL query:
SELECT d.name, d.con_id, round(s.bytes/1024/1024) "Size (MB)" FROM dba_data_files d, dba_segments s WHERE d.file_id = s.file_id AND d.con_id = (select con_id from v$database);
This query retrieves the name, container ID, and size (in MB) of each data file associated with the PDB. You can filter the results by specifying the name of the desired PDB.
2. Using Enterprise Manager (OEM)
Oracle Enterprise Manager offers a user-friendly interface for managing your database.
- Access Enterprise Manager.
- Navigate to the Database section.
- Select the desired CDB.
- Under the Pluggable Databases tab, you'll find a list of your PDBs.
- Click on the PDB name to view its details.
- The Size information is displayed on the Overview tab.
3. Using the dbms_space_admin Package
Oracle provides the dbms_space_admin package for advanced space management tasks.
-
Connect to your CDB as SYSDBA:
sqlplus / as sysdba -
Execute the following PL/SQL block:
DECLARE l_pdb_name VARCHAR2(30) := 'YOUR_PDB_NAME'; BEGIN DBMS_SPACE_ADMIN.REPORT_PDB_USAGE(pdb_name => l_pdb_name, output_type => 'TABLE'); END; /
This block generates a report that includes details about the space usage of your PDB.
Tips for Understanding PDB Size
- Data Files: Remember that the size of a PDB is primarily determined by the size of its data files.
- Shared Data: Data files are shared between the CDB and its PDBs. This means that the size of a PDB includes its own unique data files as well as its share of the CDB's shared data files.
- Temporary Tablespace: The temporary tablespace used by a PDB is also included in its size.
- System Tablespace: The system tablespace is not typically considered part of a PDB's size, as it is shared by the CDB and all its PDBs.
Example
Suppose you have a PDB named "SALES". You want to check its size.
Using SQL*Plus:
SELECT d.name,
d.con_id,
round(s.bytes/1024/1024) "Size (MB)"
FROM dba_data_files d, dba_segments s
WHERE d.file_id = s.file_id
AND d.con_id = (select con_id from v$database)
AND d.name LIKE '%SALES%';
This query will return the size of all data files associated with the "SALES" PDB.
Using dbms_space_admin Package:
DECLARE
l_pdb_name VARCHAR2(30) := 'SALES';
BEGIN
DBMS_SPACE_ADMIN.REPORT_PDB_USAGE(pdb_name => l_pdb_name, output_type => 'TABLE');
END;
/
This block will generate a report with details about the space usage of the "SALES" PDB.
Conclusion
Knowing the size of your PDBs is crucial for effective database management in Oracle 19c. You can easily check this information using SQL*Plus, Enterprise Manager, or the dbms_space_admin package. By monitoring PDB sizes, you can optimize storage, improve performance, and proactively address potential space issues.