Oracle Database Discussions

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