2 Replies Latest reply: Jul 5, 2014 11:18 PM by Kundan Sinha RSS

    Oracle MV  - Segment type


      Hi ,


        Can some one tell me what is the type of segments  used by oracle materialized views and how to find the details.




        • 1. Re: Oracle MV  - Segment type
          Kundan Sinha



          The below query will let you know of the space used by the materialized views.



          /* Assuming ABC is the name of the schema(owner):


          select a.object_name, sum(b.bytes)/(1024*1024) "ALLOCATED(MB)"

          from user_objects a, user_extents b

          where a.owner = 'ABC' and b.owner ='ABC'

          and a.object_name = b.segment_name

          and a.object_type = 'MATERIALIZED VIEW'

          group by object_name;


          As you know:


          all_objects is show you all objects of current and those object which you have rights to access.

          dba_objects show all object of all users .

          user_objects show you the current user objects


          Now for checking, under which segment_type does MV comes, user below:

          select segment_type from USER_SEGMENTS where segment_name = '<object_name>';  /** object_name, i.e., MV comes from the first script above



          • 2. Re: Oracle MV  - Segment type
            Kundan Sinha

            Table Segment!