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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.