SELECT * FROM v$log;

SELECT COUNT(*) FROM v$process;

SELECT COUNT(*) FROM v$session WHERE status='ACTIVE'

SELECT COUNT(*) FROM dba_locks WHERE blocking_others='Blocking';

SELECT COUNT(*) FROM dba_objects WHERE status='INVALID' AND owner IN ('ABC','DEF') GROUP BY owner;

SELECT A.Tablespace_Name,TRUNC((SUM(A.Tots)/1024/1024),3) Tot_Size,
TRUNC(((SUM(A.Tots)/1024/1024)-(SUM(A.Sumb)/1024/1024)),3) Tot_Used
FROM (
SELECT Tablespace_Name,0 Tots,SUM(Bytes) Sumb,
MAX(Bytes) Largest,COUNT(*) Chunks
FROM Sys.Dba_Free_Space A
GROUP BY Tablespace_Name
UNION
SELECT Tablespace_Name,SUM(Bytes) Tots,0,0,0
FROM Sys.Dba_Data_Files
GROUP BY Tablespace_Name) A, V$instance B
GROUP BY A.Tablespace_Name
ORDER BY A.Tablespace_Name

SELECT (1-(SUM(DECODE(name,'physical reads', value, 0))/
(SUM(DECODE(name,'db block gets', value, 0)) +
SUM(DECODE(name,'consistent gets', value, 0))))) * 100 "Read Hit Ratio"
FROM v$sysstat

SELECT d.status "Status",
d.tablespace_name "Name",
d.CONTENTS "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
TO_CHAR(NVL(NVL(f.bytes, 0), 0)/1024/1024 ,'99G999G990D900') "Free (MB)",
TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %"
FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.CONTENTS "Type", d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
TO_CHAR(NVL((a.bytes-t.bytes), a.bytes)/1024/1024,'99G999G990D900') "Free (MB)",
TO_CHAR(NVL((a.bytes-t.bytes) / a.bytes * 100, 100), '990D00') "Free %"
FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes_cached) bytes
FROM v$temp_extent_pool GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY'
ORDER BY 7

SELECT COUNT(*) FROM tab

posted by Jaswinder Singh @ 2:04 AM

1 Comments:

At Tuesday, December 22, 2009, multivitamine said…

The main goal of the project is to create a distributed generic system collecting and storing various runtime metrics collections used for continuous system performance, health, quality and availability monitoring purposes. Allmon agents are designed to harvest a range of metrics values coming from many areas of monitored infrastructure. Collected data are base for quantitative and qualitative performance and availability analysis. Allmon collaborates with other analytical tools for OLAP analysis and Data Mining processing.