-
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
-
what do yoy mean by tuning 4G of temp tablespace? Check if the datafile has the Autoextend option set to on.
-
-
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.
-
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
-
Drop the tablespace and recreate it with better storage parameters.
-
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.
-
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
-
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.
-
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.
-
Hi,
You are saying that you can not resize the datafile. Check what extents are there in this file (tablespace) using dba_extents view. If at all there are any, you should have segments owned by sys only. If you do not find any segments then force the coalesce on this tablespace. This will make it contiguos. Then try resizing this datafile.
Baliga
-
I think as soon as the db is restarted you should be able to resize the temp tablespace's datafile. If its still not possible, you might try a work around, like, create a new temporary tablespace with proper settings, alter the users sys, system, ... to have this new tablespace as temporary tablespace, then drop your old one and recreate and alter the users again.
-
Thanks for all the reponses that I got for this thread, each one is really
helping me to understand this temp tablespace.
I did some research and find out that dropping and creating the temp tablespace
will not effect the database. So I did that.
I did create temp tablespace and assigned 50 MB of space to it, temporary type
with Pctincrease = 0, datafile name as temp0.dbf. It took me about
one hour to drop the old 4G of temp tablespace. Creating that temp
tablespace was quick. Everything seems to ok. I checked the tablespace and datafile size from Oracle
enterprize manager. It looks the same as I was creating for.
But when I looked at size of the temp tablespace datafile
at the physical location under the oracle datafile folder.
I was amazed to see that I am having new temp0.dbf file about 3.8G
space along with the old one of 4G of temp tablespace. I deleted the 4G of temp
tablespace to free the space.
But I could not understand why this new temp file has that much of space?
And what causing to have that much of space for this temp file?
And why I am not seeing that space occupying in DBA Studio?
Any further thoughts.
SKM
