What queries are used by the oracle probe?

Document ID : KB000034647
Last Modified Date : 08/10/2018
Show Technical Document Details
Question:

What Queries are used by the Oracle Probe?

Answer:

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