I was dealing with a message like this for one of our developers:
ORA-01653: unable to extend table ...
He was using a tablespace which had one datafile with AUTOEXTEND ON and this datafile had reached its MAXBYTES / MAXSIZE. I wondered what would happen if you tried to set a datafile's MAXBYTES / MAXSIZE to a value less than its actual size. I tried this first on Oracle 9. I started with a datafile which had an actual size of 20 megabytes and a MAXBYTES / MAXSIZE of 50 megabytes. I found that I was able to set its MAXBYTES / MAXSIZE to 1 megabyte and that the change was recorded in DBA_DATA_FILES:
SQL> select file_id, bytes, maxbytes, autoextensible
2 from dba_data_files
3 where tablespace_name = 'USER_DATA'
4 /
FILE_ID BYTES MAXBYTES AUT
---------- ---------- ---------- ---
22 20971520 52428800 YES
SQL> alter database datafile 22
2 autoextend on
3 maxsize 1m
4 /
Database altered.
SQL> select file_id, bytes, maxbytes, autoextensible
2 from dba_data_files
3 where tablespace_name = 'USER_DATA'
4 /
FILE_ID BYTES MAXBYTES AUT
---------- ---------- ---------- ---
22 20971520 1048576 YES
SQL>
However, when I tried to do a similar thing in Oracle 11, the MAXBYTES value shown in DBA_DATA_FILES did not go below the actual size of the datafile:
SQL> select file_id, bytes, maxbytes
2 from dba_data_files
3 where tablespace_name = 'USERS'
4 /
FILE_ID BYTES MAXBYTES
---------- ---------- ----------
4 20971520 52428800
SQL> alter database datafile 4
2 autoextend on maxsize 10m
3 /
Database altered.
SQL> select file_id, bytes, maxbytes
2 from dba_data_files
3 where tablespace_name = 'USERS'
4 /
FILE_ID BYTES MAXBYTES
---------- ---------- ----------
4 20971520 20971520
SQL>
