Oracle Interview Questions - Datafile Autoextend feature

This post aims to provide basic oracle database interview questions for the Datafile Autoextend feature.

1. How to know if a tablespace has one or more datafiles with autoextend on?

If the Autoextensible column in the below query returns “YES” for a tablespace,then the Tablespace has one or more datafiles with Autoextend on.

sql> select tablespace_name,decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext from dba_data_files group by tablespace_name;

2. How to enable / disable autoextend for a specific datafile?

Use the below query to enable or disable autoextend for a specific datafile:

sql> alter database datafile '<file_name'> autoextend on | off;</file_name'>

3. To what extent could a datafile extend?

There are 3 limitations that could stop a datafile from extending

  • MAXSIZE specified when creating / altering the datafile.
  • DB restriction.
  • OS restriction.

4. When does a datafile extend?

By default a datafile extension is triggered by a space request, if the request would not be fulfilled by space already available then the datafile is automatically extended. From 11g this behavior could be changed as new feature “Tablespace-level space (Extent) pre-allocation” managed by SMCO background process is introduced. When this feature is enabled the datafile is extended before being full according to its history. The feature is controlled by the parameter “_ENABLE_SPACE_PREALLOCATION


5. Autoextend Known issues and bugs

- Queries on dba_free_space will be blocked while a datafile is automatically extended:

The process performing autoextend needs an exclusive pin to be acquired during the entire autoextension period. So, while autoextend is in progress and another process executing a query on dba_free_space, it needs to read the file header block in current mode and hence needs to wait for the pin.