Ask the Expert

"No rows selected"

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

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: