Selecting all SDO_GTYPE values for all tables/sdo_geometry columns in a schema

 SET serveroutput ON SIZE unlimited
 DECLARE
   v_sql         varchar2(4000);
   v_gtype       varchar2(40);
   v_gtype_count NUMBER;
   c_gtypes      sys_refcursor;
 BEGIN
   FOR rec IN (SELECT TABLE_NAME, column_name
                 FROM user_tab_columns
                WHERE data_type = 'SDO_GEOMETRY'
               ORDER BY TABLE_NAME, column_name) loop
   BEGIN
      v_sql := 'SELECT a.' || rec.column_name || '.sdo_gtype gtype, count(*) as gtype_count ' ||
                ' FROM ' || rec.TABLE_NAME || ' a ' ||
               ' WHERE a.' || rec.column_name || ' is not null ' ||
               ' GROUP BY a.' || rec.column_name || '.sdo_gtype';
      OPEN c_gtypes FOR v_sql;
      FETCH c_gtypes INTO v_gtype, v_gtype_count;
      IF ( c_gtypes%NOTFOUND ) THEN
         dbms_output.put_line(rec.TABLE_NAME || '.' || rec.column_name ||' has no data.');
      ELSE
        LOOP
           dbms_output.put_line(rec.TABLE_NAME || '.' || rec.column_name ||' = ' ||
                                (CASE WHEN v_gtype IS NULL THEN 'NULL' ELSE to_char(v_gtype) END) ||
                                ' Count(' || v_gtype_count || ')');
           FETCH c_gtypes INTO v_gtype, v_gtype_count;
           EXIT WHEN c_gtypes%NOTFOUND;
        END LOOP;
      END IF;
      CLOSE c_gtypes;
      exception
         WHEN others THEN
              dbms_output.put_line('Query (' || v_sql || ') threw error (' || SQLCODE ||')');
   END;
   END loop;
 END;
 /
 -- Results
 anonymous block completed
 GEODPOINT2D.GEOM = 2001 COUNT(500)
 GEODPOINT3D.GEOM = 3001 COUNT(500)
 GEODPOLY2D.GEOM = 2003 COUNT(1)
 GUTDATA.GEOM = 2001 COUNT(1000)
 HAS_METADATA.GEOM = 2001 COUNT(500)
 HAS_METADATA3D.GEOM = 3001 COUNT(500)
 LOCALLINE2D.GEOM = 2002 COUNT(2)
 LOCALPOINT2D.GEOM = 2001 COUNT(1)
 LOCALPOLY2D.GEOM = 2007 COUNT(1)
 LOCALPOLY2D.GEOM = 2003 COUNT(1)
 LRS_ROUTES.ROUTE_GEOMETRY = 3302 COUNT(4)
 MARK_A.GEOM = 2001 COUNT(25)
 MARK_EMPTY.GEOM has no DATA.
 MARK_L.GEOM = 3001 COUNT(4)
 NO_METADATA.GEOM = 2001 COUNT(500)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 2005 COUNT(4)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = NULL COUNT(1)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 2112 COUNT(1)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 2001 COUNT(3)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 2006 COUNT(8)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 4002 COUNT(1)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 2007 COUNT(8)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 3002 COUNT(1)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 3302 COUNT(1)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 2003 COUNT(17)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 2004 COUNT(2)
 ORACLE_TEST_GEOMETRIES.GEOMETRY = 2002 COUNT(18)
 ORIENTED_POINT.GEOM = 3001 COUNT(1533)
 PROJ41014POLY2D.GEOM = 2003 COUNT(2)
 PROJ41914POLY2D.GEOM = 2003 COUNT(1)
 PROJCIRCLE2D.GEOM = 2003 COUNT(500)
 PROJCOMPOUND2D.GEOM = 2004 COUNT(3)
 PROJLINE2D.GEOM = 2002 COUNT(5)
 PROJLINE3D.GEOM = 3002 COUNT(2)
 PROJMULTILINE2D.GEOM = 2006 COUNT(500)
 PROJMULTIPOINT2D.GEOM = 2005 COUNT(9)
 PROJMULTIPOINT3D.GEOM = 3005 COUNT(1)
 PROJMULTIPOLY2D.GEOM = 2007 COUNT(500)
 PROJPOINT2D.GEOM = 2001 COUNT(500)
 PROJPOINT3D.GEOM = 3001 COUNT(500)
 PROJPOLY2D.GEOM = 2003 COUNT(8)
 PROJPOLY3D.GEOM = 3003 COUNT(2)
 TOOLSPOLY2D.CENTROID = 2001 COUNT(1)
 TOOLSPOLY2D.GEOM = 2003 COUNT(2)
 V_GEODPOINT2D.GEOM = 2001 COUNT(500)
 V_GEODPOLY2D.GEOM = 2003 COUNT(1)