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

Breadcrumb

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?

Thanks,
Nitin

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;

FILENAME                   TABLESPACE_NAME    AUTOEXTENSIBL       BYTES       MAXBYTES      
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.

Rating

  (2 ratings)

Comments

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:

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/physical-database-limits.html#GUID-939CB455-783E-458A-A2E8-81172B990FE9

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.

Regards,
Nitin
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
from
( select tablespace_name, sum(tot_free) tot_free
  from
  ( 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

Administration

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