From oracle-l-bounce@freelists.org Thu Apr 22 08:00:58 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3MD0wC31207 for ; Thu, 22 Apr 2004 08:00:58 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3MD0v631198 for ; Thu, 22 Apr 2004 08:00:58 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EE78172CD6D; Thu, 22 Apr 2004 07:53:46 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 28968-17; Thu, 22 Apr 2004 07:53:46 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9F0E772CD9E; Thu, 22 Apr 2004 07:53:40 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 22 Apr 2004 07:52:26 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2BE4C72CD12 for ; Thu, 22 Apr 2004 07:52:25 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27357-98 for ; Thu, 22 Apr 2004 07:52:24 -0500 (EST) Received: from itpub.net (unknown [211.155.30.144]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id E342272CD51 for ; Thu, 22 Apr 2004 07:52:20 -0500 (EST) Received: (qmail 20617 invoked by uid 0); 22 Apr 2004 13:07:48 -0000 Received: from unknown (HELO oracle) (biti?rainy@218.108.180.230) by 0 with SMTP; 22 Apr 2004 13:07:48 -0000 From: "biti_rainy" To: "oracle-l@freelists.org" Subject: Re: RE: assistance dropping lost tablespace, contains partitioned tables X-mailer: Foxmail 4.2 [cn] Mime-Version: 1.0 Content-type: text/plain; charset=GB2312 Content-Transfer-Encoding: 8bit Date: 22 Apr 2004 13:07:48 -0000 Message-Id: <20040422125220.E342272CD51@turing.freelists.org> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3555 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: biti_rainy@itpub.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org X-Orig-Date: Thu, 22 Apr 2004 21:5:4 +0800 hi,George Leonard you should check all the tables in the TS. if the table has= some relative objects in the other TS ,you should drop them= first . in this case , there is a partition table in the TS and some= partition in the other TS ,so you should drop the table first= . select table_name from dba_tables where tablespace_name =3D 'THE= TS'; the indexes/constraints/mv etc , plz check it Best regards yahoo id: feng_chunpei A new dba from china ---- from the mail----- >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=3D20 > >You can not do a alter table drop subpartition= >if this partition is of type hash. > > >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@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! >=3D20 >=3D20 > >-----Original Message----- >From: oracle-l-bounce@freelists.org >[mailto:oracle-l-bounce@freelists.org] On Behalf Of Michael= Boligan >Sent: 22 April 2004 13:55 PM >To: oracle-l@freelists.org >Subject: Re: assistance dropping lost tablespace, contains= partitioned >tables > > > > > >You can try >alter database datafile offline drop; >At that point you should be able to drop the TS. > >HTH, >Mike > > >|---------+-----------------------------> >| | "George Leonard" | >| | | | co.za> | >| | Sent by: | >| | oracle-l-bounce@fr| >| | eelists.org | >| | | >| | | >| | 04/22/2004 07:07 | >| | AM | >| | Please respond to | >| | oracle-l | >| | | >|---------+-----------------------------> >=3D20 >>---------------------------------------------------------------= -------- >------------------------------------------------| >=3D20 | >| >=3D20 | To: >| >=3D20 | cc: >| >=3D20 | Subject: assistance dropping lost tablespace,= contains >partitioned tables | >=3D20 >>---------------------------------------------------------------= -------- >------------------------------------------------| > > > > >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 >=3D3D20 >________________________________________________ >=3D3D20 >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@za.didata.com >Web: http://www.didata.co.za >=3D3D20 >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,=3D3D20 >Every Fool Has the Right to Kill or Injure Themselves as They= See Fit! > >=3D3D93This e-mail is sent on the Terms and Conditions that can be= accessed=3D > >b=3D3D >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@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@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 >----------------------------------------------------------------= - >=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@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 >----------------------------------------------------------------= - > >. =A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------