Table Resizing in Oracle 9i

[skdas]

Hi,

1. I have table with 3 extents.

2. I give the following command.

ALTER TABLE (table name) MOVE TABLESPACE (Tablespace Name) STORAGE(INITIAL 3M NEXT 64K);
My data is having 2 MB.

3. So it should now take 1 extent only. But still I see 3 extents.

4. I tested it works in Oracle 8i. I get 1 extent after I give this command.

5. My extent management is AUTO, set in Oracle 9i.

What is the way to put all the data in the table in one extent..?

[raghud]

Import/export may work in your case .. You are telling it worked on 8i but not on 9i . Is all the parameters are same in both cases.

[skdas]

I need to try with Export and Import.

Also I do not see any entry in the field NEXT from DBA_SEGMENTS. Most probably, the extent management is AUTO, that is why it is not there.

[svk]

Check is the tablespace where you are moving the table is 'locally managed'. If yes, that should explain it.

[skdas]

You are right. It is Locally Manged Tablespace.

Should we leave it as it is , I mean what ever number of extents comes it should be OK ..?

If number of extents are more , is it efficient...?

[hrishy]

Hi

The number of extents for a table does not matter..unless your table is in some 1000 or so extents..

regards
Hrishy

[shailendra]

What is the size of your extents?

If it is locally managed then oracle will not consider the initial, next parameter so just fine out the size of the extents may it has created three extents of having same size to adjust your 3mb data

shailendra

[pando]

well auto is locally managed, if you are using locally managed there is no sense whatsoever setting initial and next anymore

[skdas]

well auto is locally managed, if you are using locally managed there is no sense whatsoever setting initial and next anymore

You are right Pando. Initial and next is meaning less in locally managed tablespace. But after moving to Locally Managed Tablespace, I see some tables are having more than 100 extents. The export was taken in Compress=Y, and it was imported to Locally Managed Tablespace.

Now my question is

1. Should we try to put it into one extent or not.?

2. If we need to put in one extent what is the way..?

3. Before using Locally Managed Tablespace, we were trying to put in one extent by exporting compress = Y and importing. Is that concept we should forget when we move to Locally Mnaged Tablespace..?

[marist89]

Originally posted by skdas
1. Should we try to put it into one extent or not.?

Take this notion out of your head and stomp on it. Burn it. Shred it. Obliterate it. Forget about it. The number of extents your data lives in is irrelevent (for the most part). I know that's the way you've done it in the past, but in the past you were wrong.

If you are using AUTO allocate with LMT you should never worry about how many extents are allocated to your table ever again. Ever.

(Yes ever).