Manage data files of Oracle database.

Managed Database data files in Oracle

In this blog, you can managed to add the new data file in a Tablespace if tablespace reached its upper limit, you can disable or enable the auto extend feature of datafile and set the maximum size for a datafile.
Sometime following error occurred in Alert log:
ORA-01659: unable to allocate MINEXTENTS beyond string in Tablespace string
Note: In this error you need to increase the size of Tablespace by adding more Datafiles into the existing Tablespace or increase the size of existing Datafile currently present in Tablespace

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;

5. Disable the autoextend of the datafile:

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;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.