Adding Datafiles Automatically

[Sunny_15]

I am using Oracle 8.1.7 on Windows 2000. Can I add datafiles automatically when all the existing datafiles get used. Do I have to set any parameter for this. If yes, please give the procedure.

Thanks,

Sunny

[Indira Aramandla]

Hi Sunny,

When the existing datafile (tablespace) gets to 90% full and if it is likely to increase in data then you have to plan to increase the tablespace by either resizing or adding a datafile to the tablespace.

You choose to resize (increase) an existing tablespace or add another datafile depending on the current datafile size.

The command to increase (resize) an existing datafile is

Alter database datafile 'name in full path' resize ??M;

The command to add a datafile to an existing tablespace is

Alter tablespace add datafile 'name in full path' size ??M;

I hope this is what you were looking for.

IA

[V.Tamilvanan]

Hi,
You can enable the auto extend option of your datafile. So whenever the data reaches the end of the file it automatically extends it.
The syntax is :
ALTER DATABASE DATAFILE 'D:\orant\database\Sys1ORCL.ora' AUTOEXTEND ON;
You can have a look at :

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/dfiles.htm#484

HTH

[T G Manikandan]

alter database datafile '//users.dbf' autoextend on;

[Yogeeraj_1]

hi,

I would turn on the AUTOEXTEND feature and make optimal use of space by using A locally managed tablespace with uniform extents.

You can set it up by object size -- setting up extent sizes for "small" "med" and "large". You would want all segments to be placed in a tablespace such that they would never have more then say 1,000 extents.

So, maybe for you if you have some really small tables:

64KB extent size -- good for 64k to ~6MB, could goto ~60MB
512KB extent size -- good for 512k to ~50MB, could goto ~500MB
1MB extent size -- good for 1m to ~100MB, could goto ~1MB
5MB extent size -- good for 5m to ~500MB, could goto ~5MB

The added benefits that you will also get are:
- Locally managed tablespaces are much faster at allocating and de-allocating extents - many order of magnitudes faster.

- locally managed tablespaces decrease contention on the data dictionary.

E.g. For normal tablespaces
CREATE TABLESPACE LMT_SMALL
DATAFILE 'D:\ORACLE\ORADATA\MYDB\LMT_SMALL01.DBF' SIZE 92160 K
AUTOEXTEND ON NEXT 512 K
LOGGING
ONLINE
PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64 K


For temporary tablespace:
CREATE TEMPORARY TABLESPACE TEMP_LMT
TEMPFILE 'D:\ORACLE\ORADATA\MYDB\TEMPLMT01.DBF' SIZE 51200 K
AUTOEXTEND ON NEXT 640 K MAXSIZE 102400 K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64 K;

hope this helps!

regards
Yogeeraj

[Sunny_15]

Hi Indira, Tamil, Yogeeraj

Thanks for your suggestions. But I was looking for a way wherein the datafiles can be automatically added once the existing datafiles are used up. I want to restrict the size of the datafiles so cannot make them unlimited. So the autoextend usage is limited.

For eg. If there are 10 datafiles and the 11th datafile should get automatically added (may be thru a script or setting a parameter) once the tablespace size reaches its max.

Thanks,

Sunny

[Michael Schulte zur Sur · Accepted solution]

Hi Sunny,

as you have noticed by now, there is no automatism by the database to handle this for you. You will have to take it into your own hands(crontab). Roughly you will have to do the following steps.

1. find out, how much is left
select sum(bytes) from dba_free_space where tablespace_name='BLABLA';

2. find out, now many datafiles you already have.
select count(*) from dba_data_files where tablespace_name='BLABLA';

3. add one to number, construct new datafile name

4. add datafile using whatever size you want.
alter tablespace blabla add datafile 'newdatafilename' size 10m;

is that sufficient for you to get the job done?

Michael

[Sunny_15]

Hi Michael,

Thanks for the answer. I too thought that there is no automatic way of doing it. Just wanted to make it sure.

Thanks again !!!

Sunny