Delete a rollback segment with undo_management set to AUTO?

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
LVL 2
DalTXColtsFanAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
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
 
DalTXColtsFanAuthor Commented:
good god man, is there ANYTHING you don't know???? ;)

Just kiddin - I'll try that.  Thanks!
0
 
DalTXColtsFanAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
schwertnerCommented:
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
 
gattu007Commented:
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
 
DalTXColtsFanAuthor Commented:
Thanks gattu007.
0
All Courses

From novice to tech pro — start learning today.