1 2 Previous Next 24 Replies Latest reply: Oct 28, 2007 6:21 AM by 443104 RSS

    How to a drop tablespace ?????

    443104
      Hi Expert !!

      I am living my life in ORA errors. To describe the complete picture:

      1) We have a partitioned table whose partitions reside in multiple tablespaces.
      2) One of the datafiles got corrupted where one old partition resides.
      3) We are not interested in restoring the corrupt datafile and no more require that partition.
      4) How do I drop the partition or tablespace?

      Well, I have tried the following but no success:

      startup mount
      alter database datafile 'C:\MYDB\DATA\SALES_2005_TS1.DBF' offline;
      alter database open;

      conn sales/sales

      SQL> ALTER TABLE sales_range DROP PARTITION sales_2005 update global indexes;
      ALTER TABLE sales_range DROP PARTITION sales_2005 update global indexes
      *
      ERROR at line 1:
      ORA-00376: file 7 cannot be read at this time
      ORA-01110: data file 7: 'C:\MYDB\DATA\SALES_2005_TS1.DBF'

      SQL> conn /as sysdba
      Connected.
      SQL> drop tablespace sales_2005_ts including contents;
      drop tablespace sales_2005_ts including contents
      *
      ERROR at line 1:
      ORA-14404: partitioned table contains partitions in a different tablespace


      SQL> alter tablespace SALES_2005_TS offline;
      alter tablespace SALES_2005_TS offline
      *
      ERROR at line 1:
      ORA-01191: file 7 is already offline - cannot do a normal offline
      ORA-01110: data file 7: 'C:\MYDB\DATA\SALES_2005_TS1.DBF'


      SQL> alter tablespace SALES_2005_TS offline immediate;

      Tablespace altered.

      SQL> drop tablespace SALES_2005_TS including contents;
      drop tablespace SALES_2005_TS including contents
      *
      ERROR at line 1:
      ORA-14404: partitioned table contains partitions in a different tablespace


      I did search on net and metalink but unfortunately couldn't find anything useful. I did try metalink note 267125.1 but again no luck.

      Could any of you shed some light on this?


      Regards
        • 1. Re: How to a drop tablespace ?????
          RPuttagunta
          what is the version?
          • 2. Re: How to a drop tablespace ?????
            443104
            Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
            • 3. Re: How to a drop tablespace ?????
              PavelE.
              Hi, probably:
              alter database offline drop;
              • 4. Re: How to a drop tablespace ?????
                495137
                Exchange partition.

                sql> create table x ( x number not null, y number not null ) partition by list (y) ( partition p1 values (1,2,3) tablespace test1, partition p2 values (4,5,6) tablespace test2);
                sql> create index y on x(y);
                sql> insert into x values (1,2);
                sql> insert into x values (1,3);
                sql> insert into x values (2,6);
                sql> commit;

                sql> shutdown immediate

                sql> host

                $ rm /opt/oracle/oradata/ora10gr2/test02.dbf

                $ exit

                sql> startup mount
                sql> alter database datafile '/opt/oracle/oradata/ora10gr2/test02.dbf' offline;
                sql> alter database open
                sql> select * from x;
                ERROR:
                ORA-00376: file 6 cannot be read at this time
                ORA-01110: data file 6: '/opt/oracle/oradata/ora10gr2/test02.dbf'

                sql> alter table x partition p2 update global indexes;
                ERROR:
                ORA-00376: file 6 cannot be read at this time
                ORA-01110: data file 6: '/opt/oracle/oradata/ora10gr2/test02.dbf'

                sql> create table temp as select * from x where 1=0;
                sql> alter table x exchange partition p2 with table temp without validation;
                sql> alter table x drop partition p2 update global indexes;

                no errors on last drop.

                Now that assumes you don't care about the data in partition p2.

                Message was edited by:
                user492134

                Drop your tablespace after you drop the partition in the example. As you saw, you can't drop the tablespace when it contains a partition of table that has partitions in other tablespaces. After you drop the affected partition you will be able to drop the tablespace.
                • 5. Re: How to a drop tablespace ?????
                  247514
                  You offlined the datafile of tablespace where partition resides on, of course you can't drop the partition.

                  Why don't you online the datafile, drop the partition and drop the tablespace?
                  • 6. Re: How to a drop tablespace ?????
                    443104
                    Hi,

                    Thanks for the reply !!

                    But unfortunately, I am receiving error when trying to exchange partition. Please correct me if I am doing it wrong.
                    SQL> startup
                    ORACLE instance started.

                    Total System Global Area  176160768 bytes
                    Fixed Size                  1247876 bytes
                    Variable Size             159384956 bytes
                    Database Buffers            8388608 bytes
                    Redo Buffers                7139328 bytes
                    Database mounted.
                    ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
                    ORA-01110: data file 7: 'C:\MYDB\DATA\SALES_2005_TS1.DBF'


                    SQL> alter database datafile 'C:\MYDB\DATA\SALES_2005_TS1.DBF' offline;

                    Database altered.

                    SQL> alter database open;

                    Database altered.

                    SQL> create table temp as select * from sales.sales_range where 1=2;

                    Table created.

                    SQL> alter table sales.sales_range exchange partition sales_2005
                    2 with table temp
                    3 without validation;

                    alter table sales.sales_range exchange partition sales_2005
                    *
                    ERROR at line 1:
                    ORA-00376: file 7 cannot be read at this time
                    ORA-01110: data file 7: 'C:\MYDB\DATA\SALES_2005_TS1.DBF'
                    • 7. Re: How to a drop tablespace ?????
                      443104
                      Why don't you online the datafile, drop the partition
                      and drop the tablespace?
                      SQL> alter database datafile 'C:\MYDB\DATA\SALES_2005_TS1.DBF' online;
                      alter database datafile 'C:\MYDB\DATA\SALES_2005_TS1.DBF' online
                      *
                      ERROR at line 1:
                      ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
                      ORA-01110: data file 7: 'C:\MYDB\DATA\SALES_2005_TS1.DBF'
                      • 8. Re: How to a drop tablespace ?????
                        495137
                        Whoops, misread your last reply. Checking .. I would have expected you to get the error on temp table creation but not during the partition drop.
                        • 9. Re: How to a drop tablespace ?????
                          Nicolas.Gasparotto
                          Change your 1=2 to 1=0.
                          Woaw, that is a big difference !

                          >
                          Basically if you have 1=1, 1=2, 1=3 etc it equates to
                          true and says create the table with all the data. So
                          it tries to read the datafile and fails.
                          Can you explain me how 1=2 return true ?

                          Nicolas.

                          Remark quoted from the previous post, which has been edited out. So, that invalidate this post.
                          Message was edited by:
                          N. Gasparotto
                          • 10. Re: How to a drop tablespace ?????
                            495137
                            my bad, misread the reply. haven't had enough caffeine obviously!

                            you are right.
                            • 11. Re: How to a drop tablespace ?????
                              539154
                              "Basically if you have 1=1, 1=2, 1=3 etc it equates to
                              true and says create the table with all the data."

                              Nicolas, can you explain this a little bit "user friendly" please? It's very new to me, and of course understandable that 1=1 (gives true). But 1=2 etc? And I understood from previous posts, that if "where 1=0" is used then the condition is false and none of the data is selected...

                              Thanx a log, Rev
                              • 12. Re: How to a drop tablespace ?????
                                Nicolas.Gasparotto
                                What about the following :
                                SQL> create tablespace test1 datafile 'E:\oracle\oradata\DEMO102\test01.dbf' size 1m;

                                Tablespace created.

                                SQL> create tablespace test2 datafile 'E:\oracle\oradata\DEMO102\test02.dbf' size 1m;

                                Tablespace created.

                                SQL> create table x ( x number not null, y number not null ) partition by list (y) ( partition p1 va
                                lues (1,2,3) tablespace test1, partition p2 values (4,5,6) tablespace test2);

                                Table created.

                                SQL> insert into x values (1,3);

                                1 row created.

                                SQL> insert into x values (2,6);

                                1 row created.

                                SQL> commit;

                                Commit complete.

                                SQL> select * from x;

                                         X          Y
                                ---------- ----------
                                         1          3
                                         2          6

                                SQL> shutdown immediate
                                Database closed.
                                Database dismounted.
                                ORACLE instance shut down.
                                SQL> host del E:\oracle\oradata\DEMO102\test02.dbf

                                SQL> startup
                                ORACLE instance started.

                                Total System Global Area  272629760 bytes
                                Fixed Size                  1290084 bytes
                                Variable Size             234881180 bytes
                                Database Buffers           29360128 bytes
                                Redo Buffers                7098368 bytes
                                Database mounted.
                                ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
                                ORA-01110: data file 10: 'E:\ORACLE\ORADATA\DEMO102\TEST02.DBF'


                                SQL> alter database datafile 'E:\oracle\oradata\DEMO102\test02.dbf' offline;

                                Database altered.

                                SQL> alter database open;

                                Database altered.

                                SQL> select * from x;
                                ERROR:
                                ORA-00376: file 10 cannot be read at this time
                                ORA-01110: data file 10: 'E:\ORACLE\ORADATA\DEMO102\TEST02.DBF'



                                no rows selected
                                --At this point, I reproduced your problem

                                SQL> shutdown immediate
                                Database closed.
                                Database dismounted.
                                ORACLE instance shut down.
                                SQL> startup mount
                                ORACLE instance started.

                                Total System Global Area  272629760 bytes
                                Fixed Size                  1290084 bytes
                                Variable Size             239075484 bytes
                                Database Buffers           25165824 bytes
                                Redo Buffers                7098368 bytes
                                Database mounted.
                                SQL> alter database datafile 'E:\oracle\oradata\DEMO102\test02.dbf' offline drop;

                                Database altered.

                                SQL> alter database open;

                                Database altered.

                                SQL> drop tablespace test2 including contents;
                                drop tablespace test2 including contents
                                *
                                ERROR at line 1:
                                ORA-14404: partitioned table contains partitions in a different tablespace


                                SQL> alter table x drop partition p2;

                                Table altered.

                                SQL> drop tablespace test2;

                                Tablespace dropped.

                                SQL> create table x1 as select * from x;

                                Table created.

                                SQL> select * from x;

                                         X          Y
                                ---------- ----------
                                         1          3

                                SQL>
                                Nicolas.
                                • 13. Re: How to a drop tablespace ?????
                                  Nicolas.Gasparotto
                                  Rev,
                                  I just quoted what user492134 wrote before, I was on the same state you are, since the remark, he/she edited his/her post.

                                  Nicolas.
                                  • 14. Re: How to a drop tablespace ?????
                                    247514
                                    .

                                    Overlooked OP's scenario description. Edit out.

                                    Message was edited by:
                                    yingkuan
                                    1 2 Previous Next