Skip to Main Content
  • Questions
  • Tablespace keeps autoextending to 30Gb

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jose.

Asked: June 01, 2022 - 9:24 am UTC

Last updated: June 30, 2022 - 3:19 pm UTC

Version: 19

Viewed 1000+ times

You Asked

Hi guys,

I have the following issue:

-When i add a new datafile on my SAP APP at PSAPSR3 tablespace with 1Gb for example and with maxsize of 30GB after 2 minutes Oracle resize the datafile to 30GB, if i resize again to 1GB 2 minutes after oracle resize again to 30GB.

This behavior only happens on this tablespace !!! This tablespace don't have any special settings to do this. Anyone can explain what did it hapen for this behavior ?

thanks

and Chris said...

Have you checked the AUTOEXTEND properties for this data file?

The database increases its size by whatever the NEXT value is - even if you only need a few extra megabytes.

For example, this creates a tablespace with a 1Mb file and 10Gb autoextend:

create tablespace small_ts
  datafile size 1m
  autoextend on next 10G;
  
select bytes/1024/1024, increment_by
from   dba_data_files
where  tablespace_name = 'SMALL_TS';

BYTES/1024/1024 INCREMENT_BY
--------------- ------------
              1      1310720


Once the database needs to allocate extents beyond 1M, it'll increase the file size by 10G - even if you only need a small amount more space.

Continuing the example, this loads a 8Mb table into the tablespace, which increases the file size by another 10Gb:

create table t 
tablespace small_ts as
  select level c1, 
         rpad ( 'stuff', 4000, 'f' ) c2
  from   dual 
  connect by level <= 1000;

select bytes/1024/1024 from dba_segments
where  segment_name = 'T';

BYTES/1024/1024
---------------
              8
  
select bytes/1024/1024 from dba_data_files
where  tablespace_name = 'SMALL_TS';

BYTES/1024/1024
---------------
          10241
          
drop table t 
  cascade constraints purge;
  
drop tablespace small_ts
  including contents and datafiles;

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database