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
Printable View
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
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
or you can export the data with compress=y, drop the table and import the dataQuote:
Originally posted by jovery
or you can export the data, recreate the table, import the data
Check the size of this table in MB. If it is > 5M and < 160M put it on a tablespace with 5M extents.Quote:
Originally posted by ayeleta
I have a table with lots of extents.
On the otherhand, if it is > 160M put it on a tbs with 160M extents.
HTH.
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.
I think you are missing one important step here.Quote:
Originally posted by TomazZ
or you can export the data with compress=y, drop the table and import the data
;)
MH
Shoot!Quote:
Originally posted by Mr.Hanky
I think you are missing one important step here.
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.Quote:
Originally posted by TomazZ
or you can export the data with compress=y, drop the table and import the data
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
Yes, I see, good point!
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.
Quote:
Originally posted by jovery
Why do you want to move the data to a single extent?
Isn't 1 extent better for performance reasons?
Can you elaborate on that ?Quote:
Originally posted by ayeleta
Isn't 1 extent better for performance reasons?
Take a look at
http://asktom.oracle.com/pls/ask/f?p...:730289259844,
HTH
I think you will need to recreate the indexes - the rowid's will have changed.Quote:
Originally posted by 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.