Re: Problem with tablespace file size limit
Date: Fri, 21 Aug 2009 06:51:24 -0700 (PDT)
Message-ID: <4970fb24-44bd-4d69-8a94-4583c8bf30f2_at_k19g2000yqn.googlegroups.com>
On Aug 20, 9:18 am, Noons <wizofo..._at_yahoo.com.au> wrote:
> jstuglik wrote,on my timestamp of 20/08/2009 10:52 PM:
>
> >> The other option is to delete all rows, then just move the table to another
> >> tablespace at a convenient time: deleted space is not moved (except in very
> >> unusual cases, like half-filled blocks) and you need to rebuild indexes and
> >> recalc stats after. But it does not invalidate views or other dependents. Have
> >> a try.
>
> >> Not an easy task or decision. It all is very dependent on what sizes, hardware
> >> capacity and so on you're dealing with.
> >> I don't envy the work ahead of you...
>
> > Thanks a lot for all your help. I'll have to get deep into this
> > problem to make it right it seems:-)
>
> Apologies: forgot to say what I meant by "just move the table":
> Use "ALTER TABLE [tname] MOVE TABLESPACE [tsname]". Again, look it up: some
> subtle variations. Good luck.
If you move the table remember that you need to rebuild all the indexes associated with the tables.
How long it takes to move 4G of data depends on your hardware. It can be anywhere from 3 minutes to half an hour. The index rebuilds will probably take longer than moving the table.
I see nothing wrong with having separate tablespaces for large tables. It would potentially allow you to size each tablespace about the same so your backup and recovery time for any tablespace would be about the same. I also see nothing wrong with having one single tablespace with two or three 16G - 32G data files where everything is stored. That is the database has one user tablespace.
It is just a matter of how you intend to manage your database. If you use RAC you may want multiple tablespaces to use in relation to instance partitioning the applicaiton. You may not. The decision is yours to make based on the application, user load, the resouces available to handle the load, and future growth plans.
I do not see that there is one right way to organize your tablespaces. I believe in fitting the design to the environemnt.
IMHO -- Mark D Powell -- Received on Fri Aug 21 2009 - 08:51:24 CDT