Replymessage unavailable
To troubleshoot the issue of the undo tablespace growing in AWS RDS Oracle due to a long-running DELETE operation on ausys.aud$unified, follow these steps:
1. Identify the Running Session:
- Check for active sessions running the DELETE query:
SELECT s.sid, s.serial#, s.username, s.program, s.machine, s.status, s.event, s.sql_id
FROM v$session s
WHERE s.sql_id IN (
SELECT sql_id FROM v$sql WHERE sql_text LIKE '%DELETE FROM ausys.aud$unified%'
);
2. Check the SQL Execution:
- Find out how long the SQL is running:
SELECT sql_id, elapsed_time, cpu_time, executions, sql_fulltext
FROM v$sql
WHERE sql_text LIKE '%DELETE FROM ausys.aud$unified%';
3. Find Blocking or Long-Running Transactions:
- Identify long-running transactions holding undo space:
SELECT s.sid, s.serial#, t.used_urec, t.used_ublk, t.start_time, t.status
FROM v$transaction t, v$session s
WHERE t.ses_addr = s.saddr;
### 4. Check the Undo Tablespace Usage:
- Monitor undo tablespace usage to confirm growth:
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb,
autoextensible, maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1';
5. Kill the Session (If Safe to Do So):
- If the session is identified and safe to terminate:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
*Use caution to avoid affecting production processes.*
6. Check for Scheduled Jobs:
- Review if there is a DBMS job or scheduler running this DELETE:
SELECT job_name, enabled, last_start_date
FROM dba_scheduler_jobs
WHERE job_action LIKE '%ausys.aud$unified%';
7. Check for External Connections:
- Use v$session to identify the client IP:
SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.status, s.process
FROM v$session s
WHERE s.program LIKE '%sqlplus%' OR s.program LIKE '%JDBC%';
8. Monitor Network and Security Logs:
- If the session is from an external IP, check AWS CloudWatch or VPC Flow logs for connections and activity.