DBA Tips Archive for Oracle


Resizing Datafiles in Oracle 7.2 and Above

by Jeff Hunter, Sr. Database Administrator

Manually Resizing Datafiles

As of Oracle 7.2, existing datafiles can be resized via the alter database and alter tablespace command. To manually extend a datafile, use the alter database comment, as show in the following example:

    alter database
    datafile 'D:\ORANT\DEV\DATAFILES\USER1.ORA' resize 350M;

After the alter database command is executed, the datafile (in the above example), would be resized to 350M in size. If the datafile had been more than 350M in size, it will be decreased in size to 350M.

To resize a datafile using the alter tablespace command, you must specify the name of the tablespace to which the datafile belongs. Here is an example using the alter tablespace command:

    alter tablespace USER_DATA
    datafile 'D:\ORANT\DEV\DATAFILES\USER1.ORA' resize 350M;
Automatically Extending Datafiles

As of Oracle 7.2, you can specify parameters when creating datafiles that will automatically extend datafiles whenever their current allocated length is exceeded. Here are the three sizing parameters used when creating datafiles:

autoextend A flag that can be set to ON or OFF to indicate if the datafile should be allowed to automatically extend. If this parameter is set to OFF, the other sizing parameters (below) will be set to zero.
next size The size, in bytes, of the area of disk space to allocate to the datafile when more space is required. You may qualify the size value with 'K' and 'M' for kilobytes and megabytes respectively.
maxsize size The maximum size, in bytes, to which the datafile should be allowed to extend. You may qualify the size value with 'K' and 'M' for kilobytes and megabytes respectively.

Here is a small example:

    CREATE TABLESPACE user_data2
    NEXT 100M
    MAXSIZE 900M;
Querying the Data Dictionary

The determine if AUTOEXTEND has been configured for a paritcular data file, query the view DBA_DATA_FILES:

        file_name                "File Name"
      , autoextensible           "Autoextensible?"
      , increment_by * p.value   "Increment By"
      , maxbytes                 "Max Bytes"
        dba_data_files d
      , (SELECT value
         FROM v$parameter
         WHERE name = 'db_block_size') p
        file_name = 'D:\ORANT\DEV\DATAFILES\USER1.ORA'

    File Name                         Autoextensible? Increment By    Max Bytes
    --------------------------------- --------------- ------------ ------------
    D:\ORANT\DEV\DATAFILES\USER1.ORA  YES              104,857,600  943,718,400

The ability to enlarge files was introduced in 7.2 but querying the data dictionary to determine if a datafile has "autoextended" its original size is very different between release 7.2 and 8.x (and higher).

With release 7.2, you had to query the "sys.filext$" internal view. Here is a description of that table back in release 7.2:

    Name                            Null?    Type
    ------------------------------- -------- ----
    FILE#                           NOT NULL NUMBER
    MAXEXTEND                       NOT NULL NUMBER
    INC                             NOT NULL NUMBER

         FILE#     : the datafile number as it appears in "V$DATAFILE".
         MAXEXTEND : the maximum size the file can grow to,
                     expressed in multiples of "DB_BLOCK_SIZE", not in bytes.
         INC       : the size of each increase in the datafile, also
                     in multiples of "DB_BLOCK_SIZE".

NOTE: To get the values in bytes, multiply each number by the "init.ora" parameter "DB_BLOCK_SIZE".

If a datafile in the database has never been created or altered to have "AUTOEXTEND" turned on, and the "sys.filext$" table is queried, the following error will be returned:

    SQL> 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, no datafiles have AUTOEXTEND capabilities.

Starting with Oracle8, the column "AUTOEXTENSIBLE" was added to DBA_DATA_FILES. AUTOEXTENSIBLE will have values 'YES' or 'NO' and it is only available in 8.x versions.

Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Wednesday, 20-Aug-2008 14:38:25 EDT
Page Count: 35443