How can i identify SQL statements that used up space in temp segment?
Im getting the error "ORA-1652: unable to extend temp segment". So i need to identify what causing the problem. I have done "alter system set events '1652 trace name errorstack level 1' , but ORACLE8 doesn't support this option. Any suggestions? Thanks!
oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
join tables v$sort_usage v$seesion v$sql to find out who is using the sort space.
Actually, the error occurs every 1am so i dont know what the usrs are doing in the DB. I have checked all the crons but i dont see any SQL statements. I have tried the alter system set events but oracle8 doesnt support this option. Hope you can help me.
Look at your alert file. If the tablespace is an permenant tablespace then the index creation space requirement is the issue at the tablespace where index is getting created. If the temp segment name is there in the alert then also the probable cause is the temp tablespace space issue due to index creation.
Increase both of them with auto extend and maxsize and see tomorrow with last ddl time whether any indexes created. Or simple ask your users if accessible, whether they received any errors. If you have helpdesk for your complany ask them whether any user errors are reported at the time the error occured.