Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Lipman Li
Ranch Hand
Posts: 122
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
anybody know why? I'm very confused.
 
Beksy Kurian
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 122
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it is not showed up, but
select tablespace_name from dba_tablespaces;
there's a TEMP table space exist.
 
Lipman Li
Ranch Hand
Posts: 122
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 254
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 122
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic