Home » RDBMS Server » Server Administration » full temp tablespace at startup (Oracle 9i Windows 2000 Server SP4)
full temp tablespace at startup [message #293971] Tue, 15 January 2008 16:44 Go to next message
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.
Re: full temp tablespace at startup [message #293973 is a reply to message #293971] Tue, 15 January 2008 16:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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".
Re: full temp tablespace at startup [message #293978 is a reply to message #293973] Tue, 15 January 2008 17:32 Go to previous messageGo to next message
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.
Re: full temp tablespace at startup [message #293980 is a reply to message #293971] Tue, 15 January 2008 17:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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.
Re: full temp tablespace at startup [message #293981 is a reply to message #293980] Tue, 15 January 2008 17:55 Go to previous messageGo to next message
agemaia
Messages: 11
Registered: January 2008
Junior Member
ok.

checked that oracle manager shows me the right temp tablespace usage, not so toad.

thanks a lot.
Re: full temp tablespace at startup [message #294075 is a reply to message #293971] Wed, 16 January 2008 06:14 Go to previous message
varu123
Messages: 754
Registered: October 2007
Senior Member
Use Toad only for select statements.
For the rest sqlplus is the best
Previous Topic: Recommendations for maintaining Prod support DB
Next Topic: Stop Running on DB after X number of Minutes
Goto Forum:
  


Current Time: Sun Dec 11 00:36:17 CST 2016

Total time taken to generate the page: 0.09704 seconds