RE: drop tablespace including contents

Beth,
 You could also select the info from the SM$TS_USED or SM$TS_FREE table as SYS.
the table desc is TABLESPACE_NAME, BYTES. You should see the bytes change as the work progresses.
I got this tip from OWS when I saw chasing a SMON at 100%cpu after a crash.
Ron
ROR mô¿ôm

>>> Beth.Seefelt_at_TetleyUSA.com 08/20/02 04:48PM >>>
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: Ron Rogers
  INET: RROGERS_at_galottery.org

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