Data are not saved to database - Auto creating new datafile in oracle

Question

I have some problem with Oracle database. Before it works like that, if INDX[Counter].dbf file reach its maximum size (8GB) Oracle automatically created new datafile INDX[Counter+1].dbf. But on server wasn't enough size to create new datafile. I increased space on server, I created new datafile manually:

ALTER TABLESPACE INDX2
ADD DATAFILE 'D:\oracle\product\10.2.0\oradata\profi\INDX18.DBF' 
SIZE 4M
AUTOEXTEND ON NEXT 4M
MAXSIZE 8G;

Right now after INDX18.dbf reach 8GB Oracle wasn't create new datafile automatically and data are no longer stored. I had to add new datafile manually. How to set Oracle database to auto create new data file after reach maximum size of single datafile?

Best regards

Answer 1

In your example, you have Autoextend on with 4M as the next extension, but you also have MAXSIZE set to 8G, which means that Oracle is being told that once a tablespace reaches 8G, it will no longer automatically add space.

This is for safety purposes, as sometimes administrators want to know when a datafile is growing very large very quickly.

In your case, you could issue alter tablespace indx2 autoextend on maxsize <some number>G;, which would allow this tablespace to automatically extend to that maximum size, depending on how much space you have on your server! If you run out of actual physical disk, nothing in the Oracle database will protect you from this.

Additionally, you'll still want to periodically monitor the size of this tablespace, as you will (theoretically) still eventually reach the maximum size of the autoextend. Even if you set the maxsize to unlimited, there are still factors that determine the maximum size of a tablespace that are out-of-scope for your particular question. Just know that there is always a maximum upper limit to a tablespace, and someone will have to monitor it to take appropriate action.