• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 122
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
anybody know why? I'm very confused.
 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic