| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: drop tablespace including contents
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-----
Sent: Tuesday, August 20, 2002 3:13 PM
To: Multiple recipients of list ORACLE-L
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
INET: Beth.Seefelt_at_TetleyUSA.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Aug 20 2002 - 15:48:31 CDT
 |
 |