topic Re: Adding Datafiles Automatically in Operating System - HP-UX

Adding Datafiles Automatically

Sunny_15 — Mon, 15 Sep 2003 18:39:17 GMT

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

Re: Adding Datafiles Automatically

Indira Aramandla — Mon, 15 Sep 2003 23:28:39 GMT

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

Re: Adding Datafiles Automatically

V.Tamilvanan — Tue, 16 Sep 2003 00:57:03 GMT

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

Re: Adding Datafiles Automatically

T G Manikandan — Tue, 16 Sep 2003 02:44:30 GMT

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

Re: Adding Datafiles Automatically

Yogeeraj_1 — Tue, 16 Sep 2003 03:53:28 GMT

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

Re: Adding Datafiles Automatically

Sunny_15 — Tue, 16 Sep 2003 13:36:29 GMT

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

Re: Adding Datafiles Automatically

Michael Schulte zur Sur — Tue, 16 Sep 2003 17:41:12 GMT

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

Re: Adding Datafiles Automatically

Sunny_15 — Tue, 16 Sep 2003 17:50:10 GMT

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