Can anybody explain the differences and the relationship among database, tablespace and data file. Is there any logical or physical type of relationship exists ? any brief explanation will be appreciated. Thank you.
Answer by Andrew Mobbs ·
A recent Oracle database consists of at least two tablespaces, SYS and SYSAUX. Older versions only required SYS. It's usual to have more tablespaces, as it is bad practice to store user objects in SYS, and it is common to have separate TEMP and UNDO tablespaces.
Each tablespace consists of one or more datafiles (except TEMPORARY tablespaces, which use tempfiles). The tablespace is the logical storage structure used by DDL for storage allocation.
A datafile is a physical storage structure, which is usually a file on disk. (But could be a raw device, or on an ASM disk group).