Re: Rollback Segment Trouble

From: Yves Noel <noel_at_omega.univ-lille1.fr>
Date: 24 May 1994 07:52:29 GMT
Message-ID: <2rsbnt$635_at_netserver.univ-lille1.fr>

In article <Cq31Ds.5zE_at_scu.edu.au>, jzentvel_at_scu.edu.au (John Zentveld) writes:
|>From article <shatzmanCq0nDD.83q_at_netcom.com>, by shatzman_at_netcom.com (bs):
|>>
 

 .
|>> Barry
|>
|> With this talk about maxextents I was woundering if someone could
|>tell me how to find out how many extents are currently being used for a
|>normal data/index tablespace.
|>Ive just had a prob with exceding the max no of extents on a tablespace
|> (ie >121 extents ). but I would like to be proactive about this so it wont
|>happen again.
|>Ive been looking at dba_extents for that tablespace but I couldnt see where
|>121 figured into it.
|>
|>So could someone please send me a script to do this or a direction of where
|>I should be looking.
|>
|>Thanks,
|>
|> John.
|>--
|>John Zentveld Internet: jzentvel_at_scu.edu.au
|>Southern Cross University Phone (066) 20 3219
|>Lismore NSW Australia
|>

Here is the script I use :

col tablespace_name for a20
col segment_name for a20
col nbre hea 'NBRE|EXTENTS'
select tablespace_name,

       segment_type,
       segment_name,
       count(*) nbre

from sys.dba_extents
group by segment_name,

         segment_type,
         tablespace_name

having count(*) > 1
order by 1, 3, 4;

Hope this help.

-- 
Yves NOEL ______________________________________________ C.I.T.I. (batiment M4)
Database Administrator ________________ Universite des Sciences et Technologies
Email: Yves.Noel_at_univ-lille1.fr ________ 59655 Villeneuve d'Ascq Cedex - FRANCE
Voice: (33) 20.33.70.74 _________________________________ Fax: (33) 20.43.66.25

Received on Tue May 24 1994 - 09:52:29 CEST