How to resize table initial size?

[ayeleta]

Can someone help me please?
I have a table with lots of extents. How can I resize the table's initial size so all data will be held in the first extent?
Thanks

[jovery]

There are a couple of ways you can do this:

You can use CREATE TABLE AS ... SELECT .....

or you can export the data, recreate the table, import the data

Why do you want to move the data to a single extent?

Regards

[TomazZ]

Originally posted by jovery
or you can export the data, recreate the table, import the data

or you can export the data with compress=y, drop the table and import the data

[ggnanaraj]

Originally posted by ayeleta
I have a table with lots of extents.

Check the size of this table in MB. If it is > 5M and < 160M put it on a tablespace with 5M extents.

On the otherhand, if it is > 160M put it on a tbs with 160M extents.

HTH.

[vishkr]

Hi,

There are many ways as mentioned by two of our mates. But the question is Why ? Yes, Export and import does it and it has been a traditional way and this tool was made to this job . It is for a different purpose.

You may want to look at storage parameters for the tables and sort them if you fear you may hit max extents. Its best to go for LMT with uniform extents and forget about the number of extents . It does affect the performance. Even if all the data is in one extent its still all over the disk and not contiguous .

HTH
Vish.

[Mr.Hanky]

Originally posted by TomazZ
or you can export the data with compress=y, drop the table and import the data

I think you are missing one important step here.


MH

[TomazZ]

Originally posted by Mr.Hanky
I think you are missing one important step here.

Shoot!

[jovery]

Originally posted by TomazZ
or you can export the data with compress=y, drop the table and import the data

It's not really adviable to use the compress=y unless you are sure that the data content of the table is using all of the space currently allocated.

If you have a table which has allocated 200Mb of extents yet the data contained within the table is only 10Mb (due to deletes, etc) then the compress=y will build a table with a single 200Mb extent rather than a single 10Mb extent.

IMHO it is better to establish the storage requirement first.

REgards

[TomazZ]

Yes, I see, good point!

[ArlynSmith]

Simply use an 'alter table move...' and specify a new storage clause, including a new intial extent. You do not have to move the table to a new tablespace - if you do not specify a tablespace it will be moved to a new extent in the same tablespace. This will preserve your constraints, indexes, and permissions.