fitzjarrell_at_cox.net wrote:
> 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
Amen. If you give someone the ability to be lazy ... human nature is,
after all, human nature. If someone can't manage the space on a daily
or weekly basis they won't manage it on a monthly or yearly basis any
better.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sun Jan 16 2005 - 20:05:14 CST
