DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: resetting # of extents

Hybrid View

  1. #1

    Angry

    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?

    thanks.


  2. #2
    Join Date
    Oct 2000
    Posts
    80
    1.) export the table, definitions and data with compress=y
    2.)drop the original table.
    3.)import the table
    John Doyle

  3. #3
    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.

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    if you already truncated it, is there a reason not to drop it?

  5. #5
    Join Date
    Nov 2000
    Posts
    344
    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?

  6. #6
    Join Date
    Sep 2000
    Posts
    384
    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..
    Radhakrishnan.M

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width