| 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
I fully agree. Unfortunately management insists on it.
Russ
-----Original Message-----
Sent: Tuesday, August 20, 2002 9:13 PM
To: Multiple recipients of list ORACLE-L
Dick,
There is absolutely *nothing* that SAPDBA does that a reasonably knowledgeable DBA can't do from his of her favorite toolset. ( vi, Perl and sqlplus for me :)
SAP types have it drummed into their heads that the only proper way to do anything DBA work is via SAPDBA.
I refuse to use it, and it just drives the SAP consultants crazy.
There are many cases where a good DBA can do a much better job than SAPDBA. The tablespace reorganization is a good example. Trying to 'drop tablespace including contents' with 3500 tables is not a terribly bright way of going about it.
Jared
dgoulet_at_vicr.com
Sent by: root_at_fatcity.com
08/20/2002 02:43 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re:drop tablespace including contents
Russ,
Your high usage of RBS was due to the updates being done to the system
data
dictionary. Since you were dropping a tablespace and contents the DDL
statements for the individual objects (tables and indexes) needs to be
done
first, but I've a funny idea from practice that Oracle does not do an
implicit
commit in this case but instead holds on till the end. This makes
dropping a
tablespace with the "including contents" caviot very nasty. Thank GOD we
never
implemented SAP over here. I've heard nothing but bad about SAP and
sapdba.
Dick Goulet
____________________Reply Separator____________________ Author: "Brooks; Russ" <Russ.Brooks_at_dayzim.com> Date: 8/20/2002 11:13 AM
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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.50.4807.2300" name=GENERATOR></HEAD>
<BODY>
<DIV>Hi, <BR>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. <BR>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. <BR>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. <BR>1. Was the rollback activity due solely to storing and
restoring DDL for the tables and indices? <BR>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. <BR>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? <SPAN
class=926510418-20082002> Any
way to estimate how much or how long the rollback would take?</SPAN></DIV>
<DIV><SPAN class=926510418-20082002></SPAN><SPAN
class=926510418-20082002></SPAN>4<SPAN class=926510418-20082002>. 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.</SPAN><BR><BR>Thanks,
<BR>Russ
Brooks </DIV></BODY></HTML>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: dgoulet_at_vicr.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brooks, Russ INET: Russ.Brooks_at_dayzim.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 Wed Aug 21 2002 - 08:03:24 CDT
 |
 |