Oracle Tablespace High Water Mark (HWM) Query

-

🔍 Purpose:

  • To calculate the High Water Mark (HWM) in GB for each data file.

  • The HWM is the highest block ever used by an object in the tablespace.

🧾 Query:

            SELECT 
                 a.tablespace_name,
                 a.file_name,
                ((b.maximum + c.blocks - 1) * d.db_block_size) / 1024 / 1024 / 1024 AS highwater_in_GB
            FROM 
                dba_data_files a,
                (SELECT file_id, MAX(block_id) AS maximum
                FROM dba_extents
     GROUP BY file_id) b,
                dba_extents c,
                    (SELECT value AS db_block_size
                     FROM v$parameter
                     WHERE name = 'db_block_size') d
      WHERE 
                a.file_id = b.file_id
                AND c.file_id = b.file_id
                AND c.block_id = b.maximum
      ORDER BY 
                a.tablespace_name, 
                a.file_name;

📌 Use Case:

  • Identify data files with significant free space beyond HWM for resizing or reclaiming space.

  • Useful before performing operations like shrink, transport, or reorganization.

Comments

Post a Comment

Popular posts from this blog

Managing Oracle Data Files – Resizing and Adding

-
In Oracle databases, proper management of data files is essential to ensure performance, availability, and scalability. This guide explains how to view , resize , and add data files using SQL*Plus, SQL Developer, TOAD, and command-line tools. 🔍 When to Resize Data Files? Resize a data file when available free space is less than 500MB . Use caution: only resize files that are not already at their maximum size (typically 32GB for most configurations). Always connect using a privileged user like SYS or SYSTEM . 🔧 Monitor via Alert Log You should always monitor changes in the alert log :           # Connect as Oracle OS user                su - oracle           # Monitor the alert log in real-time                tail -900f /u01/.../trace/alert_<SID>.log 📘 Using SQL*Plus or SQL Developer View Existing Data Files    ...
Read more

Oracle Database Basic Consepts (Start / Stop / Check Database Status)

-
🟢 Starting the Oracle Database                SQL> STARTUP; This command initiates the Oracle instance, mounts the database, and opens it for use. For environments using pluggable databases (PDBs), you may need to open them separately:                SQL>  ALTER PLUGGABLE DATABASE ALL OPEN; 🔴 Shutting Down the Oracle Database                SQL> SHUTDOWN IMMEDIATE; This command performs a clean shutdown, rolling back uncommitted transactions and disconnecting users. For emergency situations where immediate shutdown is required, you can use:                SQL> SHUTDOWN ABORT; 🔍 Checking Database Status 1. Verify Oracle Processes: On Unix/Linux:                ps -ef | grep pmon If the pmon process is running, the database instance is act...
Read more

Kubernetes Administration – Essential Commands for IFS Cloud

-
This guide covers practical Kubernetes (K8s) administration tasks including managing namespaces , pods , deployments , PVCs , and PVs . Ideal for teams working with IFS Cloud deployments on Kubernetes . 📁 Namespace Management 🔥 Delete a Namespace                kubectl delete namespace hypdev --force 🧱 Pod Operations ❌ Delete or Recreate a Pod                kubectl delete pod <pod-name> --grace-period=0 --force -n <namespace> Example:                kubectl delete pod ifsapp-reporting-86b484b7cc-zws87 --grace-period=0 --force -n hypprd 🔍 Describe a Pod                kubectl describe pod <pod-name> -n <namespace> Examples:                kubectl describe pod ifsapp-proxy-xxxxxxxx-8k4ph -n hypprd          ...
Read more