Check datafile is autoextensible in Oracle

Check datafile is auto extended on or not in Oracle

Check for the tablespace and datafile wise:

SELECT autoextensible FROM dba_data_files WHERE tablespace_name = 'TABLESPACE_NAME'

SELECT file_name,autoextensible FROM dba_Data_files WHERE tablespace_name = 'tn';

SELECT file_name,bytes/1024/1024/1024 "curentsize GB", maxbytes/1024/1024/1024 "MaxSize GB", autoextensible FROM dba_data_files where tablespace_name = 'tn';

In Oracle, the max size of datafile in normal tablespace is 32GB. If current size of data file is already reached then no need to edit.

Alter the datafile to autoextensible size:

ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <size>;

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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.