Oracle table autoextend

For Oracle support & training call (800) 766-1884

Oracle Tips

Donald K. Burleson

Way back in version 7, Oracle introduced the concept of auto-extendable datafiles. This simple addition to Oracle has silenced many a DBA’s pager. It basically allows Oracle to automatically grow a datafile to meet the need of incoming or changed data if not enough free space currently exists in the tablespace.

To enable this feature, you can either create a tablespace with autoextend enabled or alter a tablespace after creation to turn the feature on. An example of creating a tablespace with autoextend initially enabled would be:

create tablespace users
datafile

'd:\oracle\ora92\o92\users01.dbf' size 25600k

autoextendon next 1280k maxsize unlimited
extent management local autoallocate
logging
online;

Some DBAs have an aversion to using autoextendand instead prefer to preallocate space to a tablespace. If proper capacity planning measures are used, this approach can work just fine.

However, if the database is very dynamic and unpredictable, then autoextend should be enabled for most tablespaces, especially temporary tablespaces that can be the object of large sort operations.

Some DBAs may not know whether autoextend is enabled for their tablespaces and datafiles. Furthermore, they may not know how much total space their storage structures are currently taking up. Depending on the Oracle version you are using, one of the two following scripts can give you these exact facts.

For DBAs using Oracle7 – Oracle 8.0, the spacesum7.sqlscript can be used:

See Code depot for complete script

spacesum7.sql

select
tablespace_name,
. . .

          nvl (round ((100 * sum_free_blocks /
                     sum_alloc_blocks),2),0)) || '%'
       as pct_free
from
. . .

           sys.dba_data_files a,
               sys.filext$ b
          where
               b.file# (+)= a.file_id
         group by tablespace_name),
       (select
               b.tablespace_name fs_ts_name,
               nvl (sum (bytes), 0) as total_free_space,
               sum (blocks) as sum_free_blocks
          from
               dba_free_space a,
               dba_tablespaces b
         where
. . . .

If Oracle8i and above is being used, then the spacesum8i.sql script will be necessary.

See Code depot for complete script

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:

http://www.rampant-books.com/book_2003_1_perf.htm

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot.

Fast-Track Oracle Support
PO Box 511
Kittrell, NC 27544

Email BC: