Полезные запросы

Постепенно собираю запросы помогающие жить

Устаревшая статистика по схемам

SELECT T.OWNER,
         T.TABLE_NAME,
         TM.TIMESTAMP,
         SYSDATE,
            ROUND (
                 (  (TM.INSERTS + TM.UPDATES + TM.DELETES)
                  / DECODE (T.NUM_ROWS,
                            0, (TM.INSERTS + TM.UPDATES + TM.DELETES),
                            T.NUM_ROWS))
               * 100,
               1)
         || ' %'
            AS change_pct
    FROM DBA_TAB_MODIFICATIONS tm, DBA_TABLES t
   WHERE     TM.TABLE_OWNER = T.OWNER
         AND T.OWNER IN ('....Перечень схем .....')
         AND TM.TABLE_NAME = T.TABLE_NAME
         AND (TM.INSERTS + TM.UPDATES + TM.DELETES) > 0
         AND   (  (TM.INSERTS + TM.UPDATES + TM.DELETES)
                / DECODE (T.NUM_ROWS,
                          0, (TM.INSERTS + TM.UPDATES + TM.DELETES),
                          T.NUM_ROWS))
             * 100 >
                DBMS_STATS.GET_PREFS ('STALE_PERCENT', T.OWNER, T.TABLE_NAME)
ORDER BY (  (TM.INSERTS + TM.UPDATES + TM.DELETES)
          / DECODE (T.NUM_ROWS,
                    0, (TM.INSERTS + TM.UPDATES + TM.DELETES),
                    T.NUM_ROWS)) DESC

Таблицы имеющие foreignKey на выбранную

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = :r_table_name
   and owner = :r_owner
 )
order by table_name, constraint_name