p184.sql

oracal的入门知识

break on hash_value skip 1
SELECT /*+ rule */ hash_value,sql_text
    FROM v$sqltext
   WHERE (hash_value, address) IN (
            SELECT a.hash_value, a.address
              FROM v$sqltext a,
                   (SELECT DISTINCT a.owner, a.segment_name, a.segment_type
                               FROM dba_extents a,
                                    (SELECT dbarfil, dbablk
                                       FROM (SELECT   dbarfil, dbablk
                                                 FROM x$bh
                                             ORDER BY tch DESC)
                                      WHERE ROWNUM < 11) b
                              WHERE a.relative_fno = b.dbarfil
                                AND a.block_id <= b.dbablk
                                AND a.block_id + a.blocks > b.dbablk) b
             WHERE a.sql_text LIKE '%' || b.segment_name || '%'
               AND b.segment_type = 'TABLE')
ORDER BY hash_value, address, piece
/