autoextend

[netbar]

I have a system which has had increased volume over past few days and volume is due to increase should I be autoextended my dbf's. I don't normally use this feature is it good practice and also are there any problems with autoextending if I have partitioned tables!!


Thanks

[adewri]

i personally vote for autoextend on. its irritating to see the error message "unable to allocate extents" when there is ample free space on the filesystem.

Others may share their point of view.

[marist89]

I always go with AUTOEXTEND on for my data tablespaces.

[DaPi]

My preference is not to autoextend, but to have the TS over-allocated by a lot (30% free) and check frequently - but then for my db the growth is predictable. Since none of us have an infinite number of disks (as recommended by Oracle) you do have to think what you are sharing the disk with. [blush] I once set TEMP to autoexetend, which it did, until there was no more room for archivelogs. [/blush]

[nagarjuna]

I like to use autoextend off for critical production databases. I dont want to see akward growth of datafiles. I believe in human touch..Monitor the space usage daily and Add the datafiles as needed.. but, then it depends on how many databases we have and amount of workload

set pages 100
BREAK ON REPORT
COMPUTE SUM OF BYTES USED FREE ON REPORT
column dummy noprint
column bytes format 9999999
column used format 9999999
column free format 9999999
column pct_used format 9999999
select a.tablespace_name name,
b.tablespace_name dummy,
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))/(1024*1024) bytes,
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))/(1024*1024) -
(sum(a.bytes)/count( distinct b.file_id ))/(1024*1024) used,
(sum(a.bytes)/count( distinct b.file_id ))/(1024*1024) free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name
/

[marist89]

Yes, you have to be carefull of where you set AUTOEXTEND ON. AUTOEXTEND with no MAXSIZE can be problematic as it can eat up your disk space really quickly for something like a TEMP or RBS tablespace.

However, I am still a firm believer in maintaining my level of service with the least amount of effort expended. AUTOEXTEND combined with MAXSIZE is one of those Oracle features that allows me to do that. Space is one of those daily tasks that can take up lots of your time or very little (in my case, none) of your time. If somebody starts loading data at 03:00 and they run out of space at 04:00, do you really want to get a call to fix the problem? I don't. I would rather let the user load their data and wait until morning to politely request their authorization for $150K so I can buy a new Disk Array.

[sreddy]

I vote for Jeff. AUTOEXTEND with MAXSIZE would do the best for me, that way I can control my datafiles well for backups and moving around standby databses, rebuilding QA network over the time etc., Its just Flexibility...

[DaPi]

Originally posted by marist89
AUTOEXTEND combined with MAXSIZE

Sounds like that's it! Thanks.

[DaPi]

Originally posted by marist89
AUTOEXTEND combined with MAXSIZE

Upon reflection, I don't think this resolves all the worries.

Even with AUTOEXTEND you will still need to monitor file size as you approach MAXSIZE. Unless I've missed something, I don't think we're off that hook.

I apprecite Sam's point that AUTOEXTEND will elegantly avoid having to backup/copy a whole file just for the 50% of data in it - that is certainly a saving.