Refreshing view fills up TEMP forever --

Hello!

I am dealing with a challenging phenomenum. We have put a job for automatic execution into OEM, which would automatically refresh some views daily.

But after the job is done, TEMP won't be empty. Instead, it grows and will soon be full (last size of files in TEMP tablespace was 16+8 GB), so further executions of the job will fail:

ORA-12012: error on auto execute of job 65
ORA-12008: error in snapshot refresh path
ORA-00028: your session has been killed
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
ORA-06512: at "SYS.DBMS_IREFRESH", line 577
ORA-06512: at "SYS.DBMS_REFRESH", line 211
ORA-06512: at line 1

Sometimes, errors appear saying that the TEMP space could not be extended.

The views are quite large in size, but it seems that something is plain wrong here. We are restarting the DB (Oracle 8.1.7, Solaris 2.7) in order to free space in TEMP, but when that happens, it takes about 25 minutes to free the TEMP tablespace. This happens slowly as one can see the used % changing. During this time, the E3500 runs Oracle's smon at about 25% usage, and fuser reveals that smon is handling the datafile in the TEMP tablespace.

So why is it that the tablespace is kept full? And is there a way to empty it without having to restart the DB?

Tanks a lot!

Rick  

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net