RE: assistance dropping lost tablespace, contains partitioned tables

From: George Leonard <leonarge_at_vodacom.co.za>
Date: Thu, 22 Apr 2004 14:06:36 +0200
Message-ID: <99CEA8FB33554B4BAC372CE602F9B7ED0370DB05@zamdh02001.vodacom.corp>

Hi there

Sorry but don't work.

If I try and drop the tablespace I get the following error:

SQL> drop tablespace TS_DAILY1      including contents ;
drop tablespace TS_DAILY1      including contents

*
ERROR at line 1:
ORA-14407: partitioned table contains subpartitions in a different tablespace

The tables are spread over tablespaces - partitioned as range and sub partitioned as hash.

Also=20

You can not do a alter table <table> drop subpartition <partition_name> if this partition is of type hash.

George
=20

=20

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:george.leonard_at_za.didata.com
Web: http://www.didata.co.za
=20

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk,=20 Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
=20
=20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Boligan Sent: 22 April 2004 13:55 PM
To: oracle-l_at_freelists.org
Subject: Re: assistance dropping lost tablespace, contains partitioned tables

You can try
alter database datafile <datafilename> offline drop; At that point you should be able to drop the TS.

HTH,
Mike

|---------+----------------------------->
|         |           "George Leonard"  |
|         |           <leonarge_at_vodacom.|
|         |           co.za>            |
|         |           Sent by:          |
|         |           oracle-l-bounce_at_fr|
|         |           eelists.org       |
|         |                             |
|         |                             |
|         |           04/22/2004 07:07  |
|         |           AM                |
|         |           Please respond to |
|         |           oracle-l          |
|         |                             |
|---------+----------------------------->

=20

>-----------------------------------------------------------------------
------------------------------------------------|

=20 |

|
=20 | To: <oracle-l_at_freelists.org>
|
=20 | cc:

|
=20 | Subject: assistance dropping lost tablespace, contains

partitioned tables                                      |

=20

>-----------------------------------------------------------------------
------------------------------------------------|

Hi all

Scenario

We lost a set of discs, they were only being used as a scratch pad and contained no production data.

About 10 tablespace was located on the disc.

The tablespace had in them partitioned tables by list and then sub portioned by hash.

I am having problems dropping the tablespaces including contents since it picks up that it is a partitioned table that contains segments in other tablespaces.

I have tried dropping the user with cascade but this tries to access the actual datafiles which is of course not reachable.

George
=3D20

=3D20

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:george.leonard_at_za.didata.com
Web: http://www.didata.co.za
=3D20

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk,=3D20 Every Fool Has the Right to Kill or Injure Themselves as They See Fit!

=3D93This e-mail is sent on the Terms and Conditions that can be accessed=

b=3D
y Clicking on this link http://www.vodacom.net/legal/email.asp "

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


=93This e-mail is sent on the Terms and Conditions that can be accessed b=

y Clicking on this link http://www.vodacom.net/legal/email.asp "
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Apr 22 2004 - 07:02:07 CDT