In Oracle 8i, it is possible to create a temporary tablespace without tempfiles. You can use the following to accomplish just that:
CREATE TABLESPACE test_ts DATAFILE '/directory/filename' TEMPORARY;
Creating the tablespace as above marks it as temporary as can be seen in DBA_TABLESPACES. But the tablespace's files can be seen in DBA_DATA_FILES and V$DATAFILE.
Oracle 8i introduced the concept of tempfiles. The biggest reason is that any changes to the files are not logged in the redo logs. To create a temporary tablespace with tempfiles, use the following command:
CREATE TEMPORARY TABLESPACE test_ts TEMPFILE '/directory/filename';
Notice that the TEMPORARY clause has moved to right after the CREATE keyword. If the CREATE TEMPORARY TABLESPACE command is used, the TEMPFILE clause must be used.
You are seeing differences in how the temporary tablespace was created.
This was first published in July 2006