I'm trying to compress the data into 1 extent.
I exported my table, disable all constraints....
truncated the table...
type - alter table TABLE_A deallocate unused;
I then ran:
select t.owner, t.TABLE_NAME, e.TABLESPACE_NAME, count(*) as cnt
from dba_tables t, dba_extents e
where t.table_NAME = e.SEGMENT_NAME
and e.segment_type = 'TABLE'
group by t.owner, t.TABLE_NAME, e.TABLESPACE_NAME
having count(*) > 2
order by t.owner, e.TABLESPACE_NAME, cnt
It still shows me having like 40 extents... how do I get oracle or dba_extents to update itself?
1.) export the table, definitions and data with compress=y
2.)drop the original table.
3.)import the table
Is there a way to do it without dropping the table?
I compressed the export (which is the default)... and i then altered the table and set the NEXT to like 10M, from 100K.
im not sure how to get around it.
if you already truncated it, is there a reason not to drop it?
try alter table X deallocate unused KEEP 1K. I think this will drop all of the extents except the first one. THen you can make your next extent nice and big. Is 2 extents close enough?
If you are in 8i use the alter table move table it does the same job as you want .You need not export nor import.
only thing u need to rebuild the indexes..