set serveroutput on
DECLARE
   TYPE rec_owner_segment IS RECORD (
         owner            sys.dba_segments.owner%TYPE,
         segment_name     sys.dba_segments.segment_name%TYPE,
--         partition_name   sys.dba_segments.partition_name%TYPE,
         segment_type     sys.dba_segments.segment_type%TYPE);

   TYPE tp_owner_segment IS TABLE OF
         rec_owner_segment;
   v_owner_segment   tp_owner_segment;

   v_count_row      NUMBER;
   v_cursor         SYS_REFCURSOR;
   v_sql            VARCHAR2(2000);
BEGIN
   OPEN  v_cursor FOR 'SELECT distinct owner, segment_name, segment_type
                         FROM sys.dba_segments
                        WHERE owner IN (''SCOTT'',
                                        ''HR'',
                                        ''HAJO'')
                        ORDER BY owner, segment_type, segment_name';
   FETCH v_cursor
      BULK COLLECT INTO v_owner_segment;
   CLOSE v_cursor;

   -- Segment Loop
   FOR i_owner_segment IN 1 .. v_owner_segment.COUNT
   LOOP
      CASE v_owner_segment(i_owner_segment).segment_type
        WHEN 'TABLE'
          THEN
            v_sql := 'EXEC DBMS_STATS.GATHER_TABLE_STATS (''' || v_owner_segment(i_owner_segment).owner || ''', ''' || v_owner_segment(i_owner_segment).segment_name || ''');';
            DBMS_OUTPUT.PUT_LINE(v_sql);
        WHEN 'TABLE PARTITION'
          THEN
            v_sql := 'EXEC DBMS_STATS.GATHER_TABLE_STATS (''' || v_owner_segment(i_owner_segment).owner || ''', ''' || v_owner_segment(i_owner_segment).segment_name || ''', GRANULARITY => ''PARTITION'');';
            DBMS_OUTPUT.PUT_LINE(v_sql);
        ELSE
            v_sql := '';
      END CASE;
END LOOP;
    -- End Segment Loop
END;
/