| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Autoextend
DA Morgan wrote:
> Jim Kennedy wrote:
>
> >>Hm - never had trouble with it, but yes, it could happen, when
> >>the chunks to grow with are large, and the (file-)system is
> >>heavily loaded. It is a point to be considered (isn't it all?)
> >>when using it; the size with with to grow should be reasonable.
> >>
> >>--
> >>Regards,
> >>Frank van Bortel
> >
> > But being slow is better than being down. (eg the tablespace is out
of room
> > vs the tablespace has to grow 250megs and takes 10 to 20 seconds)
> > Jim
>
> Not sure I agree.
>
> The difference is that if it goes down it is obvious the DBA has not
> been paying attention, not doing a good job, and needs some remedial
> education or a trip to the unemployment line to remind them why they
> are being paid. The "slows down" gives the incompetent DBA more paid
> hours to diagnose the issue and blame it on someone else.
>
> I vote to let the thing break and let management dump the offender on
> the other side of the door. There should be consequences for not
paying
> attention to tablespaces and alert logs.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)
>
>
> ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet
News==----
> http://www.newsfeeds.com The #1 Newsgroup Service in the World!
>100,000 Newsgroups
> ---= East/West-Coast Server Farms - Total Privacy via Encryption =---
I'll throw in my two cents on this. I was contracted to a major telecom a while back. One Friday I see email between the local office and the office in Mississippi detailing the results of an autoextend tablespace gone awry (the affected database was in the Mississippi office). It had run off the end of the disk, so to speak, taking with it currently processed transactions. The database was in such shape (and the DBA was in such hot water) it required an entire week to rebuild and reclaim that which was lost. The tablespaces were created autoextend to make managetment easier, which, of course, backfired on the DBA for not watching his media and for not limiting the size of the file. Subsequently NONE of the tablespaces in the new database were created autoextend.
Personally I have no trouble running a couple of queries to determine which objects are about to extend and whether or not they will fit in their assigned tablespaces. Since I am usually working on large databases I usually restrict the contents of the ts_extents table to objects occupying 10 meg or more of space:
rem
rem Create the data table for the instance extent data
rem
create table ts_extents
(OWNER VARCHAR2(30), SEGMENT_NAME VARCHAR2(81), SEGMENT_TYPE VARCHAR2(17), EXTENTS NUMBER, BYTES NUMBER, BLOCKS NUMBER, ACTUAL_BLOCKS NUMBER) tablespace tools STORAGE (initial 1M next 1M pctincrease 1) /
set echo on
insert into ts_extents
(owner,segment_name,segment_type,extents,bytes,blocks,actual_blocks)
select owner,segment_name,segment_type,extents,bytes,blocks,0
from sys.dba_segments
where segment_type = 'TABLE'
and bytes > 10245460
and segment_name not in ('TS_EXTENTS','DAILY_BLK_CNT')
/
select 'UPDATE ts_extents ' nl, 'SET actual_blocks= ( select
count(distinct
substr(dbms_rowid.rowid_to_restricted(rowid,0),1,8)||substr(dbms_rowid.rowid_to_restricted(rowid,0),15,3))
from '||owner||'.'||segment_name||') ' nl,'WHERE OWNER= '''||owner||'''
AND SEGMENT_NAME = '''||segment_name||''';' from ts_extents
/
/* objects that won't fit */
select b.segment_name segname,b.segment_type segtype,a.tablespace_name
tablespace_name,max(a.bytes) available_space, max(b.next_extent)
max_nex
from sys.dba_free_space a,sys.dba_segments b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name,b.segment_name,b.segment_type
having max(b.next_extent) > max(a.bytes)
order by 2 desc
/
/* block growth */
select a.owner owner,a.segment_name segment_name,a.segment_type
segment_type,a.blocks
blocks,decode(a.actual_blocks,0,1,a.actual_blocks)
act_blocks,decode(a.actual_blocks,0,1,a.actual_blocks)/a.blocks*100 pct
from ts_extents a,dba_tables b
where a.owner = b.owner
and a.segment_name = b.table_name
and decode(a.actual_blocks,0,1,a.actual_blocks)/a.blocks*100 > 90
order by pct desc
/
The above code is excerpted from a large script I run on a weekly basis to monitor databases under my control. Before the script is finished the ts_extents table is dropped to conserve resources in my TOOLS tablespace.
I also see no reason to use autoextend for anything but the SYSTEM tablespace.
David Fitzjarrell Received on Sun Jan 16 2005 - 15:49:07 CST
 |
 |