This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes why datafile for TEMP tablespace is not listed in the V$DATAFILE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "why datafile for TEMP tablespace is not listed in the V$DATAFILE" Watch "why datafile for TEMP tablespace is not listed in the V$DATAFILE" New topic
Author

why datafile for TEMP tablespace is not listed in the V$DATAFILE

Lipman Li
Ranch Hand

Joined: May 02, 2002
Posts: 122
Hi,
anybody know why? I'm very confused.
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
Did you drop the temp tablespace? Do you have a different name for the datafile?
Do the following query...and see temp datafile(or tablespace name) is showing up!
select file_name,tablespace_name
from dba_data_files;
Regards
Beksy
Lipman Li
Ranch Hand

Joined: May 02, 2002
Posts: 122
it is not showed up, but
select tablespace_name from dba_tablespaces;
there's a TEMP table space exist.
Lipman Li
Ranch Hand

Joined: May 02, 2002
Posts: 122
I've found the reason.
strictly speaking, the datafile used for temporary tablespace is not called data file, it is called temp file instead.
try this statement
select * from v$tempfile

cheers
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
Lipman, glad you found the solution.
The confusion was due to the lack of information you have given when you asked the question. Or I should have carefully looked at the 'temp tablespace' in it.
Looks like a good question for OCP dba architecture exam!!!
There is a difference between temporary tablespace and tablespace temporary.
If you create a tablespace TEMPORARY with the following syntax:
CREATE TABLESPACE .. TEMPORARY
It uses datafiles only.
The TEMPORARY tablespace is introduced in 8i.
If you create a TEMPORARY tablespace with the following syntax:
CREATE TEMPORARY TABLESPACE .. TEMPFILE
It uses tempfiles only.
You identify a tablespace TEMPORARY/TEMPORARY tablespace in DBA_TABLESPACES for its CONTENTS of TEMPORARY type.
Use V$TEMPFILE and DBA_TEMP_FILES views to list the files associated to a TEMPORARY tablespace.
Use V$DATAFILE and DBA_DATA_FILES views to list the files associated to tablespaces TEMPORARY.
Regards
Beksy
Lipman Li
Ranch Hand

Joined: May 02, 2002
Posts: 122
good summary!

add some points:
CREATE TABLESPACE .... TEMPORARY statement will create dictionary-managed tablespace, provided that SYSTEM tablespace is also dictionary-managed.
CREATE TEMPORARY TABLESPACE ..... statement will create locally-managed tablespace, which Oracle recommend to use.
cheers
[ September 23, 2002: Message edited by: Lipman Li ]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: why datafile for TEMP tablespace is not listed in the V$DATAFILE