| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Win2000/8.1.7.3.0/SQL
Michael,
Not that this has anything to do with your specific question...
but...looking at the SQL statement you posted, I don't think it needs the
table with the alias b. It already has the information from the inline
view. Also, the object's pctincrease isn't used in the calculation although
it's printed, and although the sql uses an outer join with dba_free_space
(in case there is no free_space) it doesn't nvl the maxbytes column.
I think the SQL should be something like this
SELECT d.tablespace_name,owner,segment_name,
segment_type,next_extent/1048576 next_ext FROM dba_segments d,
(SELECT tablespace_name, max(bytes) maxbytes
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE d.tablespace_name = fs.tablespace_name (+) and
next_extent*(1 + pct_increase/100) > nvl(fs.maxbytes,0)
ORDER BY ext_extent desc, owner, segment_name
BTW, will local managed tablespaces have this type of problem of a segment not being able to acquire a next extent? How does one monitor local tbs?
Chaim
"Vergara, Michael (TEM)" <mvergara_at_guidant.com>@fatcity.com on 08/19/2002 06:23:34 PM
Please respond to ORACLE-L_at_fatcity.com
Sent by: root_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:
Hi Gurus:
I have this SQL, see...
col segment_name format a30 heading "Segment Name" col maxseg format 9,999,999.9 heading "Largest|Segment (MB)" col segment_type format a15 heading "Segment|Type" col pct_increase format 999 heading "Pct|Incr" col next_ext format 9,999,999.9 heading "Next|Extent (MB)" col owner format a12
prompt Generating First Troubled Objects Listing...
select a.owner, a.tablespace_name,
a.segment_name, a.segment_type,
a.next_extent/1048576 next_ext, a.pct_increase,
max(b.bytes)/1048576 MAXSEG
from sys.dba_segments a,
sys.dba_free_space b,
(select max(fs.bytes) maxbytes,
fs.tablespace_name tsname
from sys.dba_free_space fs
group by fs.tablespace_name)
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = tsname (+)
and a.next_extent > maxbytes
group by a.owner, a.tablespace_name, a.segment_name,
a.segment_type, a.next_extent, a.pct_increase
order by a.next_extent desc, owner, segment_name
/
...and it works great everywhere. Well, almost everywhere, and there's the rub. Thie query works on my 7.3, 8.0, 8i, 8iR2, and most 8iR3 databases. In this one database, on Windows 2000, it blows out the TEMP tablespace. Every time. And this is the only system where that happens.
There are only 12 tablespaces, and 15 datafiles.
Any ideas why this would fill up the TEMP space?
Thanks,
Mike
--- =========================================================================== Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: mvergara_at_guidant.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Tue Aug 20 2002 - 12:57:22 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Chaim.Katz_at_Completions.Bombardier.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |