Home » RDBMS Server » Backup & Recovery » Why does v$datafile not show temp01.dbf ?
Why does v$datafile not show temp01.dbf ? [message #527754] Thu, 20 October 2011 01:54 Go to next message
swaorclser
Messages: 56
Registered: August 2011
Member
Hello All,

When I run following query,
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:\APP\ADMINISTRATOR\ORADATA\THIRDB\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\ORADATA\THIRDB\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\ORADATA\THIRDB\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\THIRDB\USERS01.DBF


TEMP01.DBF is present in C:\APP\ADMINISTRATOR\ORADATA\THIRDB, still why it is not showing?

Thanks & Regards,
swaorclser
Re: Why does v$datafile not show temp01.dbf ? [message #527759 is a reply to message #527754] Thu, 20 October 2011 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because it is not a DATAfile but a TEMPfile, so you have it in v$tempfile.
I'm surprised you didn't ask you also have control files and log files but there are not in the output.

Regards
Michel
Re: Why does v$datafile not show temp01.dbf ? [message #527768 is a reply to message #527759] Thu, 20 October 2011 02:13 Go to previous messageGo to next message
swaorclser
Messages: 56
Registered: August 2011
Member
v$datafile shows datafiles and TEMP01.DBF is also a DATAFILE, be it a TEMPORARY. ( It has extension as DBF.)

I am very well aware that CONTROL files and REDO logs ARE NOT datafiles.

This is a way newcomers are answered???

Also I came across following,

SELECT NAME,
    FILE#,
    STATUS,
    CHECKPOINT_CHANGE# "CHECKPOINT"   
  FROM   V$DATAFILE;

NAME                                      FILE#     STATUS       CHECKPOINT
--------------------------------          -----     -------       ----------
/u01/oracle/rbdb1/system01.dbf                1     SYSTEM              3839
/u02/oracle/rbdb1/temp01.dbf                  2     ONLINE              3782
/u02/oracle/rbdb1/users03.dbf                 3     OFFLINE             3782


at the link http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/dfiles010.htm.

So this made me ask the question.

Thanks.
Re: Why does v$datafile not show temp01.dbf ? [message #527770 is a reply to message #527768] Thu, 20 October 2011 02:29 Go to previous message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
v$datafile shows datafiles and TEMP01.DBF is also a DATAFILE

No, in Oracle terminology, it is NOT a data file, it is a TEMP file. Whatever you think, it is Oracle that defines the name of its objects.

Quote:
It has extension as DBF

Oracle does NOT care about extension. You can name all of them ".swaorclser" if you want.

Quote:
Also I came across following,

It is not because something is called "*temp*" it is a temporary file.
Once again you can name your files as you want.
By the way, there is NO checkpoint on temporary files, this is irrelevant and meaningless.

Your link returns me:
Quote:
We're sorry, the page you requested was not found.


Regards
Michel
Previous Topic: RMAN Cloning issues
Next Topic: ORA-16004: backup database requires recovery
Goto Forum:
  


Current Time: Mon Dec 22 16:10:32 CST 2014

Total time taken to generate the page: 0.15239 seconds