This weekend I am going to replace the undo and temp tablespaces on our Oracle database for Baan. They've both grown too large and the disk is running out of space (we had a nasty cartesian join in a Baan query a couple of months ago that swelled up the temp tablespace).

I have instructions from Oracle for doing this, so I'm not looking for guidance on how to do this.

What I am wondering is if there are going to be any issues with Baan. Is there anything I need to do there if I replace the temp table and undo tablespaces?

My plan is to reboot the server to make sure Baan is out of memory, then make the changes to Oracle before restarting Baan.

Thanks,
Rob

What do you mean by replacing the temp space? are you going to create a new temporary tablespace "temp2" or something? you could just add a new tempfile on another disk...

anyway, baan doesn't care - if you rename it just make sure each of your
baan users's corresponding oracle user has a "temporary tablespace" set
to the new tablespace and also if you're setting temporary tablespace in $BSE/lib/defaults/db_resource that you fix that as well.

For undo tablespace that's all handled by oracle so baan doesn't see it at all.

Dave

Although its different than what you asked.

Still I feel, instead of deleting/recreating datafiles, you can go for re-sizing it. Just you need to restart database ones.

Let me know, if you have any queries.

regards,

-Suhas

Also a bit offtopic to your question, but something to consider:

Your problem was origionated most likely because you configured your tablespaces to automatically grow as needed. Especially for the temp and the undo tablespace this is not a good configuration. Every user can issue a large select, which will cause these to grow out of bounds. In my personal opinion it is better to manage this growth manually. or to say it differnt, once you found a suitable size for these tablespaces, they should not grow at all.

Hi,

Thanks for the info and the tips.

As to Suhas' suggestion to resize the datafiles: according to a lot of my research, this does not work (I think it depends on your version of Oracle--we are at 10.2). Oracle sugests David's solution, to change the tempfile itself (see Metalink Doc ID: 274283.1).

As to Markus' suggestion: I agree that I would like to find some optimal size for temp and undo. But Oracle seems to be making it easier and easier to replace the tempfiles and undo tablespace (see metalink doc 268870.1 for their suggestions for the undo tablespace). They seem to be suggesting that this should be part of the regular maintenance of the database.

Rob

Rob,

You are the best judge for deciding it!

Although, I dont think, why its not possible in 10.2.

Please refer - http://pbarut.blogspot.com/2007/01/ora-03297-on-empty-datafile.html

regards,

-Suhas

I don't really know enough about Oracle to explain, but as I understand it, you can resize regular data files (as the blogger in the link seems to be doing), but not temp files.

If I understand this correctly, if a regular data file is 1G in size, but only has 100M of data, you can resize it to remclaim the unallocated 900M of data. In a temp file, all the space is usually allocated (expecially if you have run some poorly written query that caused it to fill up), so their is nothing for the resize command to reclaim.

Whatever the reason, the commands to replace these files worked nicely on our test server.

Resizing needs to be done carefully: increasing it is easy but in a shrink, those 100MB in a 1GB tablespace are usually not continuous and thus you can only shrink upto the high water mark. The next procedure does a correct resizing: http://www.oracle.com/technology/oramag/code/tips2003/060103.html
And if I was you I would tune a bit the procedure to show me the resize command instead of running it thru an EXECUTE IMMEDIATE STMT; )

In 10g you can do a shrink of segments online (google for "oracle shrink tablespace 10g" for information)