Oracle Tables with no PK or UI and rowsize > 1M in the Schema

One of the proactive measures in ensuring a healthy and performant database is to ensure that your tables have a proper and meaningful primary keys and uniqueness embedded in them. The sizes also matter of the tables. 

Following query pulls out the list of database tables with no PK or UI and rowsize > 1M in the Schema.

SET Heading off

SELECT '------ Tables with no PK or UI and rowsize > 1M in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT distinct(table_name)

  FROM all_tab_columns

 WHERE owner = :b0

 group by table_name

 HAVING sum(data_length) > 1000000

MINUS

(SELECT obj1.name

  FROM SYS.user$ user1,

       SYS.user$ user2,

       SYS.cdef$ cdef,

       SYS.con$ con1,

       SYS.con$ con2,

       SYS.obj$ obj1,

       SYS.obj$ obj2

 WHERE user1.name = :b0

   AND cdef.type# = 2

   AND con2.owner# = user2.user#(+)

   AND cdef.robj# = obj2.obj#(+)

   AND cdef.rcon# = con2.con#(+)

   AND obj1.owner# = user1.user#

   AND cdef.con# = con1.con#

   AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

  FROM all_indexes idx

 WHERE idx.owner = :b0

   AND idx.uniqueness = 'UNIQUE')

/