Troubleshooting

Oracle Database Is Running Out of Space

🔍 Symptoms

The Oracle database or an APM_DATAMART tablespace is approaching its storage limit, datafiles are growing unexpectedly, or GermainUX can no longer insert monitoring data.

A common cause is that expired partitions have not been removed according to the configured GermainUX retention policy.

✅ 1. Confirm the Cause

Before deleting anything, determine whether storage is being consumed by:

Do not assume that old partitions are the cause until database utilization has been reviewed.

🔎 2. Identify Partitioned Tables

List the partitioned tables available to the current account:

SELECT
    OWNER,
    TABLE_NAME,
    PARTITIONING_TYPE,
    SUBPARTITIONING_TYPE,
    PARTITION_COUNT
FROM ALL_PART_TABLES
ORDER BY OWNER, TABLE_NAME;

Limit the query to the GermainUX datamart schema when appropriate:

SELECT
    OWNER,
    TABLE_NAME,
    PARTITIONING_TYPE,
    PARTITION_COUNT
FROM ALL_PART_TABLES
WHERE OWNER = 'APM_DATAMART'
ORDER BY TABLE_NAME;

📁 3. List the Partitions

To inspect the partitions of a specific table:

SELECT
    TABLE_OWNER,
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_POSITION,
    HIGH_VALUE,
    TABLESPACE_NAME
FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER = 'APM_DATAMART'
  AND TABLE_NAME = '<TARGET_TABLE_NAME>'
ORDER BY PARTITION_POSITION;

Replace <TARGET_TABLE_NAME> with the uppercase Oracle table name.

Review each partition’s boundary and confirm which dates it contains before considering removal.

📝 4. Verify the Retention Policy

Before removing a partition, confirm:

Never remove a partition based only on its name.

🛠️ 5. Drop Expired Partitions

Only a qualified Oracle DBA should remove partitions.

Partition deletion is destructive and may be difficult or impossible to reverse without a valid backup. Use the Oracle-supported ALTER TABLE ... DROP PARTITION procedure appropriate for the installed Oracle version and the table’s partitioning strategy.

Do not copy a generic drop command into production without verifying:

Where supported and appropriate, include the required global-index maintenance option to avoid leaving indexes unusable.

✅ 6. Validate After Removal

After removing an expired partition:

1. Confirm that the intended partition was removed.

2. Verify the status of affected indexes.

3. Confirm that GermainUX continues to insert data.

4. Validate representative dashboards and analyses.

5. Check materialized-view refresh jobs.

6. Review tablespace utilization.

7. Confirm that no required data was removed.

8. Verify that the automated retention process will handle future partitions.

🛡️ Prevent Recurrence

To prevent future storage exhaustion: