What queries are used by the oracle probe?

What queries are used by the oracle probe?

book

Article ID: 34647

calendar_today

Updated On:

Products

NIMSOFT PROBES DX Infrastructure Management

Issue/Introduction

What Queries are used by the Oracle Probe?

Environment

Release:

Component:

Resolution

Please see the queries in the attached file. The file contains all queries for all checkpoints. You need to find the query for the checkpoint you are interested in, copy into a text editor and remove the additional " symbols.

Oracle11 Checkpoint Queries

/*000*/ tablespace_free

SELECT d.tablespace_name as name, 
	NVL (dfs.BYTES, 0) AS freeSp,  
	NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100, 3), 0) AS avPct, 
	NVL (dfs.antall, 0) as chunks, 
	ddf.autoextend_flag as autoext, 
	NVL (TRUNC ((ddf.maxbytes)), 0) as maxSize, 
	NVL (TRUNC (  (ddf.BYTES - NVL (dfs.BYTES, 0))/(ddf.maxbytes)*100,3),0) as maxPct 
 FROM dba_tablespaces d, 
	(SELECT   tablespace_name, SUM (BYTES) BYTES, COUNT (1) antall FROM dba_free_space GROUP BY tablespace_name) dfs, 
	(SELECT   tablespace_name, SUM (BYTES) BYTES, SUM (GREATEST (maxbytes, BYTES)) maxbytes, COUNT (1) antall, MAX(autoextensible) autoextend_flag 
    		FROM dba_data_files GROUP BY tablespace_name) ddf 
		WHERE d.tablespace_name = ddf.tablespace_name(+) 
		AND d.tablespace_name = dfs.tablespace_name(+) 
		AND NOT (d.extent_management LIKE 'LOCAL' 
		AND d.CONTENTS LIKE 'TEMPORARY') 
UNION ALL 
SELECT LTRIM (d.tablespace_name) as name, 
	NVL (TRUNC (ddf.BYTES - NVL(dfs.BYTES, 0)), 0) AS freeSp, 
	NVL (TRUNC ((ddf.BYTES - NVL(dfs.BYTES, 0)) / ddf.BYTES * 100), 0) AS avPct, 
	DECODE (NVL (TRUNC (((ddf.BYTES - NVL(dfs.BYTES, 0)) / ddf.BYTES) * 100), 0),  0, 1,  100, 0,1) as chunks, 
	ddf.autoextend_flag as autoext, 
	NVL (TRUNC ((ddf.maxbytes)), 0) as maxSize, 
	NVL (TRUNC ((NVL(dfs.BYTES, 0)) / (ddf.maxbytes)* 100,3),0) as maxPct 
 FROM dba_tablespaces d, 
	(SELECT   tablespace_name, SUM (BYTES) BYTES, SUM (GREATEST (maxbytes, BYTES)) maxbytes, COUNT (1) antall, MAX(autoextensible) autoextend_flag 
              FROM dba_temp_files GROUP BY tablespace_name) ddf, 
	(SELECT   ss.tablespace_name, SUM ( ss.used_blocks * ts.BLOCKSIZE) BYTES, COUNT (1) antall 
              FROM gv$sort_segment ss, SYS.ts$ ts WHERE ss.tablespace_name = ts.NAME GROUP BY ss.tablespace_name) dfs 
	WHERE d.tablespace_name = ddf.tablespace_name(+) 
		AND d.tablespace_name = dfs.tablespace_name(+) 
		AND d.extent_management LIKE 'LOCAL' 
		AND d.CONTENTS LIKE 'TEMPORARY' 
ORDER BY 1

/*001*/ buf_cachehit_ratio

SELECT a.value as db_block_gets, 
	  		b.value as consistent_gets, 
	  		c.value as physical_reads, 
	  		d.value as pr_direct, 
	  		e.value as pr_direct_lob, 
	  		f.value as pr_direct_temp_space 
 FROM v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d, v$sysstat e, v$sysstat f 
WHERE a.name = 'db block gets' AND b.name = 'consistent gets' AND c.name = 'physical reads' AND d.name = 'physical reads direct' 
 AND e.name = 'physical reads direct (lob)' AND f.name = 'physical reads direct temporary tablespace'

/*002*/ dict_cachehit_ratio

 
SELECT /*+ RULE */(TRUNC(sum(gets))) as gets,(TRUNC(sum(getmisses))) as getmisses FROM v$rowcache

/*003*/ lib_cachehit_ratio

SELECT (TRUNC(sum(pins))) as pins,(TRUNC(sum(reloads))) as reloads FROM v$librarycache

/*004*/ memory_usage

SELECT (TRUNC(sum(se.value))) as value FROM v$sesstat se, v$statname n WHERE n.statistic#=se.statistic# AND n.name='session pga memory'

/*005*/ rollback_segments

SELECT name,(TRUNC(waits)) as waits,(TRUNC(gets)) as gets FROM v$rollstat, v$rollname WHERE v$rollstat.usn=v$rollname.usn

/*006*/ no_next_extents

SELECT owner,s.segment_name,s.segment_type,s.tablespace_name,s.next_extent,s.partition_name FROM   dba_segments s, dba_tablespaces d,(SELECT tablespace_name, sum(bytes) free_bytes FROM dba_free_space GROUP BY tablespace_name) f WHERE  f.tablespace_name(+) = s.tablespace_name AND s.tablespace_name NOT IN (SELECT o.tablespace_name FROM dba_data_files o WHERE o.autoextensible='YES') AND d.tablespace_name=s.tablespace_name AND d.allocation_type != 'SYSTEM' AND s.next_extent > nvl(f.free_bytes,0)

/*007*/ invalid_objects

SELECT owner,object_type,object_name,status, 1 as value FROM dba_objects WHERE status='INVALID' ORDER BY owner,object_type,object_name

/*008*/ chained_rows

SELECT owner,table_name,nvl(num_rows,0) as num_rows ,nvl(chain_cnt,0) as chain_cnt FROM all_tables WHERE owner NOT IN('SYS','SYSTEM') AND (num_rows > 0 OR chain_cnt > 0) ORDER BY owner,table_name

/*009*/ datafile_status

select t.name,h.bytes as used,h.status as chkstatus,d.status as fstatus, decode(h.status,'ONLINE',1,0) as status, d.name as path from v$datafile d, v$datafile_header h, v$tablespace t where d.file# = h.file# and d.ts# = t.ts#

/*010*/ login_count

SELECT COUNT(username) as login_cnt FROM v$session WHERE username NOT IN('SYS','SYSTEM')

/*011*/ enqueue_timeouts

SELECT value as enqueue_timeouts FROM v$sysstat WHERE name='enqueue timeouts'

/*012*/ redo_logs

SELECT name,gets,misses FROM v$latch WHERE name IN('redo allocation','redo copy')

/*013*/ mts_response

SELECT network,DECODE(SUM(totalq),0,0,SUM(wait)*10 / SUM(totalq)) as resp_time FROM v$queue q, v$dispatcher d WHERE q.type='DISPATCHER' AND q.paddr=d.paddr GROUP BY network

/*014*/ mts_wait

SELECT decode(totalq,0,0,wait*10/totalq) as wait_time FROM v$queue WHERE type='COMMON'

/*015*/ sga_memory

SELECT name, decode(pool, null, '(total)', '('||pool||')') as pool, bytes FROM v$sgastat where name != 'free memory'

/*016*/ sort_ratio

SELECT sort_mem.value as sort_memory ,sort_disk.value as sort_disk, sort_rows.value as sort_rows FROM v$sysstat sort_mem, v$sysstat sort_disk, v$sysstat sort_rows WHERE sort_mem.name='sorts (memory)' AND sort_disk.name='sorts (disk)' AND sort_rows.name='sorts (rows)'

/*017*/ index_status

SELECT owner,index_name,table_name,status FROM all_indexes WHERE status NOT IN('VALID', 'N/A')

/*018*/ tablespace_status

SELECT tablespace_name,status,decode(status,'ONLINE',1,0) as qstatus FROM dba_tablespaces

/*019*/ rollback_seg_status

SELECT segment_name,status,decode(status,'ONLINE',1,0) as qstatus FROM dba_rollback_segs

/*020*/ tablespace_deficit

SELECT s.segment_name,s.tablespace_name,s.next_extent,nvl(f.free_bytes,0) as free_bytes,s.owner,s.partition_name FROM dba_segments s,dba_tablespaces d,(SELECT tablespace_name,sum(bytes) free_bytes FROM dba_free_space GROUP BY tablespace_name) f WHERE f.tablespace_name(+) = s.tablespace_name AND s.tablespace_name NOT IN (SELECT o.tablespace_name FROM dba_data_files o WHERE o.autoextensible = 'YES') AND d.tablespace_name = s.tablespace_name AND d.allocation_type != 'SYSTEM' AND s.next_extent > nvl(f.free_bytes,0)

/*021*/ mts_uga_memory

SELECT SUM(value) memory FROM v$sesstat WHERE statistic#=20

/*022*/ user_locks

		 SELECT a.serial# as serial,a.sid,a.username,b.type,b.ctime,lmode,a.osuser,c.sql_text  
		 FROM v$session a,v$lock b, v$sqlarea c 
		   WHERE b.type in ('TM','TX','UL') and 
		   a.sid=b.sid and 
		   	lmode > 0 and 
		 	((a.PREV_HASH_VALUE = c.hash_value and 
		 	a.prev_sql_addr = c.address and 
		 	a.sql_hash_value = 0) or 
		 	(c.hash_value=a.sql_hash_value and c.address = a.sql_address))

/*023*/ locked_users

SELECT br.username username_h, br.SID sid_h, br.serial# serial_h, bd.username username_w, bd.SID sid_w, bd.serial# serial_w, bd.ctime, br.sql_text sql_text_h, bd.sql_text sql_text_w, br.osuser osuser_h, bd.osuser osuser_w 
FROM (SELECT distinct se.username, se.SID, se.serial#, txt.sql_text, se.osuser, lk.ctime 
FROM v$lock lk, (SELECT q.sql_text, lk.id1, lk.id2,s.sid FROM v$lock lk, v$session s, v$sql q WHERE s.lockwait IS NOT NULL AND s.lockwait = lk.kaddr AND s.sql_address = q.address AND s.sql_hash_value = q.hash_value) txt, v$session se 
WHERE (lk.TYPE = 'TX') AND (lk.SID = se.SID) AND (se.sid = txt.sid) AND (BLOCK = 0) AND (txt.id1 = lk.id1) AND (txt.id2 = lk.id2)) bd, (SELECT se.username, se.SID, se.serial#, txt.sql_text, se.osuser 
FROM v$lock lk, v$session se, (SELECT q.sql_text, s.SID FROM v$session s, v$sql q WHERE s.prev_sql_addr = q.address) txt WHERE (lk.TYPE = 'TX') AND (lk.SID = se.SID) AND (txt.SID = se.SID) AND (BLOCK = 1)) br order by bd.ctime  desc

/*024*/ buf_cachehit_ratio_users

SELECT s.sid,username,Consistent_Gets,Block_Gets,Physical_Reads FROM  v$session s, v$sess_io i WHERE s.sid=i.sid AND (Consistent_Gets+Block_Gets)>0 AND username IS NOT NULL

/*025*/ session_waits

SELECT class,count,time FROM v$waitstat

/*026*/ system_statistics

SELECT statistic# as statistic, name, class, value FROM v$sysstat

/*027*/ system_waits

SELECT event,total_waits,total_timeouts,time_waited,average_wait FROM v$system_event

/*028*/ dbfile_io

SELECT name,phyrds,pd.phys_reads,phywrts,pd.phys_wrts FROM (SELECT (SUM(phyrds)) phys_reads, (SUM(phywrts)) phys_wrts FROM v$filestat) pd, v$datafile df, v$filestat fs WHERE df.file# = fs.file#

/*029*/ tablespace_alloc_free

SELECT LTRIM (d.tablespace_name) as name,  
	NVL (ddf.BYTES - NVL(u.BYTES, 0), 0) as freeSp, 
	DECODE (d.CONTENTS, 'UNDO', NVL (TRUNC ((ddf.BYTES - NVL (u.BYTES, 0))/(ddf.bytes)*100,3),0), NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100,3), 0)) as avPct, 
	NVL (dfs.antall, 0) as chunks, 
	ddf.autoextend_flag as autoext, 
	NVL (TRUNC ((ddf.bytes)), 0) as maxSize, 
	NVL (TRUNC (  (ddf.BYTES - NVL (dfs.BYTES, 0))/(ddf.bytes)*100,3),0) as maxPct 
 FROM dba_tablespaces d, 
	(SELECT   tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) maxbytes, COUNT (1) antall FROM dba_free_space GROUP BY tablespace_name) dfs, 
	(SELECT   tablespace_name, SUM (BYTES) BYTES, SUM (maxbytes) maxbytes, COUNT (1) antall, DECODE (MAX (autoextensible),'YES', 'Y','N') autoextend_flag 
    		FROM dba_data_files GROUP BY tablespace_name) ddf, 
	(SELECT   tablespace_name, SUM (BYTES) BYTES FROM dba_undo_extents WHERE status <> ('EXPIRED') GROUP BY tablespace_name) u 
		WHERE d.tablespace_name = ddf.tablespace_name(+) 
		AND d.tablespace_name = dfs.tablespace_name(+) 
		AND d.tablespace_name = u.tablespace_name(+) 
		AND NOT (d.extent_management LIKE 'LOCAL' 
		AND d.CONTENTS LIKE 'TEMPORARY') 
UNION ALL 
SELECT LTRIM (d.tablespace_name) as name, 
	NVL (TRUNC (ddf.BYTES), 0) - NVL (TRUNC (dfs.BYTES), 0) as freeSp, 
	100 - NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100), 0) as avPct, 
	DECODE (NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100), 0),0, 1,100, 0) as chunks, 
	ddf.autoextend_flag as autoext, 
	NVL (TRUNC ((ddf.bytes)), 0) maxSize, 
	NVL (TRUNC (NVL (dfs.BYTES, 0) / (ddf.bytes)* 100,3),0) as maxPct 
 FROM dba_tablespaces d, 
	(SELECT   tablespace_name, SUM (BYTES) BYTES, SUM (maxbytes) maxbytes, COUNT (1) antall, DECODE (MAX (autoextensible), 'YES', 'Y','N') autoextend_flag 
              FROM dba_temp_files GROUP BY tablespace_name) ddf, 
	(SELECT   ss.tablespace_name, SUM ((ss.used_blocks * ts.BLOCKSIZE)) BYTES, MAX ((ss.used_blocks * ts.BLOCKSIZE)) maxbytes, COUNT (1) antall 
              FROM gv$sort_segment ss, SYS.ts$ ts WHERE ss.tablespace_name = ts.NAME GROUP BY ss.tablespace_name) dfs 
	WHERE d.tablespace_name = ddf.tablespace_name(+) 
		AND d.tablespace_name = dfs.tablespace_name(+) 
		AND d.extent_management LIKE 'LOCAL' 
		AND d.CONTENTS LIKE 'TEMPORARY' 
ORDER BY 1

/*030*/ sga_memory_free

SELECT name, decode(pool, null, '(total)', '('||pool||')') as pool, bytes FROM v$sgastat where name = 'free memory'

/*031*/ lock_waits

select s.serial# as serial,s.sid,s.username,s.osuser,e.event,e.total_waits,e.time_waited/100 as total_time_waited from v$session_event e, v$session s where e.sid = s.sid and e.event like 'enq:%' and s.user# <> 0

/*032*/ gc_service_util

select gets.value as gets, conv.value as converts, crb.value as cr_blocks_received,curb.value as current_blocks_received,dbbg.value as db_block_gets,cg.value as consistent_gets from 
v$sysstat gets, v$sysstat conv, v$sysstat crb, v$sysstat curb, v$sysstat dbbg, v$sysstat cg 
where gets.name='gc gets' and conv.name='gc converts'  and crb.name='gc cr blocks received' and curb.name='gc current blocks received' and dbbg.name='db block gets' and cg.name ='consistent gets'

/*033*/ gc_cr_timeouts

select value from v$sysstat where name='gc cr timeouts'

/*034*/ gc_convert_timeouts

select value from v$sysstat where name='gc convert timeouts'

/*035*/ gc_blocks_lost

select value from v$sysstat where name='gc blocks lost'

/*036*/ gc_blocks_corrupt

select value from v$sysstat where name='gc blocks corrupt'

/*037*/ gc_av_lock_get_time

select time.value * 10 as get_time, cnts.value as sync_gets, cnta.value as async_gets from v$sysstat time, v$sysstat cnts, v$sysstat cnta where time.name='global lock get time' and cnts.name='global lock sync gets' and cnta.name='global lock async gets'

/*038*/ gc_fusion_wrt_rto

select time.value as DBWR_fusion_writes, cnt.value as physical_writes from v$sysstat time, v$sysstat cnt where time.name='DBWR fusion writes' and cnt.name='physical writes'

/*039*/ long_queries

select  l.sid, l.username, u.process, u.osuser, l.opname, l.elapsed_seconds, l.time_remaining, substr(s.sql_text,1,250) as sql_text 
from v$session_longops l, v$sqlarea s, v$session u where s.hash_value=l.sql_hash_value and s.address = l.sql_address and l.sid = u.sid and l.sofar != l.totalwork

/*040*/ tablespace_size

SELECT tablespace_name, (SUM(bytes)/1024/1024) mbytes, (SUM(bytes)) bytes,  SUM(greatest(maxbytes,bytes)) maxbytes, DECODE(MAX(autoextensible),'YES','J','N') autoext 
FROM dba_data_files GROUP BY tablespace_name union ALL 
SELECT tablespace_name, (SUM(bytes)/1024/1024), (SUM(bytes)) bytes, SUM(greatest(maxbytes,bytes)), DECODE(MAX(autoextensible),'YES','J','N')  
FROM dba_temp_files GROUP BY tablespace_name

/*041*/ database_size

select (a.datasize+b.tempsize+c.logsize)/(1024 * 1024)  as mbtotalsize, a.datasize+b.tempsize+c.logsize as totalsize, a.datasize, b.tempsize, c.logsize from (select sum(bytes) datasize from dba_data_files) a , (select nvl(sum(bytes),0) tempsize from dba_temp_files) b, (select sum(bytes) logsize from v$log) c

/*042*/ resource_util

		   SELECT /*+ RULE */ resource_name, current_utilization, initial_allocation, (current_utilization*100)/initial_allocation util_pct
		   FROM (SELECT resource_name, current_utilization, initial_allocation
		   FROM v$resource_limit WHERE LOWER (resource_name) IN ('processes','sessions')
		   UNION
		   SELECT resource_name, (SELECT count(1) FROM v$transaction) current_utilization, initial_allocation
		   FROM v$resource_limit WHERE LOWER (resource_name) = ('transactions'))

/*043*/ "dataguard_status

        SELECT a.status , DECODE(a.status,'VALID',1,0) as status_num, decode(a.error,null,'NULL', a.error) as error, decode(a.fail_date,NULL,'NULL',to_char(a.fail_date,'YYYY/MM/DD HH24;MI;SS')) as fail_date,a.db_unique_name FROM V$ARCHIVE_DEST a WHERE a.status <> 'INACTIVE'and a.target = 'STANDBY'

/*044*/ "dataguard_gap

           SELECT /*+rule*/ 
           AD.DB_UNIQUE_NAME db_name, 
		   ARCH.DEST_ID, 
           ARCH.THREAD# Thread, 
		   ((PRIM.SEQUENCE#) - APPL.SEQUENCE#) gap_to_primary, 
		   (ARCH.SEQUENCE# - APPL.SEQUENCE#) gap_applied, 
		   ((PRIM.SEQUENCE#) - ARCH.SEQUENCE#) gap_received, 
		   ((ARCH.SEQUENCE# - APPL.SEQUENCE#) + ((PRIM.SEQUENCE#) - ARCH.SEQUENCE#)) gap 
		   FROM 
		   ( SELECT DEST_ID, THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'YES' AND (DEST_ID, THREAD#, FIRST_TIME) IN ( SELECT AL.DEST_ID, THREAD#, MAX(FIRST_TIME) 
           FROM V$ARCHIVED_LOG AL, V$ARCHIVE_DEST AD 
           WHERE AL.RESETLOGS_CHANGE# = ( SELECT RESETLOGS_CHANGE# FROM v$database) AND 
           AL.DEST_ID = AD.DEST_ID AND 
           AD.TARGET = 'STANDBY' AND 
           AD.STATUS <> 'INACTIVE' 
           GROUP BY AL.DEST_ID, AL.THREAD#)) ARCH, 
	       ( SELECT DEST_ID, THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'YES' AND 
           APPLIED = 'YES' AND (DEST_ID, THREAD#, FIRST_TIME) IN 
           ( SELECT AL.DEST_ID, THREAD#, MAX(FIRST_TIME) 
           FROM V$ARCHIVED_LOG AL, V$ARCHIVE_DEST AD 
           WHERE AL.RESETLOGS_CHANGE# = ( SELECT RESETLOGS_CHANGE# FROM v$database) AND 
		   AL.APPLIED = 'YES' AND 
           AL.DEST_ID = AD.DEST_ID AND 
           AD.TARGET = 'STANDBY' AND 
           AD.STATUS <> 'INACTIVE' 
           GROUP BY AL.DEST_ID, AL.THREAD#)) APPL, 
	       (SELECT THREAD#, SEQUENCE# - 1 sequence# FROM V$LOG WHERE STATUS like '%CURRENT') PRIM, 
		   v$archive_dest AD 
		   WHERE ARCH.THREAD# = APPL.THREAD# 
		   AND PRIM.THREAD# = APPL.THREAD# 
		   AND ARCH.DEST_ID = APPL.DEST_ID 
		   AND APPL.DEST_ID = AD.DEST_ID 
		   AND AD.TARGET = 'STANDBY' 
		   AND AD.STATUS <> 'INACTIVE' 
		   ORDER BY 2,3

/*045*/ dataguard_timegap

           SELECT APPLIED.DI Standby_Destination_ID, AD.DB_UNIQUE_NAME db_unique_name, 
           PRIM.TN Thread, 
           PRIM.SN sequence#_primary , 
           APPLIED.SN sequence#_standby, 
           to_char(PRIM.CT,'DD.MM.YYYY HH24.MI.SS') dato_primary, 
           to_char(APPLIED.CT,'DD.MM.YYYY HH24.MI.SS') dato_standby, 
           TO_NUMBER (((DECODE(GREATEST(APPLIED.CT,PRIM.CT), PRIM.CT, (PRIM.CT - APPLIED.CT ),0)*24)*60)*60) as gap_applied, 
           to_number(to_char(to_date('1','J') + (DECODE(GREATEST(APPLIED.CT,PRIM.CT), PRIM.CT, (PRIM.CT - APPLIED.CT ),0)),'J')-1) 
           || ' days ' || to_char(to_date('00.00.00','HH24.MI.SS') + 
           (DECODE(GREATEST(APPLIED.CT,PRIM.CT), PRIM.CT, (PRIM.CT - APPLIED.CT ),0)), 'HH24:MI:SS') as gap_applied_char 
           FROM 
           (SELECT DEST_ID DI, THREAD# TN, MAX(SEQUENCE#) SN, MAX(COMPLETION_TIME) CT 
           FROM V$ARCHIVED_LOG 
           WHERE DEST_ID=1 AND ARCHIVED='YES' AND 
           RESETLOGS_CHANGE# = ( SELECT RESETLOGS_CHANGE# FROM v$database) 
           GROUP BY DEST_ID, THREAD#) PRIM,
           (SELECT DEST_ID DI, THREAD# TN, MAX(SEQUENCE#) SN, MAX(COMPLETION_TIME) CT 
           FROM V$ARCHIVED_LOG 
           WHERE DEST_ID <> 1 AND 
           STANDBY_DEST = 'YES' AND APPLIED='YES' AND 
           RESETLOGS_CHANGE# = ( SELECT RESETLOGS_CHANGE# FROM v$database) 
           GROUP BY DEST_ID, THREAD#) APPLIED, V$ARCHIVE_DEST AD 
           WHERE APPLIED.TN = PRIM.TN AND 
           AD.DEST_ID = APPLIED.DI AND 
           AD.TARGET = 'STANDBY' AND 
           AD.STATUS <> 'INACTIVE' 
           ORDER BY 1,3

/*046*/ tablespace_temp_free

         SELECT d.tablespace_name as name, d.extent_management,  ddf.BYTES / 1024 as tssize,  (NVL (dfs.BYTES, 0)) / 1024 as free,  NVL((NVL (dfs.BYTES, 0)) / ddf.BYTES * 100, 0) as free_pct 
FROM dba_tablespaces d, 
   		(SELECT   tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) maxbytes, COUNT (1) antall FROM dba_free_space GROUP BY tablespace_name) dfs, 
   		(SELECT   tablespace_name, SUM (BYTES) BYTES, SUM (maxbytes) maxbytes, COUNT (1) antall, DECODE (MAX (autoextensible),'YES', 'J','N') autoextend_flag 
           FROM dba_data_files GROUP BY tablespace_name) ddf 
       	WHERE d.tablespace_name = ddf.tablespace_name  
       	AND d.tablespace_name = dfs.tablespace_name  
       	AND NOT (d.extent_management LIKE 'LOCAL')  
       	AND d.CONTENTS = 'TEMPORARY' 
UNION ALL 
SELECT d.tablespace_name as name, d.extent_management,  ddf.BYTES / 1024 as tssize,  (ddf.BYTES - NVL (dfs.BYTES, 0)) / 1024 as free,  NVL ((ddf.BYTES - NVL (dfs.BYTES, 0)) / ddf.BYTES * 100, 0) as free_pct 
FROM dba_tablespaces d, 
   		(SELECT   tablespace_name, SUM (BYTES) BYTES, SUM (maxbytes) maxbytes, COUNT (1) antall, DECODE (MAX (autoextensible), 'YES', 'J','N') autoextend_flag 
             FROM dba_temp_files GROUP BY tablespace_name) ddf, 
   		(SELECT   ss.tablespace_name, SUM ((ss.used_blocks * ts.BLOCKSIZE)) BYTES, MAX ((ss.used_blocks * ts.BLOCKSIZE)) maxbytes, COUNT (1) antall 
             FROM gv$sort_segment ss, SYS.ts$ ts WHERE ss.tablespace_name = ts.NAME GROUP BY ss.tablespace_name) dfs 
   		WHERE d.tablespace_name = ddf.tablespace_name 
       	AND d.tablespace_name = dfs.tablespace_name 
       	AND d.extent_management LIKE 'LOCAL' 
       	AND d.CONTENTS = 'TEMPORARY' 
ORDER BY 1

/*047*/ active_users

     	SELECT COUNT(username) as active_users FROM v$session WHERE username NOT IN('SYS','SYSTEM') and status = 'ACTIVE'

/*048*/ flash_recovery_area_memory_free

SELECT space_limit, space_used, (space_limit - space_used) space_free from v$recovery_file_dest

/*049*/ extents

SELECT /*+ RULE */ segment_name,owner,tablespace_name,TRUNC(extents) as extents, segment_type,partition_name FROM dba_segments WHERE extents >= 10 AND TABLESPACE_NAME IN (SELECT DTBS.tablespace_name FROM DBA_TABLESPACES DTBS WHERE DTBS.TABLESPACE_NAME NOT IN ('SYS','SYSTEM'))ORDER  BY extents desc, segment_name

/*050*/ extents

SELECT /*+ RULE */ segment_name,owner,tablespace_name,TRUNC(extents) as extents, segment_type,partition_name FROM dba_segments WHERE extents >= 10 AND TABLESPACE_NAME IN (SELECT DTBS.tablespace_name FROM DBA_TABLESPACES DTBS WHERE DTBS.TABLESPACE_NAME NOT IN ('SYS','SYSTEM','ROLLBACK','UNDO'))ORDER  BY extents desc, segment_name

/*051*/ extents

SELECT /*+ RULE */ segment_name,owner,tablespace_name,TRUNC(extents) as extents, segment_type,partition_name FROM dba_segments WHERE extents >= 10 AND TABLESPACE_NAME IN (SELECT DTBS.tablespace_name FROM DBA_TABLESPACES DTBS WHERE DTBS.extent_management != 'LOCAL' AND DTBS.TABLESPACE_NAME NOT IN ('SYS','SYSTEM'))ORDER  BY extents desc, segment_name

/*052*/ extents

SELECT /*+ RULE */ segment_name,owner,tablespace_name,TRUNC(extents) as extents, segment_type,partition_name FROM dba_segments WHERE extents >= 10 AND TABLESPACE_NAME IN (SELECT DTBS.tablespace_name FROM DBA_TABLESPACES DTBS WHERE DTBS.extent_management != 'LOCAL' AND DTBS.TABLESPACE_NAME NOT IN ('SYS','SYSTEM','ROLLBACK','UNDO'))ORDER  BY extents desc, segment_name

/*053*/ remaining_extents


SELECT /*+ RULE */ owner,segment_name,segment_type,(max_extents-NVL(extents,0)) as extleft, partition_name FROM dba_segments WHERE (max_extents-NVL(extents,0))<100

/*054*/ remaining_extents


SELECT owner,segment_name,segment_type,(max_extents-NVL(extents,0)) as extleft, partition_name FROM DBA_SEGMENTS WHERE tablespace_name IN ( SELECT DTBS.tablespace_name FROM DBA_TABLESPACES DTBS WHERE DTBS.TABLESPACE_NAME NOT IN ('SYS','SYSTEM','ROLLBACK','UNDO'))

/*055*/ remaining_extents


SELECT owner,segment_name,segment_type,(max_extents-NVL(extents,0)) as extleft, partition_name FROM DBA_SEGMENTS WHERE tablespace_name IN ( SELECT DTBS.tablespace_name FROM DBA_TABLESPACES DTBS WHERE DTBS.extent_management != 'LOCAL' AND DTBS.TABLESPACE_NAME NOT IN ('SYS','SYSTEM'))

/*056*/ remaining_extents


SELECT owner,segment_name,segment_type,(max_extents-NVL(extents,0)) as extleft, partition_name FROM DBA_SEGMENTS WHERE tablespace_name IN ( SELECT DTBS.tablespace_name FROM DBA_TABLESPACES DTBS WHERE DTBS.extent_management != 'LOCAL' AND DTBS.TABLESPACE_NAME NOT IN ('SYS','SYSTEM','ROLLBACK','UNDO'))

/*057*/ active_connection_ratio


SELECT (SELECT COUNT(*) FROM V$SESSION) AS ACTIVE, VP.VALUE as TOTAL FROM V$PARAMETER VP WHERE VP.NAME = 'sessions'

/*078*/ pdb_count


select con_id from v$pdbs where con_id>2 order by con_id

Additional Information

https://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en/alphabetical-probe-articles/oracle-oracle-database-monitoring

Feedback

thumb_up Yes

thumb_down No