Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Delete a rollback segment with undo_management set to AUTO?

Posted on 2007-04-03
6
Medium Priority
?
1,217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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