How to find the SQL that causes the ORA-1652

WedAug2017: 16: 372008ORA-1652: warn to solve this problem, we first need to cause the problem of SQL, there may be several ways: 1. Set eventsaltersystemsetevents1652tracenameerrorstacklevel1; such a side

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 need to cause this problem of SQL, there are several possible methods: 1. set events alter system set events '2017 trace name errorstack level 1 '.

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 need to first cause the problem of SQL, there are several possible methods:

1. Set events

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

This method has some limitations:

1) it cannot obtain the error message of 1652 that has occurred. It can only generate a trace file when a 1652 error occurs in the future;

2) with events, it is unclear what will affect the database.

2. query the V $ SQL View:

For example, select * from v $ SQL order by direct_writes/executions desc;

The limitations of this method are:

1) It is difficult to know the SQL Execution time in the V $ SQL view, and it is difficult to confirm that the specific SQL statement causes errors.

2) the SQL statement that causes the problem is probably out of age.

3. Generate the awr and statspack reports when an error occurs. Find the SQL statements from the SQL ordered by Reads section in the report.

This method is less reliable because:

1) SQL ordered by Reads does not necessarily read and write temporary tablespaces.

2) awr/statspack reports are sorted by the total number of physical reads. If the number of SQL statements that cause the problem is small, they will not appear in these reports.

4. query awr-related views

For 10 Gb, 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
WHERE a. parsing_schema_name not in ('sys ')
AND a.exe cutions_total> 0
AND a. direct_writes_total> 0
AND a. SNAP_ID IN
(SELECT SNAP_ID
FROM sys. WRM $ _ SNAPSHOT
WHERE to_date ('2014: 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.exe cutions_total DESC
)
WHERE rownum <= 10
);

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

If it is 9i, you can use statspack or similar SQL to find the expected results from the statspack view.