UIM - Queries used by the oracle probe
Article ID: 34647
Updated On:
Products
DX Unified Infrastructure Management (Nimsoft / UIM)
Issue/Introduction
I need to know what queries the oracle probe uses to generate metrics for the available set of checkpoints.
What Queries are used by the Oracle Probe?
Environment
DX UIM 20.4.x / DX UIM 24.x
Oracle probe any version
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
Attachments
Feedback
Yes
No
Powered by
