Re: Auto Datafile Creation - How to aktivate ?

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 21 Aug 2006 11:34:35 -0700
Message-ID: <1156185274.148867@bubbleator.drizzle.com>

Brian Peasland wrote:
> 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

I agree. I use DBMS_RESUMABLE and believe management is best done when it is hands-on and the DBA knows where everything is.

Alternatively I'd use the Grid Control and make sure I had built appropriate notification into the configuration.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Aug 21 2006 - 13:34:35 CDT