ASM is just a storage layer, in order to add/enlarge datafiles, you need to create a separate job in database or schedule it in cron which will add the datafile to the tablespace if it reaches certain threshold. Datafile can be created with autoextend on and maxsize set to unlimited, so that it can grow as needed and the script will take care of adding new files if all the existing files already close enough to reach their MAX (generally 32gig).
Or you can choose to use Oracle BIGFILE tablespace, if your dataset is really huge.
Thanks Daljit, a simple answer to a simple question
- A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.
- Bigfile tablespaces are intended to be used with Automatic Storage Management (ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.
In the traditional manner, expanding diskspace is one of the jobs of some administrator. The Oracle database structure is such that a lot of limits can be set on the database level, so that each time some expansion should occur, the DBA should come in between.
Step 1, putting files to auto-extend, is the first step to shift the issue towards the lower level, but it still is at the database level.
The second step is to automatically create files (in any tablespace), which is what the question in the opening post is about. For that issue, non-ASM and ASM have the same problem.
If you create some job that will be able to do just that - adding a file to a tablespace that is full - the next issue arises :
Checking that the filesystem is not running full. The storage administrator is not likely to give you all the storage you would need on a long term, in real actual storage on a short term. One reason is because we just don't know how much, second reason : $$$.
So, only a relative small amount will be given ... and this shows the issue at that time : the storage admin can you give you the needed space, but it is the DBA's task to keep an eye on the remaining space !!! Because you automated step 1 and step 2, you risc of hitting the limit in step 3 !
And, in addition, also the DBA must provide a method of knowing "when a tablespace is getting full". You can only do that from the DB level, not from OS level.