All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
You may use multiple physical files for a single tablespace.
Reason you have this option could be for many reasons.
- Expand you storage across multiple device
- Go beyond the file system file size limitation.
are some of the benefits.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂