Oracle工具sql

查询处于锁表中的表

SELECT     l.session_id SID,     l.locked_mode,     l.oracle_username,     l.os_user_name,     s.machine,     s.terminal,     o.object_name,     s.logon_time,     s.serial#  FROM     v$locked_object l,     all_objects o,     v$session s  WHERE     l.object_id = o.object_idAND l.session_id = s. SIDORDER BY     SID,     s.serial#;

删除掉系统锁定的此记录

ALTER SYSTEM KILL SESSION 'SID,serial#';

查询最慢的sql

SELECT     *FROM     (          SELECT               parsing_user_id,               executions,               sorts command_type,               disk_reads,               sql_text          FROM               v$sqlarea          ORDER BY               disk_reads DESC     )WHERE     ROWNUM < 10

消耗磁盘读取最多的sql top5

SELECT     disk_reads,     sql_textFROM     (          SELECT               sql_text,               disk_reads,               DENSE_RANK () OVER (ORDER BY disk_reads DESC) disk_reads_rank          FROM               v$sql     )WHERE     disk_reads_rank <= 5;

ORACLE分页查询

SELECT     *FROM     (          SELECT               ROW_.*, ROWNUM ROWNUM_          FROM               (                    SELECT * FROM TABLE_NAME               ) ROW_     )WHERE     ROWNUM_ > 0AND ROWNUM_ <= 5

ORACLE查询一行数据

SELECT     *FROM     (          SELECT * FROM TABLE_NAME     ) AWHERE     ROWNUM = 1

查询IO大于10000的SQL

SELECT        b.username username,        a.disk_reads READS,        a.executions exec,        a.disk_reads / decode(                a.executions,                0,                1,                a.executions        ) rds_exec_ratio,        a.sql_text statementFROM        v$sqlarea a,        dba_users bWHERE        a.parsing_user_id = b.user_idAND a.disk_reads > 100000ORDER BY        a.DISK_READS DESC;

解析时间大于执行时间

SELECT        EXECUTIONS,        DISK_READS,        BUFFER_GETS,        ROUND(                (BUFFER_GETS - DISK_READS) / BUFFER_GETS,                2        ) Hit_radio,        ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,        SQL_TEXTFROM        V$SQLAREAWHERE        EXECUTIONS > 0AND BUFFER_GETS > 0AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0

性能最差SQL

SELECT        hash_value,        executions,        buffer_gets,        disk_reads,        parse_calls,        sql_textFROM        V$SQLAREAWHERE        buffer_gets > 10000000OR disk_reads > 1000000ORDER BY        buffer_gets + 100 * disk_reads DESC;

查看表空间

SELECT Upper(F.TABLESPACE_NAME)         "表空间名",       D.TOT_GROOTTE_MB                 "表空间大小(M)",       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",       To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')       || '%'                           "使用比",       F.TOTAL_BYTES                    "空闲空间(M)",       F.MAX_BYTES                      "最大块(M)"FROM   (SELECT TABLESPACE_NAME,               Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,               Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES        FROM   SYS.DBA_FREE_SPACE        GROUP  BY TABLESPACE_NAME) F,       (SELECT DD.TABLESPACE_NAME,               Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB        FROM   SYS.DBA_DATA_FILES DD        GROUP  BY DD.TABLESPACE_NAME) DWHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER  BY 1;