CODEWITHSUNDEEP
sqloracle-databaseplsql
Ripudemy
Ripudemy

Reputation: 1

How to get the size of a materialized view in oracle?

I would like to know how can I get the size of a materialized view I created in oracle and also the cost of creating the materialized view if possible.

For example how to get the size of this view (storage used by this view), the name of the database is studentDB.

create materialized view mv_name
as
select * from student;

Upvotes: 0

Views: 5011

Answers (1)

Popeye
Popeye

Reputation: 35910

You can use the DBA_SEGMENTS or USER_SEGMENTS dictionary view as follows:

SELECT SEGMENT_NAME,
       SEGMENT_TYPE,
       BYTES / 1024 / 1024 MB
  FROM DBA_SEGMENTS
 WHERE SEGMENT_TYPE = 'TABLE'
   AND SEGMENT_NAME = '<yourviewname>';

Materialized view creates the table with the same name as the Materialized view name.

Upvotes: 3

Related Questions