Datafile resize - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 31

Thread: Datafile resize

  1. #11
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Didn't you truncate a 4.7 Gig table?

    In the affirmative case... did you use "drop storage" option on truncate statement?
    Last edited by PAVB; 05-25-2007 at 02:12 PM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  2. #12
    Join Date
    Jan 2007
    Posts
    231
    No., I gave only
    Truncate table tablename;

    not used (drop storage) clause.

    So what shall i do now to use that space;

  3. #13
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    drop storage option is default option for truncate...

    did you aborted truncate after a while 'cause of truncate was not finishing?

    Try deallocate unused space on target table...

    alter table schema_name.table_name deallocate

    You might want to check complete syntax.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #14
    Join Date
    Jan 2007
    Posts
    231
    No, i had not aborted in the middle.
    It just completes its job within few seconds.

    I think there is no need of using deallocate option after Truncate.

    But you guys know better than me..,

    So.., what else i can do further.

  5. #15
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Look at dba_extents howmany extents the truncated table occupies and howmuch space

  6. #16
    Join Date
    Jan 2007
    Posts
    231
    it nearly occupies 87 extents and only one extent has 7MB
    and all the other has 1MB and some 0.628MB

  7. #17
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    That means the table occupies only around 100MB.

  8. #18
    Join Date
    Jan 2007
    Posts
    231
    no bore..,

    before truncating that table i checked the size using segment then i checked that result in toad too.
    Same 4.7GB it shows,So i confirmed and then proceed to trucate.
    now that table occupies
    1886051 (present rec-count)rec within two weeks.
    may be b'cos of this it might have shown this result.

  9. #19
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    "I found free space using dba_data_files & dba_extents it shows only 154MB.
    So instead of resizing i left that..,"

    Maybe u want to look at DBA_FREE_SPACE

  10. #20
    Join Date
    Jan 2007
    Posts
    231
    bore..,
    by using dba_data_files & dba_extents we can find how much one can resize the datafile(by giving block size).


    So for resizing i joined these two tables and found the size(that can be resized).

    dba_free_space gives you overall Freespace in that tablespace.
    but from that one cannot resize the datafile because it may contain data.
    Bore you know better than me.

    if u need i will post that query how to find space in datafile that can be resized.without finding that how much can be resized in each datafile.If any tries to resize it shows error stating that

    file contains used data beyond requested resize value..
    Last edited by ams-jamali; 05-30-2007 at 06:57 AM.

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