My issue is, when I select all rows from dba_tablespaces the content column shows me the entry of the temporary_data column as temporary and the rest as permanent. When I select from v$tempfiles or dba_temp_files the result is "no rows selected." What is the problem and how is the tablespace marked temporary in the dba_tablespaces column created in the database? When I created the temporary tablespace it showed me the entry in the dba_temp_files and v$tempfile also. I'm using Microsoft and Oracle 8.1.7.

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top