How To Check Pdb Size In Oracle 19c

How to Check PDB Size in Oracle 19c

Oracle 19c introduced significant changes in how Pluggable Databases (PDBs) are managed. Understanding and monitoring the size of your PDBs is crucial for performance and resource planning. This guide will walk you through several methods to efficiently check the size of your PDBs in Oracle 19c.

Using SQL Queries for PDB Size Check

The most straightforward way to determine a PDB's size is by querying the relevant data dictionary views. Here are a few effective approaches:

Method 1: Checking Datafile Sizes Directly

This method provides a granular view, showing the size of each datafile belonging to the PDB. This is useful for identifying potential bottlenecks or large tables/indexes.

SELECT
  df.file_name,
  df.bytes / (1024 * 1024) AS size_mb
FROM
  dba_data_files df
WHERE
  df.file_id IN (SELECT file_id FROM dba_files WHERE tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE CONTAINER = 'pdb_name'));

Remember to replace pdb_name with the actual name of your Pluggable Database. Sum the size_mb column to get the total size of the PDB. This method requires that you're connected as a user with sufficient privileges (e.g., SYS or a user granted the necessary privileges).

Method 2: Using V$DATAFILE

Similar to the previous method, this approach uses the V$DATAFILE view, offering a dynamic view of the datafiles:

SELECT
  SUM(bytes) / (1024 * 1024 * 1024) AS total_size_gb
FROM
  V$DATAFILE
WHERE
  CON_ID = (SELECT con_id FROM v$containers WHERE name = 'pdb_name');

Again, replace pdb_name with your PDB's name. This query provides the total size in gigabytes.

Method 3: Utilizing DBA_TS_QUOTAS (for Quotas)

If you've implemented storage quotas for your PDBs, this query will be helpful:

SELECT
  tablespace_name,
  max_bytes / (1024 * 1024 * 1024) AS max_size_gb
FROM
  dba_ts_quotas
WHERE
  container_name = 'pdb_name';

This shows the maximum allocated storage for each tablespace within the specified PDB. Note that this represents the quota, not necessarily the current usage.

Beyond SQL: Using Enterprise Manager or Other Tools

While SQL queries offer direct control, Oracle Enterprise Manager provides a user-friendly interface for monitoring PDB sizes and other aspects of your database. This graphical tool offers a more intuitive way to visualize and manage your database resources, including PDB size information. Other third-party database management tools also offer similar functionalities.

Optimizing for Performance and Resource Management

Regularly checking your PDB sizes is essential for:

• Capacity Planning: Anticipate future storage needs to avoid performance degradation.
• Performance Tuning: Identify large tables or indexes that may be impacting query performance.
• Resource Allocation: Ensure fair resource distribution among your PDBs.
• Troubleshooting: Quickly identify PDBs consuming excessive storage.

By combining the SQL methods described above with the insights from database management tools, you gain a comprehensive understanding of your PDB sizes, empowering you to manage your Oracle 19c environment effectively. Remember to always back up your database before making any significant changes.