Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
cancel
Showing results for 
Search instead for 
Did you mean: 

Adding Datafiles Automatically

SOLVED
Go to solution
Sunny_15
Occasional Advisor

Adding Datafiles Automatically

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

7 REPLIES
Indira Aramandla
Honored Contributor

Re: Adding Datafiles Automatically

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

Never give up, Keep Trying
V.Tamilvanan
Honored Contributor

Re: Adding Datafiles Automatically

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
Honored Contributor

Re: Adding Datafiles Automatically

alter database datafile '//users.dbf' autoextend on;
Yogeeraj_1
Honored Contributor

Re: Adding Datafiles Automatically

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sunny_15
Occasional Advisor

Re: Adding Datafiles Automatically

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
Honored Contributor
Solution

Re: Adding Datafiles Automatically

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
Occasional Advisor

Re: Adding Datafiles Automatically

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