Query to find out FULL table scan and table extent in a database
Query to find out FULL table scan and table extent in a database
2) The below query will provide the table name for which next maxextent needed in a database if there is anything..
1) The below query will provide all the select statement which is using FTS in the whole database.
SELECT SQL_ID,OPERATION,OPTIONS,OBJECT_NAME,OBJECT_OWNER
FROM V$SQL_PLAN
WHERE OPTION like ‘%FULL%’ AND OPERATION like ‘%TABLE%’;
–Then find the Query Text :
SELECT SQL_ID,SQL_TEXT
FROM V$SQL
WHERE SQL_ID in (
SELECT SQL_ID
FROM V$SQL_PLAN
WHERE OPTIONS like ‘%FULL%’ AND OPERATION like ‘%TABLE%’
);
2) The below query will provide the table name for which next maxextent needed in a database if there is anything..
set lines 130
column DIFF format 999
column owner format a10
column segment_name format a40
select owner,next_extent,segment_name,segment_type,extents,max_Extents,(max_extents – extents) DIFF
from dba_segments
where 45 > (max_extents – extents)
and segment_type <> ‘CACHE’
order by 7 desc;