autogrow tablespace vs auto extend a datafile 2

[ptheriault]

I am new to the Oracle world and I have a question which might seem stupid. I have db that has one user table space that I believe is set to autogrow, however all of the datafiles withing the tablespace are not set to auto extend. Do I need to auto extend the datafiles as well? How can I confirm that the tablespace is in fact set to autogrow.
Thanks

- Paul
- Database performance looks fine, it must be the Network!

 

[SantaMufasa]

Paul,

Could you please tell us about the functionality that you expect from AUTOGROW that you do not receive from AUTOEXTEND?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ptheriault]

Dave,
I had a process fail last night because the datafile within the tablespace was full. When I looked at the datafile I noticed that auto extent was set to 'NO' I guess my question is if this was set to YES would it just have added more extents to the data file and kept on processing? I still very new to Oracle and trying to figure these things out. I also checked the quota for the login. It is set to -1 which as I understand is unlimited. At first I thought that the user might have used up all of its quota.

- Paul
- Database performance looks fine, it must be the Network!

 

[karluk]

What reason do you have to think your tablespace is "autogrow". I've never heard of this term connected to Oracle, although I believe that it it used in the SQL Server world. The standard way of providing for automatic growth in Oracle is to set the individual data files to "auto extend" which, as you've already verified, is not set on this tablespace's data files.

 

[ptheriault]

So if I understand you, I can't set the tablespace to autogrow. I should set the datafiles inside the tablespace to auto extend? Is there any negative to auto extend? Would it be better for me to manually grow the data files?

- Paul
- Database performance looks fine, it must be the Network!

 

[SantaMufasa]

Paul, Karl is correct. The method by which we provide automatic allocation of space on an as-needed basis in Oracle is the AUTOEXTEND feature.

You (as a DBA) can make any of your existing, non-autoextending database datafiles AUTOEXTEND by executing a command similiar to this actual command:

ALTER DATABASE DATAFILE 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF'
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Database altered.

I, personally, prefer a 10-megabyte initial size, a 10-megabyte increment value, and a 2GB maximum size (since some operating systems have a very real problem with files that exceed 2GB).

You can find the names of your database datafiles by:

select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ -------------------------------------------------
TEMP                           D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF
SYSTEM                         D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF
RBS                            D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF
DATA1                          D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA101.DBF
DATA2                          D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF

You can designate a specific file to AUTOEXTEND when you create the tablespace. This is how I do it (including the optional [but very nice] specification for local extent management):

CREATE TABLESPACE <ts-name> DATAFILE '<fully qualified file-name>' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE 200M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

If your existing datafiles approach their 2GB maximums, you can add another autoextending datafile:

ALTER TABLESPACE <ts-name> ADD DATAFILE '<fully qualified file-name>' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

Notice that when altering a tablespace to add a datafile, you do not (re)specify the "EXTENT MANAGEMENT..." clause.

Let us know if this provides you with the insight you need/want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ptheriault]

Karl and Dave,
Excellent stuff. Thank you both for your reponse.

- Paul
- Database performance looks fine, it must be the Network!