Datafile resize - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 31

Thread: Datafile resize

  1. #21
    Join Date
    Sep 2001
    Posts
    200
    On what version are you? If 10g you can simply do
    alter table XXXXXX shrink space cascade;
    this shrinks, compresses extents, drops High Water Mark. Very handy for online object rebuilds...
    Life is what is happening today while you were planning tomorrow.

  2. #22
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by ams-jamali
    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.
    So... table is alive and growing back to original size.

    Do you expect this table to grow back to the original size?

    If Yes... you don't want to resize any datafile and we can stop this insanity right now

    If No... are you sure you want to resize a datafile to reclaim less than 5 Gig? what's the total size of your database?
    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.

  3. #23
    Join Date
    Jan 2007
    Posts
    231
    * This table actually stores error message,when the user fails to register in our program.

    * I don't expect to grow to its original size.

    * Database size is 90GB(It is now 76% full and doing purging process other tables i had deleted gives me 70Mb,10Mb,.. free space).This was the only table released 4.7Gb space.

    My boss want to see at OS level.So am tring this.,

  4. #24
    Join Date
    Jan 2007
    Posts
    231
    Iam using Oracle9.2 version.

    but i don't think resize datafile changed in 10G release.

  5. #25
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by ams-jamali
    My boss want to see at OS level.So am tring this.,
    Why is it important for your boss? Explain to him that the space has been released and that is the way the DB is supposed to work. That space is now available internally for the DB to re-use.

  6. #26
    Join Date
    Jan 2007
    Posts
    231
    Ixion..,

    I had already told him about that,then showed him in GUI mode too, space that was released comparing with previous report but asked said try to repli @ OS level.
    If there is no other way, i can go and say to him,it can not be replicated at OS level for such a small sizes.

  7. #27
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Does your boss comes from a SQLServer shop or is he just one of these pointy-haired bosses?

    SQLServer allows to setup autoshrink=Yes which actually shrinks datafiles on-the-fly (database size becomes variable) then, reclaimed space can be seen at OS level.

    If this is the case -SQLServer trained boss, tell him "welcome to the wonderful world of Oracle", if not think of it as "serving time", you are winning credits and your next life might be better
    Last edited by PAVB; 06-01-2007 at 09:27 AM.
    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.

  8. #28
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by ams-jamali
    Ixion..,

    I had already told him about that,then showed him in GUI mode too, space that was released comparing with previous report but asked said try to repli @ OS level.
    If there is no other way, i can go and say to him,it can not be replicated at OS level for such a small sizes.
    That is the way Oracle functions. Releasing space back to the OS in general is a bad idea.

    Here is why, When oracle creates a datafile it does so in one fell swoop, claiming pages from the OS as available(unless your using raw). Hopefully these pages are relatively contiguous as to avoid disk fragmentation. So releasing and acquiring disk pages by shrinking and expanding the oracle datafiles leads to disk fragmentation..

  9. #29
    Join Date
    Feb 2003
    Posts
    63

    You could run this to see how much you can reduce df by

    This is from the asktom web site it shows how much you could reduce your files to, again as mentioned by other ppl not always worth it as the db will grow again, maybe something to show your boss in the mean time.

    I did also have a script which shows which extents are at the end a datafile, i will try to dig that out and post that up as well

    Alternatively you could mail me at crichards@unicle.co.uk I can send you the script (if i can find it)

    Cheers

    ----------- maxshrink.sql ----------------------------------

    set verify off
    column file_name format a50 word_wrapped
    column smallest format 999,990 heading "Smallest|Size|Poss."
    column currsize format 999,990 heading "Current|Size"
    column savings format 999,990 heading "Poss.|Savings"
    break on report
    compute sum of savings on report

    column value new_val blksize
    select value from v$parameter where name = 'db_block_size'
    /

    select file_name,
    ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
    ceil( blocks*&&blksize/1024/1024) currsize,
    ceil( blocks*&&blksize/1024/1024) -
    ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
    from dba_data_files a,
    ( select file_id, max(block_id+blocks-1) hwm
    from dba_extents
    group by file_id ) b
    where a.file_id = b.file_id(+)
    /

    column cmd format a75 word_wrapped

    select 'alter database datafile '''||file_name||''' resize ' ||
    ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
    from dba_data_files a,
    ( select file_id, max(block_id+blocks-1) hwm
    from dba_extents
    group by file_id ) b
    where a.file_id = b.file_id(+)
    and ceil( blocks*&&blksize/1024/1024) -
    ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
    /

  10. #30
    Join Date
    Feb 2003
    Posts
    63

    Other script

    select owner, segment_name, segment_type,
    (select file_name from dba_data_files b
    where b.file_id = a.file_id
    and b.relative_fno = b.relative_fno ) file_name
    from (select owner, segment_name, segment_type, file_id, relative_fno, block_id,
    max(block_id) over (partition by file_id, relative_fno) max_block_id
    from dba_extents
    ) a
    where block_id = max_block_id
    /

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