On Oracle 12c, is the content of each datafile belonging to a single tablespace the same?
If yes, is it because of performance or backup purpose thus recommanding us to store each datafile on different drives?
If no then why would we create multiple datafiles for a single tablespace when we can autoextend each datafile?
The maximum size that a data file can reach when set to 'maxsize unlimited' depends on the type of tablespace 'smallfile' (default) or 'bigfile' and the size of the data blocks. But, you do not just configure everything to be big because of the way tablespace extents work. An 8k block size smallfile tablespace can have data files of up to 32GB each.
If you create multiple data files when first creating a tablespace, the TB I/O will not be somehow 'optimized' between the files. I/O optimization occurs at a lower level--either file system, RAID, or even down in the NAS/NFS/SCSI architecture itself. Each data file will be used in turn until an extent can no longer be allocated for a given segment at which time the next data file will be used. This means that smaller extent requests may still be possible in the nearly full data file after the next one has already begun usage. What they WON'T DO is behave like a disk pool with extents spread across them.
How you distribute data files in the file system is less dependent on the shape and behavior of one tablespace as much as on the distribution of I/O load across all data files across the entire database. In some cases, it may be wise to put new data files on separate file systems and sometimes not. It is important to understand the I/O distribution of your database.
Cheers,
Russ
You may use multiple physical files for a single tablespace. Reason you have this option could be for many reasons.
To answer your 2nd question; Yes you could set auto extend but there is a maximum limit an operating system can handle as a single file . eg: Linux 64 bit maximum file size is 32GB so if you set auto extend tablespace setting unlimited with single data file that tablespace would not extend beyond this limit. refer: Physical Database Limits Best practice is when you create datafile which is expected to grow maximum to set maximum as 64 then create multiple datafiles and to have initial size with a lower number.
