Datafile resize
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: Datafile resize

  1. #1
    Join Date
    Jan 2007
    Posts
    231

    Datafile resize

    Hi all,

    In my ProductionDB i have truncated table which contails 4.7GB.The table present in ISHDATA tablespace with datafiles
    '/mnt/db/oracle/oradata/ISPRO/ishdata01.dbf' upto 10 files are there in Ishdata tablespace.

    After truncating i tried to resize all the 10 datafiles, to show free space at OS level.(MD wants to see at OS level.)

    So i tried as ..
    SQL> alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata01.dbf' resize 1900m;
    alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata01.dbf' resize 1900m
    *
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value
    ...

    SQL> alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata10.dbf' resize 4000m;
    alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata10.dbf' resize 4000m
    *
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value

    spaces at datafiles reduces at different level ie., for one datafile 250MB released another 1800MB released.

    So,please help me on this regard.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you have other tables which are at the end of the file so the datafiles cant be resized

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Query DBA_EXTENTS to find the segments at the high end of the data files.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jan 2007
    Posts
    231
    So..,If i want to rezise i have to drop entire tablespace and recreate with new size.So i have to take backup of that tablespace(ishdata).

    ok., In no way i can play in production.

    Thanks to all.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by ams-jamali
    So..,If i want to rezise i have to drop entire tablespace and recreate with new size.
    Nope.
    You have to move offending objects to a different place, resize tablespace, move objects back.
    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.

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    PAVB, isn't it easyer, after the objects have been moved, just to drop the empty tablespace?
    OK, say you do not have to drop it, but generally yes, that's the easyest way

  7. #7
    Join Date
    Jan 2007
    Posts
    231
    Resizing tablespace... can't follow.

    I can try as

    alter tablespace [tbs] coalesce;

    then i can resize datafile (am i right...)

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Yes, I belive that is what PAVB means

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Yes, then resize datafile. Sorry, I'd a senior moment.
    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.

  10. #10
    Join Date
    Jan 2007
    Posts
    231
    It doesn't help me to release even 1GB.

    I found free space using dba_data_files & dba_extents it shows only 154MB.
    So instead of resizing i left that..,

    Thanks to all.

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