File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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 Murach's Java Servlets and JSP this week in the Servlets 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
 
Similar Threads
Help with Properties file
B&S: Problem -read field length in bytes
Serialization Doubt
How to I know the my URLyBird version?
Serialization and deserialization