Oracle Tablespaces maxsize "unlimited" is not completely unlimited

Question

I recently needed to import .dmp into the new user I created. I also created a new tablespace for the user with the following command:

create tablespace my_tablespace datafile 'C:\My\Oracle\Install\DataFile01.dbf' size 10M autoextend on next 512K maxsize unlimited; 

While the import was in progress, I received an error message:

 ORA-01652 Unable to extend my_tablespace segment by in tablespace 

When I examined the data files in the dba_data_files table, I noticed that maxsize is about 34 gigabytes. Since I knew the total size of the database, I was able to import .dmp without any problems after adding several data files to the tablespace.

Why do I need to add multiple data files to a tablespace when the first one I added was set to automatically grow to unlimited size? Why is the maximum size 34gb and not unlimited? Is there a 34gb hard cover?

Answer 1

As you have discovered, and as Alex Pool noted, there are restrictions on the size of individual data files. Small files are limited to 128 GB, and large files are limited to 128 TB, depending on the size of your block. (But you do not want to resize the block just to increase these limits.) The size limit in the create tablespace command exists only where you want to further limit the size.

This can be a bit confusing. You probably don't care about file management and want it to "just work." Managing database storage will always be annoying, but here are some things you can do:

• Limit your table spaces. There are a few rare cases where it is useful to split data into many small table spaces. But these rare benefits are usually superior to the pain you will experience when managing all these objects.
• Get in the habit of always adding multiple data files. If you use ASM (which I would not recommend if it is a local instance), then there is almost no reason not to "go crazy" when adding data files. Even if you do not use ASM, you will still be a little crazy. As long as you set the minimum size of the original, you are not close to the MAX_FILES limit, and you are not dealing with one of the special table spaces such as UNDO and TEMP, there is no penalty for adding more files. Do not worry about allocating more potential space than is contained in your hard drive. This causes some database administrators to be crazy, but you have to weigh the likelihood of exiting the OS space compared to the possibility of exiting the space of one hundred files. (In any case, your application will fail.)
• Set the RESUMABLE_TIMEOUT parameter. Then the SQL statements will be paused, may generate a warning, will be listed in DBA_RESUMABLE and will patiently wait for more space. This is very useful in data warehouses.

---

Why is it called "UNLIMITED"?

I would suggest that the UNLIMITED keyword is a historical mistake. Oracle had the same file size limit as at least version 7 and possibly earlier. Oracle 7 was released in 1992 when a 1 GB hard drive cost $ 1995 . Perhaps each operating system at that time had a file size limit below this. Perhaps then it was wise to think of 128 GB as "unlimited."