4G of TEMP.dbf ??

[SKMORA]

I have oracle8i running on the NT box. I am the sole person on
using that oracle database in that box. Somehow temp tablespace of
box is using 4G of space. I don't remember I have allocated that much
of space for that table space.

Oracle8i on that box is running in noarchivelog mode. I am writing a perl
application to use that database, somehow my perl application screwed up
with Operating System. So I had to shutdown the machine, when I reboot
the machine it was taking very long time to mount the database. I know
that temp tablespace did not have that 4G of space before, after a while
when database is completely mounted, I saw that temp
tablespace is occupying that much space.

By the way I have other tablespace which has 3G of space for my application.


Can somebody give me idea to tune 4G of temp tablespace?

Any idea and suggestion are welcome.
Thanks in advance.

SKM

[sln81]

what do yoy mean by tuning 4G of temp tablespace? Check if the datafile has the Autoextend option set to on.

[JDoyle]

Check this:

[url]http://63.236.73.14/forums/showthread.php?threadid=6771[/url]

[sreddy]

I wonder you have AUTOEXTEND option enabled and contents of the tablespace are PERMANENT rather TEMPORARY. Check and see.

It should release the temp space when you bounce the database.

[SKMORA]

I had autoextend option on that temp table space and had a temporary
storage type. I disabled autoextend and
bounced the database by using NT services. It did turned the database
quickly but database is still having 4G of temp tablespace. When
I tried to resize it, it is saying file contains used data beyond the
requested resize value error message.

Any further thoughts and suggestions.

SKM

[deggzy]

Drop the tablespace and recreate it with better storage parameters.

[Chris]

SKM,

I'm not sure whether this will work, but I think I can remember
having a similar problem with a temporary tablespace filling up. Even though the contents were temporary, the extents were not being released. To get Oracle to release them try the following:-

alter tablespace temp default storage (pctincrease 0)
/

(In my case, my pct increase was set to 0 anyway, so all this does is to wake Oracle up and release the temp extents) So, just use a parameter like pctincrease and use the alter tablespace statement to set it to what it is already set as. ie, don't change anything, just issue the command and see what happens.

cheers

chris.

[SKMORA]

Chris,
I did but nothing significant change happend and even though I did restarted the db instances after that.
I am thinking of removing or droping temp tablespace and recreating it. But when I looked that table space dependencies I saw sys, system and Oracle Management Users are using that table space. I wondered removing that table space may effect running oracle database or I might
screwed up with oracle database.

Any thoughts.

SKM

[sreddy]

Make sure pctincrease set to ZERO. I hit the same problem couple of minutes ago. If you don't specify at the time of creating tablespace it takes defaults.

[steviep9]

That may take a while to do depending on the number of
extents that have been created.


one of the best ways I use is to start up the db with a very small buffer cache (say 100 db block buffers) and make sure you only have 1 DBWR configured. Obviously, the db won't be usable in that type of configuration, but it should help
smon clean things up faster. So, if you have a window of opportunity to try this out, this might be the best thing to do.

There are also 'unsupported' ways of doing this probably such as updating the appropriate data dictionary tables, etc, but this would have to be done with extreme caution of course.