Re: Auto Datafile Creation - How to aktivate ?

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Mon, 21 Aug 2006 18:21:43 GMT
Message-ID: <J4D1oB.A3A@igsrsparc2.er.usgs.gov>

DA Morgan wrote:
> Eagle Fan wrote:

>> oracle noob wrote:
>>> I want to create a new data file automatical, not extend an old one.
>>>
>>> This is the DDL sql, we use
>>>
>>> ALTER TABLESPACE "USERS" ADD DATAFILE '/oradata/xyz/users01.dbf' SIZE
>>> 4194304K REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 4096M
>>>
>>>
>>> The Datafile have to be smaler than 4,4 Gbyte.
>>
>> You can add a cron job to check the tablespace status for every 5 or 15
>> minutes.
>>
>> You need to check the freespace of the tablespace, the free extent
>> fragmentation if you are using DMT, the max next extent size and
>> something else you concerned
>>
>> If you find the tablespace is "FULL" ( you define the "FULL" ), you can
>> automatically add DATAFILES to the tablespace

>
> I think it inappropriate to use a cron job for something that can and
> should be done entirely within the database and will work the same no
> matter the operating system and shell.
>
> DBMS_JOB or DBMS_SCHEDULER can easily be used to run a procedure to
> monitor usage and take the appropriate actions when required including
> notifying the DBA what actions were taken and keeping a log of run
> results in a queryable table.

I wouldn't even use DBMS_JOB or DBMS_SCHEDULER. In 10g, I'd use Manage Metrics in OEM and set up a response for "Tablespace Space Used (%)" metric. The response would be to add a new datafile.

But then again, I wouldn't even do this in an automated fashion at all. Maybe I'm paranoid, but I like to keep a tighter lid on my datafile growth. I don't want a rogue process filling up my tablespace...allocating a new datafile...and repeating until the disk volume is full.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Mon Aug 21 2006 - 13:21:43 CDT