
Originally Posted by
hardik_982
when i try out above query it shows zero rows(no such file id)
That's very good news. Your offending datafile is part of your temporary tablespace, you can double-check that by running query below...
Code:
select file_id,
file_name,
tablespace_name
from dba_temp_files
where file_id = 201
;
Here is the fix.
1- Drop the offending temporary tablespace; this is the one you got from the above query.
2- Re-Create your temporary tablespace; be sure no datafile is larger than 4 Gig and as Chiappa said make them of a fixed size -autoextend=no