OraFAQ Forum: Server Administration » full temp tablespace at startup

Home » RDBMS Server » Server Administration » full temp tablespace at startup (Oracle 9i Windows 2000 Server SP4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

full temp tablespace at startup [message #293971]

Tue, 15 January 2008 16:44

agemaia
Messages: 11
Registered: January 2008

Junior Member

Hi,

my temp tablespace appears full at database startup. I know it is full because the collecting statistics process can not be executed since temp tablespace can not grow (autoextend off). I had to add another temp datafile to get the statistics but first one continues full after each restart.

Is this any signal of malfunction?

Thanks for all.

Greetings.

Report message to a moderator

Re: full temp tablespace at startup [message #293973 is a reply to message #293971]

Tue, 15 January 2008 16:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I am curious at the SQL & returned results which led you to conclude that you have "full temp tablespace at startup".

Report message to a moderator

Re: full temp tablespace at startup [message #293978 is a reply to message #293973]

Tue, 15 January 2008 17:32

agemaia
Messages: 11
Registered: January 2008

Junior Member

I got this log when I tried to launch statistics collect

00:01:56 SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('IDEA',null,FALSE,'FOR ALL COLUMNS SIZE 1',null,'DEFAULT',TRUE);
BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('IDEA',null,FALSE,'FOR ALL COLUMNS SIZE 1',null,'DEFAULT',TRUE); END;

*
ERROR en l�nea 1:
ORA-01652: no se ha podido ampliar el segmento temporal con 128 en el
tablespace TEMP
ORA-06512: en "SYS.DBMS_STATS", l�nea 9136
ORA-06512: en "SYS.DBMS_STATS", l�nea 9616
ORA-06512: en "SYS.DBMS_STATS", l�nea 9800
ORA-06512: en "SYS.DBMS_STATS", l�nea 9854
ORA-06512: en "SYS.DBMS_STATS", l�nea 9831
ORA-06512: en l�nea 1

I thought (also with Toad temp level at 100%, temp tablespace 1.5 GB) temp tablespace was full so I create new datafile.

Is it possible dbms_stats.gather_schema_stats would need more than 1.5 GB to finish its process?

thanks.

Report message to a moderator

Re: full temp tablespace at startup [message #293980 is a reply to message #293971]

Tue, 15 January 2008 17:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is it possible dbms_stats.gather_schema_stats would need more than 1.5 GB to finish its process?
Yes

SQL> select round(sum(bytes)/(1024*1024)) from dba_temp_files;

ROUND(SUM(BYTES)/(1024*1024))
-----------------------------
                        32767

Disk space is cheap so use it where necessary.

Report message to a moderator