I have 2 datafiles for the user table space which got filled.
Then i created anew 1 with auto extend on so that i dont have to add newer 1es every day.
("cause i get quite heavy data dumps every day to be loaded)
Now the problem is that from the day I made this auto extend on,the "TEMP" table space in the database seems to be 100% full all the time
I this some kinda problem?
If yes pls could any1 tell me how do get through with this
Thanx a lot in advance
06-27-2001, 04:00 AM
just have a look at the segments in your TEMP tablespace, if owner is SYS, then these are temporary segments, and it's a perfectly normal behaviour (Oracle fills the tablespace, and then just reuses unused segments when needed), if not then you should alter your tablespace and make it temporary (alter tablespace TEMP temporary) to prevent people from creating objects in this tablespace.
I do not understand why this happened just after you added your datafile, maybe you just didn't see it before ... well anyway, you should free unused space from your temp tablespace by using the now famous workaround :
alter tablespace TEMP default storage (pctincrease 1);
alter tablespace TEMP default storage (pctincrease 0);
alter tablespace TEMP coalesce;
06-27-2001, 04:01 AM
I don't see any relationship between autoextend and temporary tablespace. Anyway what does the log file tells ? and what does v$filestat report ?
06-27-2001, 04:03 AM
Not the used % in temp is 0%..
now Is this a problem
(sorry for asking such stupid questions cause I am really very new to databases)
thanx a lot
06-27-2001, 04:15 AM
no, it's normal, your temp tablespace is empty, then someones makes a big query which manipulates data, your TEMP tablespace will then have segments created until it is filled (100%), and then Oracle will manage unused segments (because of course all segments are not used simultaneously and some were used a while ago), and your tablespace should then stay 100% filled ... this is the normal behaviour of Oracle 8, do not panic ;)))