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.
WHERE OPTION like ‘%FULL%’ AND OPERATION like ‘%TABLE%’;
–Then find the Query Text :
WHERE SQL_ID in (
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..