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:

ADD DATAFILE 'D:\oracle\product\10.2.0\oradata\profi\INDX18.DBF' 

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

  • Oracle doesn't create data files automatically? Sure you didn't have a script doing this for you?
    – Philᵀᴹ
    Nov 12 '14 at 18:26
  • I'm not sure. I'm not database administrator. I'm user but everyone think that I know everything just because I can do report query. If oracle doesn't create new datafile automatically it means that there had to be some script or some program that monitors situation and adds new datafile to tablespace.
    – Marcin1199
    Nov 13 '14 at 8:06

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.