Skip to Main Content
  • Questions
  • Want to understands autoextend concept of tablespace


Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 31, 2021 - 9:02 am UTC

Last updated: April 12, 2021 - 5:25 am UTC

Version: 12.1

Viewed 100+ times

You Asked

Hi Tom

I have installed SAP on Oracle DB there is one table space PSAPSR3 got created & we have only 1 sapdata FS like /Oracle/FS/sapdata.

So can you help me how auto extend will work , will it extend all table space if we set auto extend?


and we said...

Tablespaces don't autoextend - their data files do! Autoextensible files can grow up to their max size.

You can view this information with:

select substr ( file_name, instr ( file_name, '/', -1 ) + 1 ) filename, 
       tablespace_name, autoextensible, bytes, maxbytes 
from   dba_data_files;

system.271.1039595837      SYSTEM             YES             503316480    34359721984 
sysaux.272.1039595837      SYSAUX             YES             608174080    34359721984 
undotbs1.270.1039595837    UNDOTBS1           YES            2243952640    34359721984 
users.275.1039596367       USERS              YES           34357903360    34359721984 
users.276.1067866925       USERS              YES           13610516480    34359721984 
tblsp.dbf                  TBLSP              NO               12582912              0

You can change the setting for a file with alter database. This disables autoextension:

alter database datafile '...' 
  autoextend off;

This enables it up to 10Gb:

alter database datafile '...' 
  autoextend on 
  maxsize 10g;

You can also set these properties when creating the tablespace or adding a data file to an existing tablespace.


  (2 ratings)


A reader, March 31, 2021 - 6:24 pm UTC

Hi Tom,

i tried to extend the tablespace with brtools got below option.

Options for extension of tablespace PSAPSR3 (1. file)

1 * Last added file name (lastfile) ……. [/oracle/PI1/sapdata4/sr3_18/sr3.data18]

2 * Last added file size in MB (lastsize) . [9216]

3 – New file to be added (file) ……….. [/oracle/PI1/sapdata4/sr3_19/sr3.data19]

4 ~ Raw disk / link target (rawlink) …… []

5 – Size of the new file in MB (size) ….. [9216]

6 – File autoextend mode (autoextend) ….. [yes]

7 – Maximum file size in MB (maxsize) ….. [32000]

8 – File increment size in MB (incrsize) .. [20]

9 – SQL command (command) …………….. [alter tablespace PSAPSR3 add datafile ‘/oracle/PI1/sapdata4/sr3_19/sr3.data19’ size 9216M
autoextend on next 20M maxsize 32000M]

My understanding are blow please correct if i am wrong
1) this sr3.data19 will get extended till 32000M from 9216M by auto extending 20M each time

2) Once it is cross 32000M still can i extend this FS by your query which you have mentioned?
Is there any limitations for datafile size

3)if i keep new file size & max size both same supoose 9216M then auto extend will worm or not?

4) what is the file id?

5) Is it possible that if a table space has multiple data file but some of them only marked as auto extend on
Chris Saxon
April 01, 2021 - 9:03 am UTC

1 Yes

2 There are limits, but it depends on your OS and tablespace type; see the docs for full details:

3 Not sure what you're asking here

4 It's an internal unique identifier for the file

5 Yes

Nitin, April 07, 2021 - 5:33 am UTC

Thanks Tom,

Can you please tell me the SQL query for setting a threshold in tablespace for autoextend

Suppose if table space got 90% full then extend by 10Gb like that.

Connor McDonald
April 12, 2021 - 5:25 am UTC

Something like this should give you a nice report

select d.tablespace_name, lpad(round(tot_size/1024/1024)||'m',10) alloc_size, lpad(round(f.tot_free/1024/1024)||'m',10) tot_free, round(100-100*tot_free/tot_size) pct_used
( select tablespace_name, sum(tot_free) tot_free
  ( select tablespace_name, sum(bytes) tot_free
    from dba_free_space
    group by tablespace_name
    union all
    select tablespace_name, 0
    from dba_data_files
    group by tablespace_name
    union all
    select tablespace_name, 0
    from dba_temp_files
    group by tablespace_name
    union all
    select s.tablespace, -1*alloc*t.block_size
    from ( select /*+ NO_MERGE */ tablespace, sum(blocks) alloc
           from   v$sort_usage
           group by tablespace) s,
          dba_tablespaces t
    where t.tablespace_name = s.tablespace
  group by tablespace_name
) f,
( select tablespace_name, sum(bytes) tot_size
  from dba_data_files
  group by tablespace_name
  union all
  select tablespace_name, sum(bytes)
  from dba_temp_files
  group by tablespace_name
) d
where f.tablespace_name(+) = d.tablespace_name
order by 1

More to Explore


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