DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, September 26, 2016

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


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;

No comments: