4G of TEMP.dbf ??

[mbaliga]

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

[sln81]

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.

[SKMORA]

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