f



Auto Datafile Creation - How to aktivate ?

How can I aktivate the automatical creation auf datafiles wenn the
tablespace is full ?

thx



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

Wie kann ich die automatische Erstellung von Datenfiles aktivieren,
wenn ter Tablespace voll l=E4uft ?

Danke f=FCr eure Hilfe.

0
8/21/2006 6:16:47 AM
comp.databases.oracle.server 22978 articles. 1 followers. Post Follow

8 Replies
37817 Views

Similar Articles

[PageSpeed] 42

ALTER DATABASE DATAFILE 'D:\USERS01.DBF' AUTOEXTEND ON


"oracle noob" <Armin.Kramer@gmail.com> wrote in message 
news:1156141007.409498.98170@m79g2000cwm.googlegroups.com...
How can I aktivate the automatical creation auf datafiles wenn the
tablespace is full ?

thx



------------------------------------------------------------------------------------------------------------------------------

Wie kann ich die automatische Erstellung von Datenfiles aktivieren,
wenn ter Tablespace voll l�uft ?

Danke f�r eure Hilfe.


0
none851 (51)
8/21/2006 6:36:24 AM

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.

0
8/21/2006 7:12:40 AM
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

0
eagle.f (21)
8/21/2006 8:27:35 AM
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


Why setting up a cron job when OEM can do it for you (by using events
and a fixit job) *for free*

-- 
Sybrand Bakker
Senior Oracle DBA

0
sybrandb1 (715)
8/21/2006 9:37:41 AM
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.
-- 
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
0
damorgan3 (6326)
8/21/2006 3:45:30 PM
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@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
0
dba (518)
8/21/2006 6:21:43 PM
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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
0
damorgan3 (6326)
8/21/2006 6:34:35 PM
oracle noob schreef:

> 
> The Datafile have to be smaler than 4,4 Gbyte.
> 

Oh? why is that? (Ignoring the fact 4096MB != 4.4GB)

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
0
8/21/2006 7:23:49 PM
Reply: