How to shrink user temp datafile

[raygg]

I have a user schema with a temp tablespace with its own datafile that pushes out to 3.4GB while running a long transaction. After the transaction is done it shrinks back to 8k. How do I shrink thge datafile without dropping the user or exporting, dropping, resizning and importing the user schema?

 

[C4IAaron]

I believe you can reclaim space from the datafile by:
alter database datafile 'db01/oracle/cc/data01.dbf' resize 80m;

of course you can't reclaim space that is allocate to another object. But I hope this helps!

Aaron

 

[raygg]

Thanks - that worked - to a point.

I get a message ora-03297 file contains used data beyond requestedd resize value. If I do a rename of the datafile with the new name in a different folder would this actually move the contents of the datafile to a physically different datafile thereby reorganizing it at the same time - sort of like an export/import affect?

 

[karluk]

The rename idea won't work. The renamed file will still have the same internal structure.

The error you are getting is very common. It means that there are Oracle segments occupying the space that you are trying to release. Normally the right approach at this point would be to drop and recreate the objects that are taking up the extra space. I'm not sure if this would work with a temporary tablespace.

One approach that should work is to create a new temporary tablespace with the desired size. Then alter all user ids to have their temp tablespace assigned to the new tablespace. After all current sessions log out, the old temporary tablespace shouldn't be in use. At that point you can drop it.

As always when you make significant structural changes to your db, be sure to do backups before and after the change, so you can recover in case of problems.

 

[raygg]

Thank you.