Questions on moving big tables

From: Linda Lee <goforticket_at_yahoo.com>
Date: 14 May 2002 15:12:32 -0700
Message-ID: <f901fb80.0205141412.7e4cc7db@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.

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?

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(?);

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?

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

Thank you very much for your help. Received on Tue May 14 2002 - 17:12:32 CDT