Technorama

Query rewrite is a very useful concept provided by Oracle so that queries on a table can be redirected to a Materialized view. I am not too familiar with this, so that will be the topic of another post.

This is a quick post about the settings on the database to enable query rewrite.

The following query helps in knowing the settings:

show parameter rewrite


In the results, the query_rewrite_enabled property must be true.

If it is false, query rewrite will not happen

To set it to true, use the following:

alter system set query_rewrite_enabled = TRUE ;


To find out how much physical space a table occupies in Oracle Database, use the following query :

SELECT
owner, table_name, segment_type,TRUNC(sum(bytes)/1024/1024) MEG
FROM
(SELECT segment_name table_name, owner, bytes, segment_type
FROM dba_segments
)
WHERE owner in UPPER(SCHEMA_NAME)
GROUP BY table_name,segment_type, owner
HAVING SUM(bytes)/1024/1024 >= 10
ORDER BY SUM(bytes) desc;


This will return all the tables in the schema that occupy more than 10 MB of space.