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.
asked
Relationship among database, tablespace and data file ?
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).
ASM is Automatic Storage Management. It's an Oracle subsystem that manages storage directly, replacing the filesystem and logical volume manager. It can handle RAID, optimize data placement on disk, and Oracle claim it offers improved IO performance over letting the OS manage the storage. It also means DBAs don't need to talk to sysadmins as much, which can't be a bad thing :-). A disk group is the physical storage element within ASM. See
http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b31107/asmcon.htm for the documentation.
