When was the last time you checked on your Oracle database tablespaces and their datafiles, how those things are set up and organized? Are you sure that your tablespaces and datafiles are set up in a way that won’t cause an error the next time data is inserted into a table? If you are coming from a SQL Server background, you may not even know that you should be checking these things.

I wasn’t aware. Until our finance application, which is built on an Oracle database, had an error when trying to insert data due to a tablespace being out of space. When that happened, I did some quick online searching, found the first solution I trusted, and implemented that to increase the size of the datafile in question. And I’ve also dealt with some DB2 tablespace issues as well in the past, but I have never understood what’s going on and what the best way is to deal with current issues, and also prevent future issues. Until now.

In this post, I will walk you through the most important quick checks you can run on your Oracle database to ensure you’re not about to run into the brick wall of having a tablespace and its datafiles completely out of available space. If you are using a modern installation of an Oracle database, this may not even be an issue for you. But with my database, it was created as an on-prem database over 20 years ago, then moved to the cloud about 5 years ago with all the old baggage still intact. And that was handed to me about a year ago, so I’ve had to learn as I go, and I want to spare you that pain.

What’s in this post

What are tablespaces and datafiles?

These two database objects and logical entities are something you may be totally unfamiliar with if you’ve been working with Microsoft SQL Server in the modern age, because Microsoft obfuscates and removes any direct management of these entities. They exist on all RDBMSs in some manner or another, but you might not always need to know what they are or how to manage them.

So what are they?

Tablespaces are essentially a logical way to group the data for a set of tables. You can specify which tables (and their indexes) are stored on which tablespaces, so you can manage those tables together.

Datafiles are how the data of the tables is grouped to translate to physical storage on disks, and how the database tells the operating system to store its data.

If you get an error from an application along the lines of “unable to increase tablespace <tablespace> by 1 MB during insert or update on table...“, that would make you think that you need to add space to the tablespace, but you would be wrong. Since tablespaces are a logical data storage mechanism, you can’t add space to them directly. Instead, you need to add space to the datafiles related to that tablespace to resolve such an error.

The most important checks

I will go over all these more in detail in separate sections below, but I believe the items on this simplified list are the most important checks you should be running on your Oracle database to ensure you’re not about to hit storage errors.

  1. All datafiles (tablespaces) are set to autoextend
  2. Datafiles have room to grow
  3. Tablespace as a whole has enough room to grow
  4. No datafile is close to the absolute maximum

All datafiles (tablespaces) are set to autoextend

One of the easiest settings to check and enable on your tablespaces and datafiles is the “autoextend” setting, which, if enabled, will automatically increase the size capacity of datafiles for you when they are about to run out of space. Run the query below to see which of your datafiles are not set to autoextend.

SELECT *
FROM dba_data_files
WHERE AUTOEXTENSIBLE = 'NO';

Once you have that list of datafiles that are stuck at their specified size, you can then change them so that they will automatically grow, either up to a total maximum size you specify, or “unlimited”, which will stop growing at the physical maximum for a file based on some other values you specified when setting up the database. Those queries look like this:

ALTER DATABASE DATAFILE '+DATA/database_name/id_value/DATAFILE/tablespace.id.id' AUTOEXTEND ON MAXSIZE 1G;
ALTER DATABASE DATAFILE '+DATA/database_name/id_value/DATAFILE/tablespace.id.id' AUTOEXTEND ON MAXSIZE UNLIMITED;

Datafiles have room to grow

The next thing you’ll want to check, even if your datafiles are set to autoextend, is to make sure that there is still enough allocated space for your files to grow, that the file isn’t about to hit the autoextend limit that was set (if they were set to autoextend). To see what datafiles might be in trouble currently, you can run the query below, which will return any records where the value of the column “bytes” is equal to the value of “maxbytes”.

SELECT FILE_NAME
	,TABLESPACE_NAME
	,bytes
	,AUTOEXTENSIBLE
	,MAXBYTES
FROM dba_data_files
WHERE bytes = maxbytes;

The “bytes” column is the current amount of data storage used in the file, in bytes. The “maxbytes” column is the total maximum value the datafile can use (in bytes), which is the highest amount of storage the datafile can grow to if it is set to autoextend. If bytes = maxbytes, that means there is no storage left in the file, so no data will be able to be inserted into the file. And you’ll likely receive an error like this the next time someone tries to insert data into a table stored on that datafile:

unable to increase tablespace <tablespace> by 1 MB during insert or update on table...

In my environment, I had a total of 7 datafiles where bytes = maxbytes, which I needed to review to decide how to handle the situation.

Screenshot of the results of a query on an Oracle database to return datafiles where the used bytes value is the same as the maxbytes value. There are 7 redacted records shown, to demonstrate what the data looks like when a datafile can no longer extend itself.

You can either leave the files as they are, like in a case where the file has been extended to the maximum allowable size for the database, or you can choose to increase the maxbytes value so the file can continue to grow itself more as needed. If you are in the former situation where a datafile is at the maximum size for your database, see the “No datafile is close to the absolute maximum” section below to learn how to handle that situation.

To change the maxbytes value to increase the amount of storage the datafile can grow with, you can run a query like this:

ALTER DATABASE DATAFILE '+DATA/database_name/id_value/DATAFILE/tablespace.id.id' AUTOEXTEND ON MAXSIZE 1G;

This query is essentially the same as the one above, where you set the file to autoextend, but this time, you are ensuring you are setting the maxsize value to something significantly greater than the current value of maxsize and greater than the current value of bytes, which is the current amount of space used in the datafile.

Tablespace as a whole has enough room to grow

As I mentioned in the first section of this post above, a tablespace is a logical entity that is comprised of one or more datafiles, which are physical storage entities. Since a tablespace can have one or more datafiles, we also need to check to see if the tablespace itself has room to grow and insert new data into its tables. To get such information, we need to sum together the amount of space used for all datafiles within the tablespace, sum together the total amount of growable space in the datafiles, then calculate the percent free for all datafiles and thus the tablespace. The query below is what I have used to retrieve that data for my tablespaces.

SELECT fs.tablespace_name "Tablespace"
	,(df.totalspace - fs.freespace) "Used MB"
	,fs.freespace "Free MB"
	,df.totalspace "Total MB"
	,round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
FROM (
	SELECT tablespace_name
		,round(sum(bytes) / 1048576) TotalSpace
	FROM dba_data_files
	GROUP BY tablespace_name
	) df
	,(
		SELECT tablespace_name
			,round(sum(bytes) / 1048576) FreeSpace
		FROM dba_free_space
		GROUP BY tablespace_name
		) fs
WHERE df.tablespace_name = fs.tablespace_name
ORDER BY "Pct. Free"
Screenshot of the results of a query on an Oracle database showing a list of 7 tablespaces, with most names redacted except SYSTEM and SYSAUX, demonstrating what the data looks like for tablespaces when they have very little free room for storage.

Anything that has 5 percent or less free in the tablespace should be looked at. Other tablespaces may also need reviewing if they have tables that fill up quickly. It all depends on your environment. In my case, I decided 5% or less free was worth fixing.

Resolving low availability in tablespaces

If you found, with the above query, that you had one or more tablespaces that were almost out of space, you will need to do something to remedy that situation. There are essentially two options for giving more space to your tablespace: 1) increase the maximum size of the datafiles within the tablespace so they have room to grow and make sure they’re set to autoextend, or 2) add one or more additional datafiles that can be used to store more data going forward.

Query to increase the size of the datafile:

ALTER DATABASE DATAFILE '+DATA/database_name/id_value/DATAFILE/tablespace.id.id' AUTOEXTEND ON RESIZE 1GB;

In this query, you are explicitly setting the new total size value of the datafile, which in this case, is now going to be 1 GB in size. Also, ensure you’ve set a high enough autoextend value by using the previous section above.

Query to add another datafile to a tablespace:

ALTER TABLESPACE tablespace ADD DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE 5G;

In this query, we are creating a new datafile, without specifying a path, because we’re assuming that the database is set up to have files managed by Oracle. We’re setting the starting size of the file to be 1 GB and setting AUTOEXTEND on so the file can grow up to 5 GB as needed.

If you have space that you can extend your existing datafiles, I would proceed with option one. However, if the files are close to their absolute maximum size so they can’t be extended, you will be forced to go with option 2 and need to create those additional datafiles. How do you know whether or not you need to add more files? Read on to the next section to learn how to figure out if a file is close to the maximum allowable size.

No datafile is close to the absolute maximum

While executing the step above of trying to increase the data space inside your tablespace, you may run into the situation where the datafile(s) within the tablespace are at the maximum allowable limit according to your database settings, so you can’t further extend those files. To discover if you have any datafiles in that situation, run the query below, which will get you close to the standard maximum file size. In my case, I had one datafile in the SYSAUX tablespace that was completely full, so if I were going to extend that tablespace, I would 100% need to create additional datafiles.

SELECT FILE_NAME
	,TABLESPACE_NAME
	,BYTES
	,MAXBYTES
FROM dba_data_files
WHERE bytes > 34000000000
Screenshot showing a single record returned by a query on an Oracle database, showing what the data looks like when a datafile is at the maximum allowable size for a database, with the BYTES and MAXBYTES columns both being the same value of 34,359,721,984.

What is the maximum datafile size allowed?

The answer to this question depends on the settings of your database, specifically, the size of data block your database was created with. A data block is the smallest unit of data that can be stored in an Oracle database. By default, most Oracle databases are created with an 8 KB data block, but they can also be created with a 4 KB block by default, or a different value that you specify upon creation. To find the maximum size of datafiles within your database, we will need to run a query to find the data block size and then do some math.

First, run the query below to get the specified data block size for your database:

select *
from v$parameter
where name = 'db_block_size';

That will return the parameter that contains the size of data blocks for your database, like this:

Screenshot showing a single record returned from a query on an Oracle database showing what data is returned when you query the "v$parameter" view to get the default size of data blocks in your database. The screenshot shows a value of 8192 for the parameter db_block_size.

According to the Oracle documentation, the maximum number of database blocks for a datafile is “platform dependent; typically 2^22-1 blocks”. That means that the answer depends on the operating system your database is installed on (Windows, Linux, etc.), but is typically 4,194,303 blocks. If we assume you have a typical system with an 8 KB block size, that means the maximum number of bytes for a single datafile is 34,359,730,176 bytes, which is about 34 GB. I can’t guarantee that your datafile’s maximum size is that value, but it’s highly likely if you have that 8 KB block size.

If you run the above query to get datafiles with byte values > 34000000000, those files are likely close to your maximum limit, so you should create additional datafiles in those same tablespaces if no others exist already. Otherwise, you are very close to a “cannot insert data” disaster.

Summary

In the Oracle database world, all logical and physical storage tasks are not automatically maintained for you the way they are in the Microsoft SQL Server realm. I would consider Oracle databases a little more old-fashioned in that sense, since now I must regularly remember to do these types of checks on our databases to ensure we never get the dreaded “can’t increase tablespace” error. I would recommend that if you have an Oracle database, and you’re not already doing these types of checks, that you go through your database and run all the queries and checks I listed above to ensure your storage layer has a minimum viable level of support. You don’t want to be caught unaware and uneducated on the topic like I was a few months ago.