DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: How to claim unused space in datafile

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    Potomac, Maryland
    Posts
    85

    Post How to claim unused space in datafile

    Hello,
    I have a tablespace (TS_01) that consists of 5 data files. The tablespace is housing 200+ tables but 99% of the space is used by 1 table (Table_A) storing temporary documents that get deleted the following year. I have just deleted 90% of the rows in Table_A for data older than 2005 with should free up at least 10GB of space. (the export file was 10GB for this table before the row deletion, it's 47MB now). I have moved table_A to a new tablespace TS_02 and rebuilt the indexes but the space it used on TS_01 has still not been freed up and all 5 datafiles are still 95% full. How do I reclaim the unused space ?
    Thank you

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    How did you move the table?

    Thanx,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Nov 2000
    Location
    Potomac, Maryland
    Posts
    85
    Alter table Table_A move tablespace TS_02;

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    That should have released the segments that was used by the table_A on ts_01. What is your DB & OS environments and versions?

    BTW, check for any locks being held on that table, that was prior to the move? if so, upon releasing that lock or the session would release the space for you.
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Nov 2000
    Location
    Potomac, Maryland
    Posts
    85
    That's what I thought it would do too... unless Enterprise Manager is not displaying the correct information.
    os : Win 2003 Server
    db : Oracle 9.2.0.6

  6. #6
    Join Date
    Nov 2000
    Location
    Potomac, Maryland
    Posts
    85
    no current lock on this or any other user table. It's a dev server and I'm the only one connected to it.

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    What does your tablespace mapping on the OEM show? Can you see the table in the new tablespace, from the sqlplus query?

    Thanx,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  8. #8
    Join Date
    Nov 2000
    Location
    Potomac, Maryland
    Posts
    85
    OEM shows the correct ts name and also shows the correct number of rows once analyzed.

  9. #9
    Join Date
    Nov 2005
    Location
    Indianapolis
    Posts
    24

    Check the data dictionary

    What does Oracle say is still being stored in TS_01?

    i.e.:

    Code:
    select segment_name, bytes
    from dba_segments
    where tablespace_name = 'TS_01'
    order by bytes

  10. #10
    Join Date
    Nov 2000
    Location
    Potomac, Maryland
    Posts
    85
    no, oracle says that it's in TS_02, the correct size is also being reported in OEM or SQL statement. I don't think the issue is with the table not releasing the space. I have dropped the table but the space has still not been freed up. I can move other tables and space have been freed for those but not for table_a, and like I said table_a was the largest and used up 99% of all available space.

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