i added a datafile to temp tablespace temp02.dbf
then i gave the cmd alter database datafile offline drop to drop the older one which lot of fragmentation.
and error of the kind ora3232.
then i removed it physically.
the dba_data_files still showed the temp01.dbf as available.
and queries started failing due to non-availability of temp01.
under v$datafile temp01 appeared as recover status and 0 bytes.
even under dba_free_space all records of temp01 are appearing with the original file id of 4.
recover datafile or tablespace or database are also
though guidance came belated i must thank you sambavan
When i posted the problem i didn't have much time to
get the DB online.
i droped the temp tablespace and created a new one with 2 datafiles.
that solved the problem
mine is ora 8.1.6 on nt server with archive log enaled.
what bugged me was inconsistent behaviour of oracle.
i went about the task systematically by first adding a new datafile to the temp tablespace and then offlining then dropping followed by physically dropping the original datafile.
alter db datafile <> offline drop command got the datafile in v$datafile as 0 bytes and recover status.
and the same cmd when fired repeatedly gave the same response: statement processed.
why still the datafile appeared in dba_data_files as available and with original size?
why dba_free_space continued to show all the records for the dropped datafile?
and why execution of queries failed with error that datafile no.<> is locked/unavailable.
even export and recover tablespace/datafile cmds failed .
i can see any logic in this insistence on a particular file for just about any operation.
i even backed up the control file to trace and edited the trace file removing the line specifying that particular datafile.
at svrmgr i executed the edited tracefile.
then at least the dba_datafiles didn't show that datafile.
but queries like those creating indexes failed.
export also didn't work
on oracle 8.1.6 on hp-ux i remember having successfully removed the only datafile in temp tbs and the adding new datafile with no problem being reported later.
1) You can only drop a tablespace but not a datafile, although your can bring a datafile offline.
2) Deframentation in temp tablespace is not something you should worry.
3) In case temp tablespace is corrupted or not working, create another temporary tablepsace and change default temporay tablespace of all users to the new temporary tablespace. But it should be done with care, especially during peak time.