Selecting polygon feature classes with one record in Oracle geodatabase using SQL query?

Several parts are involved:

• getting all the tables from the database (used code from the answer https://stackoverflow.com/a/9954833/3346915)
• reading geodatabase metadata (in your question body)
• filtering using the where clause to include only those tables that are of feature class type and their shape type are of polygon (parsing XML is involved).

This code would work on SQL Server:

SELECT 
    TableName = t.NAME,
    TableSchema = s.Name,
    RowCounts = p.rows
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID 
    AND i.index_id = p.index_id
WHERE 
    t.name in (
        (SELECT PARSENAME(items.Name,1)
          FROM SDE.GDB_ITEMS items INNER JOIN SDE.GDB_ITEMTYPES itemtypes 
          ON items.Type = itemtypes.UUID
          WHERE itemtypes.Name = 'Feature Class' and 
                Definition.value('(/DEFeatureClassInfo/ShapeType)[1]', 
                'varchar(max)') = 'esriGeometryPolygon'))   
    and t.is_ms_shipped = 0 
    and p.rows = 1

GROUP BY
    t.NAME, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

The results:

    TableName           TableSchema      RowCounts
--  ------------------  -------------  -----------
 0  ND_1226_DIRTYAREAS  dbo                      1
 1  REDLINING           dbo                      1
 2  SS_WORKAREAS        dbo                      1
 3  T_1_DIRTYAREAS      dbo                      1