Monday, February 04, 2013

Can You Set a Datafile's Maxbytes / Maxsize to Less Than its Actual Size?

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>

No comments: