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”
SQL> ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0; --Disabled
SQL> ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 3; --Enabled
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.