Re: Questions on moving big tables

goforticket_at_yahoo.com (Linda Lee) wrote in message news:<f901fb80.0205141412.7e4cc7db_at_posting.google.com>...
> I have a few tables that each have several thousand extents with
> several hundred MB data. I plan to move the big tables into a new and
> separate tablespace.
>
> 1. When I use following code to create the new LMT tablesapce, should
> I use AUTOEXTEND or not? what's the difference? What are the advantage
> and disadvantage in using AUTOEXTEND?

  Better Don't. There are lots of threads that talk about how bad   autoextend is. Do a search, search key being 'LMT HJR'
>
> create tablespace prod2
> datafile 'O:\ORACLE81\OWXE2\ORADATA\ORAJDE\proddtat02.dbf' size
> 1536064K EXTENT management local uniform size 5120K;
>
> 2. When I move big table into the new LMT tablespace, should I have a
> storage clause with it?
>
> alter TABLE bigTable move tablespace prod2 storage(?);

   No need since you normally specify it during tablespace creation time.    It will take that as your default table storage.
>
> 3. The old tablespace that currently has bigTable in it is a
> Dictionary tablspace. If its size is 4508 MB and the size of bigTable
> is 1200 MB, how much disk space can I reduce the old tablespace after
> the move? (1200MB ?) Should I use "alter database datafile ... resize
> 3308M" to reduce the old tablespace size? Or are there other better
> methods?

  Just drop the tablespace if there is only one object. If not, export   all other objects out and drop the tablespace and import back. It depends   on your env.  

>
> 4. Is it a good practice to put big tables into a dedicated
> tablespace?
>

   Yes.. but some people have other preferences.

> Thank you very much for your help.
Received on Tue May 14 2002 - 23:24:55 CDT