Adding Data Files automatically

Hi!
Is there any method of adding a new datafile to a tablespace when a existing datafile is full? I know there is a mehtod to auto incrementing or resizing the Datafiles, but the problem is very large file size can pose a problem at OS level.

Thanks in Advance

you would have to write your own script

Not AFAIK, there is no Oracle provided method. This is where the "A" in DBA comes from.

My suggestion would be setup some monitoring scripts to tell you when you are almost full on a data file and then send yourself an email every morning indicating which tablespaces may be running out. If you want to get fancy, when your script recognizes you need a new datafile, you can automatically add it to your tablespace via some code.

Personally, I like the human intervention so I can alleviate any problem before it gets out of hand, but I'm also AR...

What will be the Triggering Event for the specific script. Will the script be a stored procedure or Shell Script?

Quote:

---

Originally posted by sudip
What will be the Triggering Event for the specific script.

---

Depends. Where would you add another data file?

Quote:

---

Will the script be a stored procedure or Shell Script?

---

Yes. (you could do it either way)

Not until u migrate to 9i, which has this feature.

Till 8i, what u can do is , create a number of datafiles of minimum size say 10MB and set autoextend.
And setup some warning procedure when u r left with last two datafiles .

Is there a feature of automatically adding datafiles to a tablespace? Thats Cool.

Quote:

---

Originally posted by sudip
Is there a feature of automatically adding datafiles to a tablespace? Thats Cool.

---

No, there is no such feature....

what a danger, adding files automatically. it´s only good ONLY if you can drop a datafile from a tablespace which I have my doubts even in 9i

Quote:

---

Originally posted by pando
what a danger, adding files automatically. it´s only good ONLY if you can drop a datafile from a tablespace which I have my doubts even in 9i

---

You are right, it is not possible in 9i.

then it´s a danger!

Quote:

---

Originally posted by pando
then it´s a danger!

---

There is no danger, because there is no such feature as adding datafiles to a tablespace automaticaly, not even in 9i.

that´s fine then =)

Even in 9i you first have to determine where to make the files:

alter system set DB_CREATE_FILE_DEST='/oradata/9i/files';

Only then you can add files:

alter tablespace DBA_SUPPORT add datafile;