get rid of extents on table and index

[RaymonG]

Hi there. I have the following problem. I have a table and an index with too many extents due to a delete problem in this table, which is cured now. Now I only need to get rid of the extents. I was thinking to export the table and index, delete the table and index and import the table and index again. There are no constraints on this table whatsoever. Can someone tell me if this is the correct way, maybe add some parameters with it? Thnx!

[jovery]

You can use

.. import/export
.. create table as select ...

All will work fine.

Regards

[RaymonG]

So, in other words, using the create table as select, I should do this twice? because first I need to get the data to a temptable, drop the old table and create the new table from the temptable, since it should have the same name? After that I can create the index and all will be fine?

[jovery]

Depending on your version you should be able to

CREATE TABLE newtable AS SELECT * oldtable;
DROP oldtable;
ALTER TABLE newtable RENAME TO oldtable;
Recreate you indexes.

HTH

[marist89]

If you are on 8.1+, you can use ALTER TABLE MOVE for the table and ALTER INDEX REBUILD for the index. For example:

system@nt817.us>  create table xyz (x number(10), y date, z varchar2(20)) storage (minextents 20);

Table created.

Elapsed: 00:00:00.80
system@nt817.us> select segment_name, count(*) from dba_extents
  2  where segment_name = 'XYZ'
  3  group by segment_name
  4  /

SEGMENT_NAME                     COUNT(*)
------------------------------ ----------
XYZ                                    22

Elapsed: 00:00:00.00
system@nt817.us> alter table xyz move storage (minextents 1);

Table altered.

Elapsed: 00:00:00.21
system@nt817.us> select segment_name, count(*) from dba_extents
  2  where segment_name = 'XYZ'
  3  group by segment_name
  4  /

SEGMENT_NAME                     COUNT(*)
------------------------------ ----------
XYZ                                     3

Elapsed: 00:00:00.00
system@nt817.us> create unique index xyz_uk1 on xyz(x) storage (minextents 20);

Index created.

Elapsed: 00:00:00.01
system@nt817.us>  select segment_name, count(*) from dba_extents
  2   where segment_name = 'XYZ_UK1'
  3*  group by segment_name
system@nt817.us> /

SEGMENT_NAME                     COUNT(*)
------------------------------ ----------
XYZ_UK1                                20

Elapsed: 00:00:00.00
system@nt817.us> alter index xyz_uk1 rebuild storage (minextents 1);

Index altered.

Elapsed: 00:00:00.40
system@nt817.us>  select segment_name, count(*) from dba_extents
  2   where segment_name = 'XYZ_UK1'
  3   group by segment_name
  4  /

SEGMENT_NAME                     COUNT(*)
------------------------------ ----------
XYZ_UK1                                 1

Elapsed: 00:00:00.10

[LKBrwn_DBA]

You can also try the following:

ALTER TABLE MYTABLE DEALLOCATE UNUSED KEEP
32K;

and

ALTER INDEX MYINDEX DEALLOCATE UNUSED KEEP
32K;

NOTE: the 32K forces deallocate unused space at the end of the table.