Solved

Delete a rollback segment with undo_management set to AUTO?

Posted on 2007-04-03
6
1,202 Views
Last Modified: 2013-12-18
I want to drop a large rollback segment in my UNDOTBS1 tablespace, but my database has the undo_management parameter set to AUTO.  Is there any way to drop that one segment without changing that parameter?


To let you know why I ask the question, I was experimenting with ways to add large numbers of rows to a table.  I tried creating 46,000,000 rows without doing a periodic commit, let it run for about 14 hours, and just aborted it.  Now I have a rollback segment taking up 5 Gigs of harddrive space that is totally unnecessary - I've learned my lesson and won't be doing updates or inserts of that size without periodic commits.

Thanks
Joe
0
Comment
Question by:DalTXColtsFan
  • 3
  • 2
6 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 125 total points
ID: 18844851
Undo Tablespace File can be reduced.

--reduce the retnetention to 5 minutes

ALTER SYSTEM SET UNDO_RETENTION = 5;

--create a new ts for a temp undo

CREATE UNDO TABLESPACE undotbs_02
DATAFILE '/u02/ora_data/oradata/dbeta/undo02.dbf' SIZE 2M REUSE AUTOEXTEND ON;

--set the undo ts to new ts
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;


--wait for original undo ts to be freed up (no online segments).

select segment_name,status,tablespace_name from dba_rollback_segs
where status='ONLINE' and tablespace_name='UNDOTBS1';


--offline old ts

alter tablespace UNDOTBS1 offline;


-- drop old ts

drop tablespace UNDOTBS1 including contents and datafiles;

--recreate new default ts

create undo tablespace UNDOTBS1
datafile '/u02/ora_data/oradata/dbeta/undotbs01.dbf' size 200m reuse autoextend on next 10m;

--repoint undo to default again
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1;


--wait for temp undo ts to be freed up (no online segments).
 
select segment_name,status,tablespace_name from dba_rollback_segs
where status='ONLINE' and tablespace_name='UNDOTBS_02';

--offline the temp undo tablespace

alter tablespace undotbs_02 offline;

--drop temp undo

drop tablespace undotbs_02 including contents and datafiles;

--reset retnetion to normal default (whatever you use in init.ora)

ALTER SYSTEM SET UNDO_RETENTION = 10800;
0
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 18844867
good god man, is there ANYTHING you don't know???? ;)

Just kiddin - I'll try that.  Thanks!
0
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 18845405
worked perfectly.  Had to reboot a couple times to get Oracle to release its lock on the files (I probably could have just restarted the service but what the hey).
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 47

Expert Comment

by:schwertner
ID: 18848924
I run in the same situation a year before you.... The same problem arise also for Temporary tablespace but the solution is more direct.
Good Luck!
0
 
LVL 7

Expert Comment

by:gattu007
ID: 18850800
You can also check these notes from metalink

If using Oracle9i/Oracle 10g review
  Note 135217.1 Which Operations are Allowed or Prohibited on RBS with or without AUM?:
  Note 135053.1 How to Create a Database with Automatic Undo Management:
  Note 268870.1 How to Shrink the datafile of Undo Tablespace:
0
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 18850806
Thanks gattu007.
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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

706 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

16 Experts available now in Live!

Get 1:1 Help Now