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_id
AND l.session_id = s. SID
ORDER 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_text
FROM
     (
          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_ > 0
AND ROWNUM_ <= 5

ORACLE查詢一行數據

SELECT
     *
FROM
     (
          SELECT * FROM TABLE_NAME
     ) A
WHERE
     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 statement
FROM
	v$sqlarea a,
	dba_users b
WHERE
	a.parsing_user_id = b.user_id
AND a.disk_reads > 100000
ORDER 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_TEXT
FROM
	V$SQLAREA
WHERE
	EXECUTIONS > 0
AND BUFFER_GETS > 0
AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0

性能最差SQL

SELECT
	hash_value,
	executions,
	buffer_gets,
	disk_reads,
	parse_calls,
	sql_text
FROM
	V$SQLAREA
WHERE
	buffer_gets > 10000000
OR disk_reads > 1000000
ORDER 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) D
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER  BY 1;