Cannot get Oracle to drop tablespaces and recognize that datafiles have been dropped

I have a slightly different problem, but along the way I got the same error. A dba-in-training was attempting to do some partition maintenance on one of our largest tables yesterday. It has 90 days worth of partitions on a rolling basis. When he went to drop the old partitions (older than 90 days) and add the new ones for next week, he made a procedural error (he accidentally did things completely out of order). So on my way in this morning, I got a call that the database was inaccessible. When I arrived and looked at the alert log, last evening we were getting
Wed May 28 22:57:38 2008
Errors in file /u01/app/oracle/admin/DWHPRD1/bdump/dwhprd1_ckpt_16904.trc:
ORA-01110: data file 99: '/f11/oracle/DWHPRD1/DAH080605.dbf'
ORA-01116: error in opening database file 99
ORA-01110: data file 99: '/f11/oracle/DWHPRD1/DAH080605.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
and soon afterward we got
Wed May 28 22:57:38 2008
CKPT: terminating instance due to error 1242
Wed May 28 22:57:38 2008
Errors in file /u01/app/oracle/admin/DWHPRD1/bdump/dwhprd1_pmon_16892.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode

The datafiles involved in this mess were being created for next weeks data, so they were empty. I started to use the old trick of "alter database datafile 'filename' offline drop" Oracle accepted the command, but my colleague failed to mention that he had already removed the physical files from the server. Oracle accepted the commands, but still shows the datafiles as being there. I was eventually able to restart the database, and it is open for business, but there is a lingering problem I do not know how to resolve. My colleague said he had dropped the partitions (each partition is in its own tablespace) but Oracle still reports that the tablespace, datafile, and partition are out there, as follows:

select tablespace_name, file_name, bytes, autoextensible, increment_by, maxbytes from dba_data_files
where tablespace_name like 'DAH%'
TSPACE                        FILE_NAME                                                            BYTES
DAH080601                     /f11/oracle/DWHPRD1/DAH080601.dbf
****************************  ******************************************************  ------------------
DAH080602                   /f11/oracle/DWHPRD1/DAH080602.dbf
****************************  ******************************************************  ------------------
DAH080603                     /f11/oracle/DWHPRD1/DAH080603.dbf
****************************  ******************************************************  ------------------
DAH080604                     /f11/oracle/DWHPRD1/DAH080604.dbf
****************************  ******************************************************  ------------------
DAH080605                     /f11/oracle/DWHPRD1/DAH080605.dbf
****************************  ******************************************************  ------------------
DAH080606                     /f11/oracle/DWHPRD1/DAH080606.dbf
****************************  ******************************************************  ------------------
DAH080607                     /f11/oracle/DWHPRD1/DAH080607.dbf
****************************  ******************************************************  ------------------
So Oracle still thinks the datafiles are there, but shows no size for them (there are no files on server). If I tell Oracle to do an offline drop of the files, it says operation completed, but still shows the files as existing. If I try to offline or drop the tablespaces for which no files exist, or drop the partitions, here is what I get.

SQLDW1>alter tablespace dah080607 offline;
alter tablespace dah080607 offline
*
ERROR at line 1:
ORA-01191: file 117 is already offline - cannot do a normal offline
ORA-01110: data file 117: '/f11/oracle/DWHPRD1/DAH080607.dbf'

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

SQLDW1>alter table incentive.daily_account_history drop partition PART080607;
alter table incentive.daily_account_history drop partition PART080607
*
ERROR at line 1:
ORA-00376: file 117 cannot be read at this time
ORA-01110: data file 117: '/f11/oracle/DWHPRD1/DAH080607.dbf'

Well of course! It makes perfect sense! So, how can I work around this conundrum? I need to be able to get Oracle to accept commands that will drop the 7 tablespaces/datafiles/partitions involved. We do NOT want to have to drop and recreate the entire table from exports, it is very large and it would take a long time. It could also result in the premature demise of my colleague.

ORIGINAL RELATED ISSUE:
I have an application that errored out.

On checking out the logs I found the following log contents:

What is the problem and how can I rectify it?

SQL> set time on
23:13:35 SQL> set timing on
23:13:35 SQL> @/home/appmgr/PPGP/sfi/sql/sfi_drop.sql
23:13:35 SQL> set verify off
23:13:35 SQL> select distinct a.tablespace_name ,a.status
23:13:35   2  from dba_tablespaces a, dba_data_files b
23:13:35   3  where a.status <> 'READ ONLY'
23:13:35   4  and a.tablespace_name = b.tablespace_name
23:13:35   5  and b.file_name like '%/u391%'
23:13:35   6  order by tablespace_name
23:13:35   7  /

no rows selected

Elapsed: 00:00:00.04
23:13:35 SQL> drop tablespace SFI_DATA_Q1 including contents ;
drop tablespace SFI_DATA_Q1 including contents
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace



23:13:43 SQL> drop tablespace SFI_INDEX_Q9 including contents ;
drop tablespace SFI_INDEX_Q9 including contents
*
ERROR at line 1:
ORA-14405: partitioned index contains partitions in a different tablespace


Elapsed: 00:00:00.35


Start Free Trial