How to reduce UNDOTBS01.DBF?

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

Question by:Desperate34
  • 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 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;


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?
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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 450 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

621 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