temp tablespace questions....

I have a 9.0.1.2.0 instance that has a temporary tablespace (duh...). The tablespace was created as follows:

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/dev_tools/oracle/product/oradata/PRODDB/sys3/temp01.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"; Last week I someone ran dbms_stats.gather_schema_stats on a user with gobbs of tables (not a lot of data though). I ran for a while (hour) and exteneded the tablespace to 450M. No big deal....

The problem came the next day when I tried to shut down. It wouldn't.  I could shutdown abort but not immediate. I finally figured out that it was probably the TEMP tablespace. I created another temp tablespace and dropped the original one. It took an hour and a half to drop the tablespace and switched 100M redo logs 4 times while it was dropping.

What would be in a temporary tablespace that would require writting to disk a day after it was done being used. I thought the stuff in the temporary tablespace was only temporary sort/order data used during executions and not required when the executions where finished. What was in the temporary tablespace that required 400+ meg of redo data?

Thanks for any help.

Carl
cmercer_at_vibrant-1.com Received on Mon Mar 04 2002 - 07:48:26 CST