How to find SQL that causes ORA-1652

In the alert file, we may see this error message:

Wed Aug 20 17:16:37 2008
ORA-1652: unable to extend temp segment by 128 in tablespace DBA_TEMP

To solve this problem, we first want to cause the problem of SQL, there are several possible methods:

1, set events

Alter system set events ' 1652 trace name errorstack level 1 ';

This approach has certain limitations:

1 It cannot get 1652 error messages that have occurred and can only generate a trace file when 1652 errors occur later;

2) with events, it is not clear what the bad impact on the database.

2, Query V$sql view:

such as SELECT * from V$sql ORDER by direct_writes/executions Desc;

The limitations of this approach are:

1 because it is difficult to know the SQL execution time in the V$sql view, it is difficult to confirm that the SQL caused the error

2 The SQL that caused the problem is very likely to have been out of age

3, generate error occurs AWR, Statspack report, from the report of SQL ordered by reads section to find SQL

This approach is more unreliable because:

1 SQL ordered by reads read and write is not necessarily a temporary table space

2 Awr/statspack reports are sorted according to the total amount of physical reading, and are not present in these reports if the number of SQL executions that causes the problem is low.

4, query awr related views

For 10G, this method is the most feasible and accurate.

SELECT DISTINCT TO_CHAR(SUBSTR(b.sql_text,1,4000))
FROM sys.WRH$_SQLTEXT b
WHERE b.sql_id IN
(SELECT sql_id
FROM
(SELECT a.sql_id
FROM sys.WRH$_SQLSTAT a
WHERE a.parsing_schema_name NOT IN ('SYS')
AND a.executions_total >0
AND a.direct_writes_total >0
AND a.SNAP_ID IN
(SELECT SNAP_ID
FROM sys.WRM$_SNAPSHOT
WHERE to_date('2008:08:20 17:20:08','yyyy:mm:dd hh24:mi:ss') BETWEEN begin_interval_time AND end_interval_time
)
ORDER BY a.direct_writes_total/ a.executions_total DESC
)
WHERE rownum<=10
);

Basically, the first sentence in the result, as long as it is not a statement like Insert/*+ append * *, is likely to be SQL that causes ORA-1652.

If it is 9i, you can use Statspack to find the desired results from the Statspack view with similar SQL.