| Delete Statements useTemp Tablespace? [message #512023] |
Thu, 16 June 2011 10:54  |
bpeasey
Messages: 46
Registered: March 2005
|
Member |
|
|
Hi,
Does anyone know why large delete statements, with no where clauses, could be using up temp tablespace segments? I thought temp tablespace was just for sort operations,joins, etc.
We have a weekly job that inserts rows into a staging table, deletes, with commit, the a few minutes later and repeats the process. Each delete is using some of the temp tablespace. Eventually we run out of space (ora-01652). That's another thing. I would have thought the space would be released for the next process, but it's now.
SNAP_TIME USERNAME SESSION_ADDR SESSION_NUM MB SQLHASH SQLADDR SQL_TEXT
2011-06-15; 19:10 SCHEMA_USER 0700000209286B00 49680 980 3165065004 070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
2011-06-15; 19:15 SCHEMA_USER 070000020F2A7670 10601 275 3165065004 070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
2011-06-15; 19:20 SCHEMA_USER 070000020925FE38 19368 4748 3165065004 070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
2011-06-15; 19:25 SCHEMA_USER 070000020925FE38 19368 15783 3165065004 070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
We will probably switch to truncate statements, but this situation really puzzles me. I've tried view the docs but don't see any of delete statements using temp tablespace.
Thanks
BP
|
|
|
|
|
|
| Re: Delete Statements useTemp Tablespace? [message #512027 is a reply to message #512024] |
Thu, 16 June 2011 11:01  |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Also if you want to clear a table completely and don't feel the need to ever rollback, you should never consider anything other than truncate.
How are you measuring the temp usage?
Are you sure it's the delete?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
