How to reduce UNDOTBS01.DBF?

Posted on 2004-04-03
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:

Question by:Desperate34
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2

Expert Comment

ID: 10748160
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 ?


Expert Comment

ID: 10748174
You can drop your undo tablespace.

First check there are no active transactions as follows:-

SELECT, b.status
FROM v$rollname a, v$rollstat b
WHERE 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;


Author Comment

ID: 10748230
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?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 11

Expert Comment

ID: 10748276
You can try and shrink the datafile itself:

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

LVL 11

Accepted Solution

vc01778 earned 150 total points
ID: 10748284
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


Expert Comment

ID: 10748290
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.

LVL 13

Expert Comment

ID: 10748325
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;


Author Comment

ID: 10748438
Thanks very much for your help, I'm overwhelming.

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question