What Queries are used by the Oracle Probe?
Release:
Component:
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
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'
SELECT /*+ RULE */(TRUNC(sum(gets))) as gets,(TRUNC(sum(getmisses))) as getmisses FROM v$rowcache
SELECT (TRUNC(sum(pins))) as pins,(TRUNC(sum(reloads))) as reloads FROM v$librarycache
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'
SELECT name,(TRUNC(waits)) as waits,(TRUNC(gets)) as gets FROM v$rollstat, v$rollname WHERE v$rollstat.usn=v$rollname.usn
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)
SELECT owner,object_type,object_name,status, 1 as value FROM dba_objects WHERE status='INVALID' ORDER BY owner,object_type,object_name
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
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#
SELECT COUNT(username) as login_cnt FROM v$session WHERE username NOT IN('SYS','SYSTEM')
SELECT value as enqueue_timeouts FROM v$sysstat WHERE name='enqueue timeouts'
SELECT name,gets,misses FROM v$latch WHERE name IN('redo allocation','redo copy')
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
SELECT decode(totalq,0,0,wait*10/totalq) as wait_time FROM v$queue WHERE type='COMMON'
SELECT name, decode(pool, null, '(total)', '('||pool||')') as pool, bytes FROM v$sgastat where name != 'free memory'
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)'
SELECT owner,index_name,table_name,status FROM all_indexes WHERE status NOT IN('VALID', 'N/A')
SELECT tablespace_name,status,decode(status,'ONLINE',1,0) as qstatus FROM dba_tablespaces
SELECT segment_name,status,decode(status,'ONLINE',1,0) as qstatus FROM dba_rollback_segs
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)
SELECT SUM(value) memory FROM v$sesstat WHERE statistic#=20
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))
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
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
SELECT class,count,time FROM v$waitstat
SELECT statistic# as statistic, name, class, value FROM v$sysstat
SELECT event,total_waits,total_timeouts,time_waited,average_wait FROM v$system_event
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#
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
SELECT name, decode(pool, null, '(total)', '('||pool||')') as pool, bytes FROM v$sgastat where name = 'free memory'
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
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'
select value from v$sysstat where name='gc cr timeouts'
select value from v$sysstat where name='gc convert timeouts'
select value from v$sysstat where name='gc blocks lost'
select value from v$sysstat where name='gc blocks corrupt'
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'
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'
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
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
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
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'))
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'
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
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
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
SELECT COUNT(username) as active_users FROM v$session WHERE username NOT IN('SYS','SYSTEM') and status = 'ACTIVE'
SELECT space_limit, space_used, (space_limit - space_used) space_free from v$recovery_file_dest
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
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
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
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
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
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'))
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'))
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'))
SELECT (SELECT COUNT(*) FROM V$SESSION) AS ACTIVE, VP.VALUE as TOTAL FROM V$PARAMETER VP WHERE VP.NAME = 'sessions'
select con_id from v$pdbs where con_id>2 order by con_id