Table not giving up space problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Table not giving up space problem

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    Table not giving up space problem

    9.2.0.4

    After computing statistics, a table has

    19,538 "BLOCKS"
    0 "NUM_ROWS"
    70 "EMPTY_BLOCKS"

    I tried "alter tablespace aq_1 coalesce", reanalyze .. no love.

    I've got 1 3gb datafile and I want to shrink it, there's no data in it.

    Any advice would be greatly apprciated.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Alter Table Move ..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Since you want to shrink the size of your datafile, I would:
    1. alter table move to a new ts
    2. resize your existing ts
    3. alter table move back to original ts
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Aug 2001
    Posts
    267
    If you do not want to move table. We can try EXP/IMP that table
    Raghu

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Excellent suggestions. Thank you all.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    created tablespace aq_2 to move tables from aq_1
    alter table el_webqueue move tablespace aq_2

    ORA-00054: resource busy and acquire with NOWAIT specified

    I know it's busy and I can't do it online (like an index)

    What's keeping it from deallocating that space?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Aug 2001
    Posts
    267
    Do you tried .. It may be easy ..Create new table in existing TS or new TS

    Create table new_tab as select * from old_tab nologging;

    Drop old table
    Rename new table to old
    Raghu

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Originally posted by KenEwald
    created tablespace aq_2 to move tables from aq_1
    alter table el_webqueue move tablespace aq_2

    ORA-00054: resource busy and acquire with NOWAIT specified

    I know it's busy and I can't do it online (like an index)

    What's keeping it from deallocating that space?
    Does a process have the table locked?
    Is there a large uncomitted dml transaction on that table?

    As you know either commiting or rolling back that session should unlock the table.

  9. #9
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    It's an advanced queue - queue table.

    Messages are being consumed constantly from that queue table.

    Looks like I need to stop the subscribers to the queue then make the move.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

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