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:
CODE
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:
CODE
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):
CODE
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:
CODE
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]
autogrow tablespace vs auto extend a datafile