Oracle工具sql

標籤：oracle

查詢處於鎖表中的表

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

SELECTb.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 statementFROMv$sqlarea a,dba_users bWHEREa.parsing_user_id = b.user_idAND a.disk_reads > 100000ORDER BYa.DISK_READS DESC;

解析時間大於執行時間

SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS,2) Hit_radio,ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,SQL_TEXTFROMV$SQLAREAWHEREEXECUTIONS > 0AND BUFFER_GETS > 0AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0

效能最差SQL

SELECThash_value,executions,buffer_gets,disk_reads,parse_calls,sql_textFROMV$SQLAREAWHEREbuffer_gets > 10000000OR disk_reads > 1000000ORDER BYbuffer_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;

本文出自 “10950988” 部落格，請務必保留此出處http://10960988.blog.51cto.com/10950988/1791064

Oracle工具sql