ORA-01114 and tempfile id

this error indicates
ORA-01114: IO error writing block to file 1023 (block # 455946)
Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.

but there's no file with id 1023 in the database.

This is actually a tempfile. Tempfile id is being reckoned from (db_files + dba_temp_files.file_id).
So in this case the value of init.ora parameter "db_files" is 1022 and that makes the first tempfile id = 1023.

fuck all those who say to read documentation. there's supposed to be a metalink note which talks about it, but its hard to find it due to metalinks screwed search engine.
ненавижу бля ебаный оракл.

Temporary tablespaces

Restriction on Taking Tablespaces Offline: You cannot take a temporary tablespace offline.

To get an overview of temp tablespace usage:

select TABLESPACE_NAME,CURRENT_USERS,SEGMENT_FILE,USED_EXTENTS,FREE_EXTENTS from v$sort_segment;

select username, user, contents, segtype, tablespace from v$tempseg_usage;

If you want to drop non-default temporary tablespace, you issue something like:

> drop tablespace tempt including contents and datafiles;

At least in 10.2 the previous statement got hanging until the temporary segments (temp tables in my case) were not cleared.

Also after killing the non-default temp tablespace 'tempt', the temp tables created in there became invalid:

SCOTT@andrkydb> insert into te values (6);
insert into te values (6)
*
ERROR at line 1:
ORA-00959: tablespace 'TEMPT' does not exist

it is also funny, that I now can neither use, nor drop the temp table 'te':

SCOTT@andrkydb> drop table te;
drop table te
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

Metalink article (Doc ID: Note:270645.1) says that table need to be truncated before drop. But it still doesn't help. I cannot drop the table.

If I recreate the tablespace 'tempt' I can use the table but still cannot drop it!

Instance restart helped...

This problem doesn't appear if we operate only with default temporary tablespaces.