We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


How to reduce UNDOTBS01.DBF?

Desperate34 asked
Medium Priority
Last Modified: 2011-08-18

Is there any way to reduce UNDOTBS01.DBF and don't get any problem with the database once it restarted?
I have inserted 18 millions of new records to my table Table01 and UNDOTBS01.DBF has grown more than 3GB! I don't need UNDOTBS01.DBF so large and I need space on my disk C:

C:\oracle\oradata\MyDataBase\TEMP01.DBF        more than 1GB
C:\oracle\oradata\MyDataBase\UNDOTBS01.DBF  more than 3GB

And I have in other disk:

Watch Question

Hi, Is the Table01 table in the UNDOTBS01.DBF  ? If yes then if the table to be big, the data file also become big.

Did you mean you want to move the table01 to F:\oradata\Table01.ora data file ? What is your Oracle version ?

You can drop your undo tablespace.

First check there are no active transactions as follows:-

SELECT a.name, b.status
FROM v$rollname a, v$rollstat b
WHERE a.name IN (SELECT segment_name
                                   FROM dba_segments
                                    WHERE tablespace_name = upper(‘&tablespace’))
AND a.usn = b.usn;

It should return no rows if there are no active transactions.

then just do

drop undo tablespace undotbs;


hi, morphman

I tried as system:
SQL>  drop tablespace UNDOTBS1;

but error: ORA-30013: undo tablespace 'UNDOTBS1' is in use right now
There's no active transactions
what can I do, please?

You can try and shrink the datafile itself:

ALTER DATABASE DATAFILE '/.../your_undo.dbf'
   RESIZE 1000M;

Unlock this solution and get a sample of our free trial.
(No credit card required)

You are probably within your UNDO retention period.

sho parameter UNDO_RETENTION.

Its set in init.ora

You can still apparently drop undo tablespaces within undo period as follows:-

alter system set undo_tablespace='';

then drop undo tablespace as before.

Ora_TechieDatabase Administrator

The following query will give you the minimum possible size (that you can set without disturbing data) alongwith commands to do so:

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

On my System, for example, it gave:


1 row selected.

Elapsed: 00:00:00.50

                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
================================================== ======== ======== ========
E:\ORACLE\ORADATA\STARR\SYSTEM01.DBF                    160      400      240
E:\ORACLE\ORADATA\STARR\RBS01.DBF                     1,178    1,178        0
E:\ORACLE\ORADATA\STARR\USERS01.DBF                      41      136       95
E:\ORACLE\ORADATA\STARR\TOOLS01.DBF                       1       12       11
E:\ORACLE\ORADATA\STARR\SYSTEM02.DBF                     29      100       71
D:\DATAFILES\STARRDATA1.DBF                           2,000    2,000        0
D:\DATAFILES\STARRDATA2.DBF                           1,000    1,000        0
D:\DATAFILES\STARRDATA3.DBF                           1,500    1,500        0
D:\DATAFILES\STARRDATA4.DBF                           1,500    1,500        0
D:\DATAFILES\SATRRDATA5.DBF                           1,000    1,000        0
D:\DATAFILES\STARRINDEXES1.DBF                          901    1,000       99
D:\DATAFILES\STARRINDEXES2.DBF                          887    1,000      113
D:\DATAFILES\STARRINDEXES3.DBF                          858    1,000      142
D:\DATAFILES\SATRRDATA.DBF6.ORA                       1,000    1,000        0
E:\ORACLE\ORADATA\STARR\STARRLOCAL1.DBF                 500      500        0
E:\ORACLE\ORADATA\STARR\STARRLOCAL2.DBF                 600      600        0
E:\ORACLE\ORADATA\STARR\STARRLOCAL3.DBF                 700      700        0
D:\DATAFILES\STARRDATA7.DBF                           1,500    1,500        0
D:\DATAFILES\STARRDATA8.DBF                           1,216    1,500      284
E:\ORACLE\ORADATA\STARR\STARRLOCAL4.DBF                 700      700        0
D:\DATAFILES\STARRLOCAL5.DBF                          2,000    2,000        0
D:\DATAFILES\STARRLOCAL6.DBF                          1,392    1,500      108
E:\ORACLE\ORADATA\STARR\TEMP01.DBF                        1      272      271
D:\DATAFILES\INDX02.DBF                                   1      100       99
E:\ORACLE\ORADATA\STARR\INDX01.DBF                        1      500      499
sum                                                                     2,032

25 rows selected.

Elapsed: 00:00:41.09
Input truncated to 1 characters

alter database datafile 'E:\ORACLE\ORADATA\STARR\SYSTEM01.DBF' resize 160m;
alter database datafile 'E:\ORACLE\ORADATA\STARR\USERS01.DBF' resize 41m;
alter database datafile 'E:\ORACLE\ORADATA\STARR\TOOLS01.DBF' resize 1m;
alter database datafile 'E:\ORACLE\ORADATA\STARR\SYSTEM02.DBF' resize 29m;
alter database datafile 'D:\DATAFILES\STARRINDEXES1.DBF' resize 901m;
alter database datafile 'D:\DATAFILES\STARRINDEXES2.DBF' resize 887m;
alter database datafile 'D:\DATAFILES\STARRINDEXES3.DBF' resize 858m;
alter database datafile 'D:\DATAFILES\STARRDATA8.DBF' resize 1216m;
alter database datafile 'D:\DATAFILES\STARRLOCAL6.DBF' resize 1392m;
alter database datafile 'E:\ORACLE\ORADATA\STARR\TEMP01.DBF' resize 1m;
alter database datafile 'D:\DATAFILES\INDX02.DBF' resize 1m;
alter database datafile 'E:\ORACLE\ORADATA\STARR\INDX01.DBF' resize 1m;


Thanks very much for your help, I'm overwhelming.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.