Solved

How to reduce UNDOTBS01.DBF?

Posted on 2004-04-03
8
10,896 Views
Last Modified: 2011-08-18
Hello,

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:
F:\oradata\Table01.ora

Thanks!
0
Comment
Question by:Desperate34
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 8

Expert Comment

by:baonguyen1
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 ?



0
 
LVL 6

Expert Comment

by:morphman
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;

0
 

Author Comment

by:Desperate34
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?
0
 
LVL 11

Expert Comment

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

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

VC
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Accepted Solution

by:
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


VC
0
 
LVL 6

Expert Comment

by:morphman
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.

0
 
LVL 13

Expert Comment

by:riazpk
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:


VALUE
==============================================================================
8192

1 row selected.

Elapsed: 00:00:00.50

                                                   Smallest
                                                       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

CMD
===========================================================================
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;


0
 

Author Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now