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.