Data file vs. Temp file creation

[KenEwald]

Why is temp file creation and extension so much faster than data file creation? Is there additional validation going on?

[hrishy]

Hi

Temp files are sparse in nature oracle will not format the blocks thats why even if you create a 10gb tempfile its fast.

For datafile oracle has to format the blocks and that takes time.

regards
Hrishy

[KenEwald]

Thank you, that makes perfect sense.
Cheers,
Ken

[gopu_g]

Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except that:
- You cannot create a tempfile with the ALTER DATABASE statement.
- You cannot rename a tempfile or set it to read-only.
- Tempfiles are always set to NOLOGGING mode.
- When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems (for example, UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.
- Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view.


Note: this arrangement enables fast tempfile creation and resizing; however, the disk could run of space later when the tempfiles are accessed.

[KenEwald]

Thanks for the additional details.

It appears that when a tempfile is created, it just lays down a bof and an eof marker on the specified storage.

It also appears that when a datafile is created, write I/O's are intense. It makes sense that it would be walking through the storage to get every block's physical address.