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%
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 # o
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 Re
On a side note I guess I could just look for a large change in space
allocation. My free space queries run pretty fast.
- Ethan
-Original Message-
Sent: Thursday, February 28, 2002 11:10 AM
To: '[EMAIL PROTECTED]'
Well I will need those included. I want to configure an alert in my
mo
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
-
Well I will need those included. I want to configure an alert in my
monitoring tool that will pick up the rapid allocation of extents and notify
me in case it happens to be a runaway job. I have a lot of objects in one
particular database and the sum(extents) from dba_segments is taking 30-60
se
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 2
How about counting rows from uet$? I have not tried it.
Raj
"Post, Ethan"
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)
FROMsys.dba_t
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)
FROMsys.dba_t
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, d
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
Fa
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 the
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,
>
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
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 t.ta
16 matches
Mail list logo