Job Seekers   Employers
dbapool: Authors | Submissions | Contact Us
   Forgot password? | Sign up
  Home   Discussion Forum   Articles   Interview Questions   FAQs   Scripts   Rewards   Analyzer   White Papers   Blog   Certification   Downloads   Tools
   





By  


Digg! digg!     Print    email to friend Email to Friend

Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.




How to check whether auto extend is enabled on a datafile?

If you have space problem then it is useful to know if datafiles have autoextended their original sizes. Starting from Oracle release 7.2,datafiles were given the ability to extend automatically if needed. If the given datafile is unable to allocate the space needed, it can increase the size of the datafile to make space for objects to grow.

You can determine if a datafile has AUTOEXTEND enabled by querying the SYS table "FILEXT$".

This table has following field in it.

Name   Null? Type
FILE#    NUMBER
MAXEXTEND   NUMBER
INC     NUMBER


Here:
FILE# is the datafile number as it appears in "V$DATAFILE".

MAXEXTEND is the maximum size the file can grow to, expressed in multiples of "DB_BLOCK_SIZE", not in bytes.

INC is the size of each increase in the datafile, also in multiples of "DB_BLOCK_SIZE".

Examples:

SVRMGR> select * From sys.FILEXT$;

 

FILE# MAXEXTEND  INC
------ ---------- ----

0 rows selected.

This means all of the files have autoextend off.
Note: You will get the following error message if a datafile in the database has never been created or altered to have "AUTOEXTEND" turned on.

SVRMGR> select * from sys.filext$;

select * from sys.filext$ 
                        *

ORA-00942: table or view does not exist .

This is because "FILEXT$" only gets created the first time you enable the AUTOEXTEND characteristic for a datafile in the database. If "FILEXT$" does not exist means no datafiles have AUTOEXTEND capabilities.

You can enable Autoextend by the following command.

SVRMGR> alter database datafile '/u03/oradata/prod/user01.dbf' autoextend on;

Statement processed.

SVRMGR> select * From sys.FILEXT$;

FILE# MAXEXTEND  INC
 3 4194302 1

1 row selected.

This shows that file #3 has autoextend set to on.

You can query from v$datafile for this file # to check the file.
In the similar way you can turn Autoextend off by the following command.

SVRMGR> alter database datafile '/u02/oradata/prod/user01.dbf' autoextend off;

Statement processed.

This disables autoextend

SVRMGR> select * From sys.FILEXT$;

FILE# MAXEXTEND  INC
 --------- --------- ---------

0 rows selected.

This shows that none of the files have autoextend set to ON.

In Oracle 8.x you can query column AUTOEXTENSIBLE in DBA_DATA_FILES
For example.

Select file_name, AUTOEXTENSIBLE from dba_data_files;

/u03/oradata/prod/system01.dbf                           NO

/u03/oradata/prod/index01.dbf                            NO

/u04/oradata/prod/temp01.dbf                            NO

/u04/oradata/prod/users01.dbf                           YES

/u04/oradata/prod/tools01.dbf                            YES

AUTOEXTENSIBLE will have values 'YES' or 'NO' and it is only available in 8.x versions.

 

 
About author:

 




  About Us Advertise Terms of Use Privacy Newsletters Contact Us    

Home   Discussion Forum   FAQs  Articles  Jobs   Newsletters  Directory  Downloads 

Our Premium Sponsor