5 Replies Latest reply: Feb 7, 2013 12:26 AM by 956893 RSS

    Drop partition with missing associate Tablespace

    956893
      Hi All,

      Scenario is user accidentally removed datafile using rm command then we dropped that Tablespace using offline drop. we want to drop partition for which TS and datafile both are already offline dropped .

      Thanks.
        • 1. Re: Drop partition with missing associate Tablespace
          Helios-GunesEROL
          Hi;

          So why you dont demonstrate it yourself to see answer? Its best way for learn :)

          Regard
          Helios
          • 2. Re: Drop partition with missing associate Tablespace
            rp0428
            Welcome to the forum!

            Whenever you post provide your 4 digit Oracle version.
            >
            Scenario is user accidentally removed datafile using rm command then we dropped that Tablespace using offline drop. we want to drop partition for which TS and datafile both are already offline dropped .
            >
            What happened when you tried to drop the partition?

            Don't be afraid of breaking Oracle by trying things.

            You can safely test what you want to do because if the DROP is successful you are done and if not successful you are back where you started anyway.
            • 3. Re: Drop partition with missing associate Tablespace
              956893
              Thank You guys for your Prompt response and for your warm Welcome.

              Oracle Version is : 11.2.0.1

              When I dropped the partition Its prompted below error,
              ORA-00376: file 3868 cannot be read at this time
              ORA-01110: data file 3868

              We have a partition table which contains day wise Partitions. During the clean up activity user deleted whole month datafiles using 'rm' command. :( I want to drop all the partitions as well as tablespaces.

              I tried to exchange partition with another table having same properties of original table. While doing so it prompted below error,

              ORA-14292: Partitioning type of table must match subpartitioning type of composite partition
              • 4. Re: Drop partition with missing associate Tablespace
                rp0428
                >
                I tried to exchange partition with another table having same properties of original table. While doing so it prompted below error,

                ORA-14292: Partitioning type of table must match subpartitioning type of composite partition
                >
                Then most likely the table and partition do NOT have the same properties.

                If you want to exchange a partition of a table that is subpartitioned then the other table has to be partitioned in the same way that the main table is subpartitioned.

                Post the DDL for the main table that shows how it is partitioned/subpartitioned and the DDL for the work table you are trying to use for the exchange.

                See my reply Posted: Jan 7, 2013 7:02 PM in this thread for a solution that uses exchange partition is a similar manner
                Merge tables
                • 5. Re: Drop partition with missing associate Tablespace
                  956893
                  Actually here we do not have data under the tablespace associated with the partition thats why we are not able to exchange.

                  PS : Both table and partition have same properties.