Subject: Drop Tablespace Including Contents And Datafiles Takes Hours
To Complete
Doc ID: Note:438461.1 Type: PROBLEM
Last Revision Date: 11-JUL-2007 Status: MODERATED
In this Document
Symptoms
Cause
Solution
------------------------------------------------------------------------
--------
This document is being delivered to you via Oracle Support's Rapid
Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not
been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.3
This problem can occur on any platform.
Symptoms
The following command takes very long to complete:
SQL> drop tablespace <tablespace name> including contents and datafiles;
Dropping the tablespace only with contents completes much faster.
But whenever datafile clause is included in the drop tablespace
statement it takes hours to complete.
Cause
SQL trace on the drop tablespace command would show the query below is
consuming most of the time:
select distinct ind.bo#, ind.type#
from ind$ ind, indcompart$ icp1, indsubpart$ isp1
where isp1.ts#=:1
and isp1.pobj#=icp1.obj#
and icp1.bo#=ind.obj#
and ind.type# <> 8
and exists
(select *
from indcompart$ icp2, indsubpart$ isp2
where icp2.bo# = icp1.bo#
and icp2.obj#=isp2.pobj#
and isp2.ts# <> :1)
You are frequently creating a lot of new partitions and dropping old
partitions in the tablespace.
You are not gathering optimizer statistics for SYS schema.
The lack of statistics on SYS schema is the cause for this slowness.
Solution
Gathering statistics on SYS schema will help optimizer to select a good
execution plan for the above mentioned recursive query.
You can gather statistics for the SYS schema using the following
procedure:
SQL> conn / as sysdba
SQL> execute dbms_stats.gather_schema_stats('SYS');
Keywords
'DROP~TABLESPACE' 'TABLE~PARTITION'
------------------------------------------------------------------------
--------
Help us improve our service. Please email us your comments for this
document. .
Bookmarks Admin Profile Feedback Sign Out Help
Copyright (c) 2006, Oracle. All rights reserved. Legal Notices and
Terms of Use | Privacy Statement
-----Original Message-----
From: Edwards Ed
[mailto:oracledba-ezmlmshield-x33558861.[Email address protected]
Sent: Monday, April 28, 2008 1:02 PM
To: LazyDBA Discussion
Subject: RE: drop table space including content and datafile
I just ran the command (DROP TABLESPACE EDTEST INCLUDING CONTENTS AND
DATAFILES;) as sysdba and as dba and it worked both times in Oracle 10G
Release 2. Does it have to do with file permissions on the physical
tablespace datafile?
Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Dr. Eric J. Smith is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below. Thank you in
advance for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
-----Original Message-----
From: Qi Cheng
[mailto:oracledba-ezmlmshield-x84518767.[Email address protected]
Sent: Monday, April 28, 2008 12:59 PM
To: LazyDBA Discussion
Subject: Re: drop table space including content and datafile
I have no problem to use dba user to drop it in 9i.
In 10G, the dba user can also execute the command, just the file not get
deleted for some reason.
On 4/28/2008 10:43 AM, Michael Peel
<oracledba-ezmlmshield-x90895696.[Email address protected] wrote:
>Sys.
>
>Michael Peel
>
>
>-----Original Message-----
>From: Edwards Ed
>[mailto:oracledba-ezmlmshield-x79515521.[Email address protected]
>
>Sent: 28 April 2008 15:42
>To: LazyDBA Discussion
>Subject: RE: drop table space including content and datafile
>
>Did you run it as "sys" or an account that has "DBA" rights?
>
>
>
>-----Original Message-----
>From: Michael Peel
>[mailto:oracledba-ezmlmshield-x79314920.[Email address protected]
>
>Sent: Monday, April 28, 2008 10:38 AM
>To: LazyDBA Discussion
>Subject: RE: drop table space including content and datafile
>
>No you don't.
>I've just done it on 9.2.
>Has one of us had too many redstripes...?
>
>Michael Peel
>
>
>-----Original Message-----
>From: Edwards Ed
>[mailto:oracledba-ezmlmshield-x91634088.[Email address protected]
>
>Sent: 28 April 2008 15:34
>To: LazyDBA Discussion
>Subject: RE: drop table space including content and datafile
>
>BTW, you need to be running Oracle 10G Release 2 and up.
>
>
>-----Original Message-----
>From: Edwards Ed
>[mailto:oracledba-ezmlmshield-x6971716.[Email address protected]
>
>Sent: Monday, April 28, 2008 10:30 AM
>To: LazyDBA Discussion
>Subject: RE: drop table space including content and datafile
>
>Well, well, well!
> It's Qi Cheng! Hehe. How have you been?
>
>To delete the datafiles associated with a tablespace at the same
>time
>that the tablespace is dropped, use the INCLUDING CONTENTS AND
DATAFILES
>clause. The following statement drops the users tablespace and its
>associated datafiles:
>
>DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
>
>
>
>
>
>
>
>Please take a few minutes to provide feedback on the quality of service
>you received from our staff. The Department of Education values your
>feedback as a customer. Commissioner of Education Dr. Eric J. Smith
>is
>committed to continuously assessing and improving the level and quality
>of services provided to you.Simply use the link below. Thank you
>in
>advance for completing the survey.
>
>
>http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
>
>
>
>
> -----Original Message-----
>From: Qi Cheng
>[mailto:oracledba-ezmlmshield-x88726861.[Email address protected]
>
>Sent: Monday, April 28, 2008 10:23 AM
>To: LazyDBA Discussion
>Subject: drop table space including content and datafile
>
>drop table space including content and datafile is executed without
>errors.
>The data file not get delelted.
>Any idea?
>
>Same command in 9i worked well, but in 10g, seems has problem.
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>To post a dba job: http://jobs.lazydba.com
>To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>To post a dba job: http://jobs.lazydba.com
>To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>To post a dba job: http://jobs.lazydba.com
>To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>**********************************************************************
>This e-mail is confidential and may be privileged.
>It may only be read, copied and used by the intended recipient.
>
>If you have received it in error please contact the sender immediately
>by return e-mail. Please then delete the e-mail and any copies of
>it
>and do not use or disclose its contents to any person.
>**********************************************************************
>Registered Office: Farncombe House, Broadway, Worcestershire, WR12
>7LJ
>
>AccuRead Limited Registered Number: 3076187 England
>GSL UK Limited Registered Number: 3333860 England
>Global Solutions Limited Registered Number: 3189802 England
>**********************************************************************
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>To post a dba job: http://jobs.lazydba.com
>To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>To post a dba job: http://jobs.lazydba.com
>To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>**********************************************************************
>This e-mail is confidential and may be privileged.
>It may only be read, copied and used by the intended recipient.
>
>If you have received it in error please contact the sender immediately
>by return e-mail. Please then delete the e-mail and any copies of
>it
>and do not use or disclose its contents to any person.
>**********************************************************************
>Registered Office: Farncombe House, Broadway, Worcestershire, WR12
>7LJ
>
>AccuRead Limited Registered Number: 3076187 England
>GSL UK Limited Registered Number: 3333860 England
>Global Solutions Limited Registered Number: 3189802 England
>**********************************************************************
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>To post a dba job: http://jobs.lazydba.com
>To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page