Method of Procedure (MOP) for Checking Tablespace Usage in Oracle Database

Overview

This document provides a step-by-step guide to checking tablespace usage in an Oracle database. The procedure ensures that database administrators can monitor tablespace utilization and take proactive measures if the usage exceeds a threshold. The process involves pre-checks, execution of the tablespace usage query, and post-checks to verify database health after the activity.

Prerequisites

Before performing this activity, ensure the following prerequisites are met:

• Accessibility to the database servers through NIAM.
• Sufficient privileges to log in as the oracle user and execute database queries.

Pre-Check

Before proceeding with the tablespace utilization check, perform the following pre-checks to confirm database health.

1. Login to the Config Primary Database Server

• Execute the following command to access the database server:

ssh oracle@xx.xxx.xxx.xx

2. Verify if the Database Instance Process is Running

• Run the following command to check the database process:

ps -ef | grep pmon

3. Check the Listener Status

• Ensure the Oracle listener is running using:

lsnrctl status

Procedure for Checking Tablespace Utilization

To check tablespace utilization in the Oracle database, follow these steps:

1. Login to the Config Primary Database Server

ssh oracle@xx.xxx.xxx.xx

2. Login to the Oracle Database Instance

• Use SQL*Plus to access the database:

sqlplus / as sysdba

3. Run the Tablespace Utilization Query

• Execute the following SQL query to check for tablespaces exceeding 70% usage:

select tablespace_name, used_percent from dba_tablespace_usage_metrics where used_percent > 70;

• This query will return a list of tablespaces where used_percent is greater than 70%.
• If any tablespace exceeds the threshold, consider adding space or performing maintenance to free up storage.

Post-Check

After executing the procedure, ensure the database remains in a healthy state by performing the following checks:

1. Login to the Config Primary Database Server

ssh oracle@xx.xxx.xxx.xx

2. Verify if the Database Instance Process is Running

ps -ef | grep pmon

3. Check the Listener Status

lsnrctl status

 

Following this MOP ensures that database administrators can effectively monitor tablespace usage and take necessary actions to prevent performance issues.

 

Related Ticket

• #4588034