Donald K. Burleson Oracle Tips

Auto-extending an Oracle tablespace

Inside Oracle, the table and tablespaces can now be made to extended on an as needed basis. This is a great feature for the Oracle DBA because they no longer need to constantly monitor table extents and the free space within each tablespace.  By allowing automatic extending, the only possible source of an outage is when the filesystem becomes full.

Of course, in a production environment, an automatic extend of a tablespace can cause a performance problem, so senior DBAs will set AUTOEXTEND on as insurance, and continue to manually extend data files during off-peak hours.

To automatically extend a data files, the following command is used

   alter database

   datafile

      ‘/uor/oradata/prod/customer.dbf’

   resize 800m;

To allow a table to extend indefinitely:

   Alter table customer storage (maxextents unlimited);

To make a tablespace extend indefinitely, the following syntax is used:

      Create tablespace

         Customer_ts

      Datafile

         ‘/u01/oradata/prod/customer.dbf’'

      size

         300M

      AUTOEXTEND ON

         next 30M 

      MAXSIZE UNLIMITED;

Remember, if you choose to allow object to extend indefinitely, you must constantly monitor your UNIX mount points to ensure that the filesystem does not become full.  This is done with the “df –k” or “bdf” commands.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

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