Re: Problem with tablespace file size limit
Date: Thu, 20 Aug 2009 04:24:52 -0700 (PDT)
Message-ID: <445b8f4a-5a31-4854-a4f8-e99a0ae570f2_at_j9g2000vbp.googlegroups.com>
On 20 Sie, 12:53, Noons <wizofo..._at_yahoo.com.au> wrote:
> jstuglik wrote,on my timestamp of 20/08/2009 8:34 PM:
>
> > Thank you very much for your help.
> > It seems strange, I agree, that I don't want to add another datafile
> > but there are some reasons to do that involving supporting of the
> > application this DB is for. I think I will move some tables containing
> > various statistics to another tablespace stored in another file.
> > Those are the troublemakers - they are quite big and will grow
> > constantly in time.
>
> Hang on a tick: you don't want to add another file, but it's OK to add another
> tablespace(s) - with its(their) own file(s)?
>
> I strongly suggest you move away from that train of thought: adding a datafile
> to a tablespace is the accepted and correct way to increase the capacity of a
> tablespace. Use it: it's there, it's standard, it won't change.
>
> Besides if your concern is growing size, then you most definitely need to think
> in terms of adding files, rather than tablespaces: even the dedicated
> tablespaces will eventually need more files.
>
> Unless of course you want to package averything into a self-contained set and
> then later on after install, as things grow, let dbas add files as needed?
> In that case, yes: package the large growth tables into their own tablespaces,
> the dbas will love your package as that is the way they like to isolate and
> contain large growth tables!
Actually I want to package everything into self-contained set as you
said, but
as things grow I will archive them and delete. Those tables which are
a problem
are statistics and I need them in the DB from two years behind so I
will archive
older ones once every 3 months. For now I have those tables packed in
the same
tablespace (and datafile of course) as other data (which grow in time
also but not so fast and they should
be ok with 32GB of space for a long time). If I move the big tables
into the new tablespace
with new datafile it should be enough space in one file to store 2
years behind
of them, and if it's not I will just add another datafile then.
I hope also this will improve performance of the db in data
manipulation in those tables left
in the old tablespace (and datafile).
I will do the CTAS on those tables and then drop the old ones - do you
have any hints on this to make
it as fast as possible? The are 5 tables to move, the biggest one
contains about 50 millions rows and
is about 4GB of size on disk. I'm asking because I'm affraid this
could take a long time and I will have
only few-hour long window to do this.
Received on Thu Aug 20 2009 - 06:24:52 CDT