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

    Oracle MV  - Segment type

    ee02272f-3bde-4478-abaf-cd9f2c3e4a8c

      Hi ,

       

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

       

      Thanks

      Lbn

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

          Hi,

           

          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



          Regards,

          Kundan


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

            Table Segment!