• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12845
  • Last Modified:

How to reduce UNDOTBS01.DBF?


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:

  • 2
  • 2
  • 2
  • +2
1 Solution
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;

Desperate34Author Commented:
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?
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

You can try and shrink the datafile itself:

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

Failing the above for whatever reason,  you can:

1. create undo tablespace undotbl_new datafile size 1m;  -- new undo

2. alter system set undo_tablespace = undotbl_new scope=both;

3. drop tablespace undotbs; -- drop old undo

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.

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;

Desperate34Author Commented:
Thanks very much for your help, I'm overwhelming.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now