FAQ
Hello Everyone:

Thanks to all who have responded to my question about logging. That got me to the problematic SQL. This SQL,
on just one instance, hangs and does not complete. I have let it run for hours to no avail. When I run this
query at SQL*Plus I get...

'OKAY'
---------------------------------
Okay

1 row selected.
Elapsed: 00:00:00.84

This query runs on all of the other instances without an issue. This is a 9.2.0.4 database, running on HP-UX. I am
running perl "v5.8.4 built for PA-RISC2.0-LP64", with DBI v1.46 and DBD::Oracle v1.16.

Here is my code. Help!
# -------------------------------------------------------------------------
# DB Extent growth check
# -------------------------------------------------------------------------
$SQLstmt =
qq{ select 'Okay' from dual
where not exists
( select 'x' from sys.dba_segments t1, extents_history t2
where (t1.owner = t2.owner) and
(t1.segment_name = t2.segment_name) and
(t1.segment_type = t2.segment_type) and
(t1.segment_type not like '%TEMP%') and
(t1.segment_type not like '%ROLLBACK%') and
(t1.segment_type not like '%UNDO%') and
((t1.extents - t2.extents) > :Threshold) )
union all
select 'Extent Growth Above Threshold (${DbExtGrowthThreshold})' from dual
where exists
( select 'x' from sys.dba_segments t1, extents_history t2
where (t1.owner = t2.owner) and
(t1.segment_name = t2.segment_name) and
(t1.segment_type = t2.segment_type) and
(t1.segment_type not like '%TEMP%') and
(t1.segment_type not like '%ROLLBACK%') and
(t1.segment_type not like '%UNDO%') and
((t1.extents - t2.extents) > :Threshold) ) };
$SQLh = $DBh->prepare($SQLstmt,{ora_check_sql => 0 });
$SQLh->bind_param( ":Threshold", $DbExtGrowthThreshold );
$SQLh->execute;
$SQLh->bind_col( 1, \$DbSegments );
$SQLh->fetch;
$SQLh->finish;
print STDOUT "DB Extent Growth....: $DbSegments \n";
print LOGF "DB Extent Growth....: $DbSegments \n";
#

Thanks,
Mike
________________________________

Michael P. Vergara Be good and you will be lonesome
Oracle Database Administrator Mark Twain
(951) 914-2000 (Voice)
(951) 914-2990 (FAX)
www.guidant.com <http://www.guidant.com/>

Any views expressed herein are not necessarily those of Guidant Corporation.

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 4 | next ›
Discussion Overview
groupdbi-users @
categoriesperl
postedNov 9, '05 at 9:21p
activeNov 10, '05 at 3:37p
posts4
users3
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase