ORA-01659: unable to allocate MINEXTENTS beyond string in Tablespace string
In this blog, you can add a new data file to a Tablespace when it reaches its limit. You can also enable or disable the auto-extend feature and set a maximum size for the data file.
A following error occurred in the Alert log:
ORA-01659: cannot allocate MINEXTENTS beyond string in Tablespace string
Note: To fix this, increase the Tablespace size by adding Datafiles or enlarging the existing Datafile.
Following steps will give you more details:
1. Connect to the database
sqlplus / as sysdba
2. Check the datafile and tablespace going to filled.
set line 999 pages 999
col FILE_NAME format a50
col tablespace_name format a15
Select tablespace_name, file_name, autoextensible, bytes/1024/1024/1024 "USEDSPACE GB", maxbytes/1024/1024/1024 "MAXSIZE GB" from dba_data_files order by tablespace_name;
3. Add one datafile to the tablespace:
alter tablespace USERS add datafile 'D:\ORACLE11204\ORADATA\PEGA\USERS02.DBF' size 1G autoextend on next 500M;
4. Resize the datafile upto 32GB.
Note: Max datafile size in Oracle is 32GB in normal tablespace.
alter database datafile 'D:\ORACLE11204\ORADATA\PEGA\USERS02.DBF' resize 32767M;
alter database datafile 'D:\ORACLE11204\ORADATA\PEGA\USERS02.DBF' autoextend off;
6. Set the Max size of the datafile
alter database datafile 'D:\ORACLE11204\ORADATA\PEGA\USERS02.DBF' autoextend on maxsize 10G;
7. Enable the auto extend of data file
alter database datafile 'D:\ORACLE11204\ORADATA\PEGA\USERS02.DBF' autoextend on;