That's what I thought too, but it will skip extents from any LMTs in use.
And getting extents info when LMTs are is use will be slower as compared to
DMTs due the way this info is stored in the bitmap in each datafile for the
LMT.
- Kirti
-Original Message-
Sent: Wednesday, February
dba_segments is a view on sys_dba_segs which is:
all normal segments
union all
all temp segs
union all
all rollback segs
the last two of which we rarely care about when it
comes to checking space etc. You can get (some) gains
by creating your own view on just the normal segs
hth
connor
---
Kirti,
In LMT you can query the X$KTFBUE which is roughly equivalent to UET$ in
DMTs.
select v.name FILE NAME,count(x.KTFBUEFNO) TOTAL # of EXTENTS
from V$datafile v, X$KTFBUE x
where v.file#=X.ktfbuefno
group by v.name;
Ethan, Is this what you are looking for or something else?
Best
Gopal,
Thanks for the info..
- Kirti
-Original Message-
Sent: Thursday, February 28, 2002 11:53 AM
To: Multiple recipients of list ORACLE-L
Kirti,
In LMT you can query the X$KTFBUE which is roughly equivalent to UET$ in
DMTs.
select v.name FILE NAME,count(x.KTFBUEFNO) TOTAL # of
Thanks Connor, that is a lot faster. I think I will go with the idea of
just watching for any dramatic drops in DBA_FREE_SPACE. I have everything
tied down pretty tight but if a single object on a near empty tablespace
started to grow uncontrollably I wouldn't pick it up till tablespace hit 75%
How about counting rows from uet$? I have not tried it.
Raj
Post, Ethan
yep that works -- that is the total ALLOCATED extents in the
tablespace.
--- Viraj Luthra [EMAIL PROTECTED] wrote:
Could I use the following query to get the total used extents :-
SELECT t.tablespace_name, t.initial_extent,
t.next_extent, t.min_extents, t.max_extents,
if you use count, then you need to replace dba_segments with
dba_extents
each row in dba_segments has a column called extents which is the
total number of extents allocated to that segment.
there is one row in dba_extents for each allocated extent for a
segment.
either way will work, but
Errata :
Change the last line of the query to
group by a.tablespace_name
Sorry about that !!
Samir
Samir Sarkar
Oracle DBA - Lennon Team
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6217
EPABX : +44 (0) 115 - 957 6418 Ext. 76217
This query should give u the total number of used extents in a tablespace as
well
as the number of used blocks and bytes :
select a.tablespace_name, NVL(count(extent_id),0) Used Extents,
sum(NVL(bytes,0)) Bytes Used,
sum(NVL(blocks,0)) Blocks Used
from dba_tablespaces a,
Hello All,
Thanks for your responses. I am listing the query, if any one else wants to use the
query :-
SELECT t.tablespace_name, t.initial_extent,
t.next_extent, t.min_extents, t.max_extents,
t.pct_increase, status, contents, nvl(sum(extents),0)
FROM
Could I use the following query to get the total used extents :-
SELECT t.tablespace_name, t.initial_extent,
t.next_extent, t.min_extents, t.max_extents,
t.pct_increase, status, contents, sum(extents)
FROMsys.dba_tablespaces t, sys.dba_segments s
where
Hi,
I'd use count i.s.o. sum if you want the number of extents
Jack
Viraj Luthra [EMAIL PROTECTED]@fatcity.com on 31-01-2002 08:05:19
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:(bcc: Jack van
13 matches
Mail list logo
