This discussion is archived
8 Replies Latest reply: Nov 2, 2011 2:48 AM by 846231 RSS

recover space

846231 Newbie
Currently Being Moderated
Hi all,

Oracle 10g

Our PROD /u01/oracle is now 100% full.

I got this table-space status from our "USER01" tablespace
USED=5G
FREE=55G

Select * from DBA_FREE_SPACE

TABLESPACE_NAME     FILE_ID     BLOCK_ID     BLOCKS     RELATIVE_FNO
USER01     6     2031625     2031616     6
USER01     7     553     2031072     7
USER01     7     2031625     1900512     7
USER01     6     813737     1217888     6
USER01     6     4063241     131040     6
Can I resize this USER01 tablespace to freeup this 50G freespace and give it to the OS freespace?


Thanks a lot
  • 1. Re: recover space
    Aman.... Oracle ACE
    Currently Being Moderated
    KinsaKaUy? wrote:
    Hi all,

    Oracle 10g

    Our PROD /u01/oracle is now 100% full.

    I got this table-space status from our "USER01" tablespace
    USED=5G
    FREE=55G
    
    TABLESPACE_NAME     FILE_ID     BLOCK_ID     BLOCKS     RELATIVE_FNO
    USER01     6     2031625     2031616     6
    USER01     7     553     2031072     7
    USER01     7     2031625     1900512     7
    USER01     6     813737     1217888     6
    USER01     6     4063241     131040     6
    Can I resize this USER01 tablespace to freeup this 50G freespace and give it to the OS freespace?
    Can you would get an answer of yes . But will it happen , for that the answer would be it depends . If you have the datafile's HWM touching a certain point, you can't resize it beyond that point. So better would be that you export the contents of this tablespace to a new one, drop the existing tablespace and rename the new one to the old name.

    HTH
    Aman....
  • 2. Re: recover space
    UweHesse Expert
    Currently Being Moderated
    Before you go through the painfull procedure to move everything into another tablespace, I would recommend that you check out with this good old script by Tom Kyte (maxshrink.sql), whether & to what size you can resize your datafiles:
    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
    /
    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
  • 3. Re: recover space
    Aman.... Oracle ACE
    Currently Being Moderated
    Aha very nice Uwe, thanks :-) .

    Regards
    Aman....
  • 4. Re: recover space
    UweHesse Expert
    Currently Being Moderated
    I'm sure you knew that :-)
  • 5. Re: recover space
    846231 Newbie
    Currently Being Moderated
    Thanks all,

    I run the script above, and i got this output:
    alter database datafile '//system01.dbf'  resize 354m;
    alter database datafile '//USER01.dbf'   resize 6358m;
    alter database datafile '/c/tools01.dbf'   resize 60m;
    alter database datafile '/c/SYSAUX.dbf'    resize 207m;
    alter database datafile '//USER02.dbf'   resize 5m;
    alter database datafile '//UNDOTBS2a.dbf' resize16m;
    alter database datafile '//rbs01.dbf'     resize 1m;
    But this is too good to be true??? the tablespace of issue (USER01) can be resize to 6358M :( it seems all the freespaces are contiguous when they are not.


    Thanks.
  • 6. Re: recover space
    UweHesse Expert
    Currently Being Moderated
    Why not just do the command and find out? If it can't be done, it gives you an error message immediately.
    It will do no harm.

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
  • 7. Re: recover space
    846231 Newbie
    Currently Being Moderated
    I bet 100 USD it will not ;) since 6358M is the actual used space in USER01.
    alter database datafile '//rbs01.dbf' resize 1m;
    Just curious, can I create an rbs.dbf using 1m size?
  • 8. Re: recover space
    UweHesse Expert
    Currently Being Moderated
    see answer above

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points