RE: drop tablespace including contents

[Seefelt, Beth]

Title: Message

Hi Russ,

 

I'm experiencing a very similar situation today.  A batch job aborted last night after it couldn't extend the rollback segment beyond 4GB, and I'm still waiting for the rollback to complete.  Before identifying that, my symptoms were very similar to yours.  Any DDL or DML against the table would hang, the cpus are very active and there is very little i/o going on. 

 

Here's is what I'm using to monitor the rollback progress, and might be helpful to you if it happens again -

 

select a.sid,b.used_ublk
from v$session a, v$transaction b
where a.taddr = b.addr and a.username =  'PROD' ;

substitute your username.  Watch the USED_UBLK column.  It decreases when a rollback is occuring, and the rollback will finish when it hits 0.  It gives you an idea how long before the rollback will complete.

 

HTH,

 

Beth

-----Original Message-----
From: Brooks, Russ [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 20, 2002 3:13 PM
To: Multiple recipients of list ORACLE-L
Subject: drop tablespace including contents

Hi,
This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again.
After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change.
Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup.
1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices?
2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from the system. Was that the appropriate reaction to this problem.
3. A join of v$session and v$sql did not indicate any active SQL. How should we have monitored the progress of what we assume was rollback activity?   Any way to estimate how much or how long the rollback would take?

4. If the database were shutdown during the rollback I assume the rollback would recommence when Oracle came back up.  Would it start where it left off or start from scratch.  It was my impression that it is marking the header blocks as it goes, but I would like to check.

Thanks,
Russ Brooks