Solved

Delete a rollback segment with undo_management set to AUTO?

Posted on 2007-04-03
6
1,208 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 48

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
what privileges needed for S2 for this function (Oracle 12c)? 3 23
Oracle encryption 12 48
Creation date for a PDB 5 39
oracle sqlplus query delimiter 8 23
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.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

828 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