Q

"No rows selected"

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?

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.

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close