I would like to reclaim some free space from our tablespaces. I can see that UNDOTBS1 and USERS for example have 30GB and 10GB free.

these are the tablespaces:

| tablespace    | used mb   | free mb   | total mb  | pct. free     |
|------------   |---------  |---------  |---------- |-----------    |
| SYSAUX        | 3268      | 152       | 3420      | 4             |
| UNDOTBS1      | 991       | 31777     | 32768     | 97            |
| USERS         | 186866    | 10766     | 197632    | 5             |
| SYSTEM        | 3360      | 30        | 3390      | 1             |
| INDEXES       | 32920     | 2920      | 35840     | 8             |

I could see on ASKTOM, and other questions that I need to:


and then:


But I would like to know how much can I reclaim. when i try to redize UNDOTBS1 for example, it says I cannot trim space that is used. but it has more than 30GB free.

On SQL Server we can simply " shrink file" adn it shrinks the free space.

  • given the freespace you have in sys and sysaux you should be adding more datafiles not resizing them. What is the problem you are trying to solve? – kevinsky Jun 10 '19 at 14:18
  • I have some tablespaces with a lot of free space. Company here is a pain in the a$$ about addind disks, so for now I would like do resize these tableSpaces to reclaim some space until we can add another disk. – Racer SQL Jun 10 '19 at 14:58

I know I am late for the party, but this script was gold for me at the time I found it:

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(+) order by savings desc

It will tell you smallest and current size of each datafile and it will tell you how much storage you may gain by shrinking a datafile(s combined).

Once you find the smallest possible size of a datafile, you may simply shrink it with:

ALTER DATABASE DATAFILE 'Datafile_name_bla_bla' RESIZE 20M;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.