8 Replies Latest reply: Nov 2, 2011 4:50 AM by UweHesse RSS

    recover space

    846231
      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....
          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
            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....
              Aha very nice Uwe, thanks :-) .

              Regards
              Aman....
              • 4. Re: recover space
                UweHesse
                I'm sure you knew that :-)
                • 5. Re: recover space
                  846231
                  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
                    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
                      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
                        see answer above