-
I am having a pl/sql problem. Getting this error message:
declare
*
ERROR at line 1:
ORA-00937: not a single-group group function
ORA-06512: at line 3
ORA-06512: at line 14
pl/sql
declare
cursor cursor_rollbackmonitor_cache is
select class, count
from v\$waitstat
where class in ('free list','system undo header','system undo block',
'undo header','undo block');
any ideas?
"High Salaries = Happiness = Project Success."
-
Try this...
BEGIN
FOR CUR IN (select * from v$waitstat where class in ('free list','system undo header','system undo block', 'undo header','undo block'))
LOOP
DBMS_OUTPUT.PUT_LINE(CUR.CLASS ||' : '||CUR.COUNT);
END LOOP;
END;
Hope, it helps you
-
hi..it worked but i am getting bunch of other errors.
systemblock_count boolean;
*
ERROR at line 9:
ORA-06550: line 9, column 2:
PLS-00103: Encountered the symbol "SYSTEMBLOCK_COUNT"
ORA-06550: line 44, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
function package pragma procedure form
========pl/sql====================
begin
for cur in (select * from v$waitstat where class in ('free list','system undo he
ader','system undo block','undo header','undo block'))
loop
dbms_output.put_line(cur.class || ';'||cur.count);
end loop;
end;
systemblock_count boolean;
monitor_cache_rec cursor_rollbackmonitor_cache%rowType;
begin
open cursor_rollbackmonitor_cache;
freelist_count := false;
systemblock_count := false;
loop
fetch cursor_rollbackmonitor_cache into monitor_cache_rec;
exit when cursor_rollbackmonitor_cache%notFound;
if cursor_rollbackmonitor_cache%Found then
if ((monitor_cache_rec.class = 'free list') AND (monitor_cache_rec.count <= 10) ) then
freelist_count := true;
end if;
if ((monitor_cache_rec.class = 'system undo block') AND (monitor_cache_rec.count <= 10) ) then
systemblock_count := true;
end if;
end if;
end loop;
close cursor_rollbackmonitor_cache;
if ( (freelist_count) OR (systemblock_count) ) then
open cursor_rollbackmonitor_cache;
loop
fetch cursor_rollbackmonitor_cache into monitor_cache_rec;
exit when cursor_rollbackmonitor_cache%notFound;
if cursor_rollbackmonitor_cache%Found then
dbms_output.put_line( ' Count for '||monitor_cache_rec.class||' = '||to_char(monitor_cache_rec.count) );
end if;
end loop;
close cursor_rollbackmonitor_cache;
end if;
end;
/
spool off;
exit;
"High Salaries = Happiness = Project Success."
-
This will work for your requirement.....
DECLARE
systemblock_count boolean;
freelist_count boolean;
BEGIN
freelist_count := false;
systemblock_count := false;
FOR monitor_cache_rec IN (select * from v$waitstat where class in ('free list','system undo header','system undo block', 'undo header','undo block'))
LOOP
if ((monitor_cache_rec.class = 'free list') AND (monitor_cache_rec.count <= 10) ) then
freelist_count := true;
end if;
if ((monitor_cache_rec.class = 'system undo block') AND (monitor_cache_rec.count <= 10) ) then
systemblock_count := true;
end if;
END LOOP;
if ( (freelist_count) OR (systemblock_count) ) then
FOR monitor_cache_rec IN (select * from v$waitstat where class in ('free list','system undo header','system undo block', 'undo header','undo block'))
LOOP
dbms_output.put_line( ' Count for '||monitor_cache_rec.class||' = '||to_char(monitor_cache_rec.count) );
END LOOP;
end if;
END;
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|