Oracle工具sql

前端之家收集整理的这篇文章主要介绍了Oracle工具sql前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

查询处于锁表中的表

  1. SELECT
  2. l.session_idSID,l.locked_mode,l.oracle_username,l.os_user_name,s.machine,s.terminal,o.object_name,s.logon_time,s.serial#
  3. FROM
  4. v$locked_objectl,all_objectso,v$sessions
  5. WHERE
  6. l.object_id=o.object_id
  7. ANDl.session_id=s.SID
  8. ORDERBY
  9. SID,s.serial#;

删除掉系统锁定的此记录

  1. ALTERSYSTEMKILLSESSION'SID,serial#';

查询最慢的sql

  1. SELECT
  2. *
  3. FROM
  4. (
  5. SELECT
  6. parsing_user_id,executions,sortscommand_type,disk_reads,sql_text
  7. FROM
  8. v$sqlarea
  9. ORDERBY
  10. disk_readsDESC
  11. )
  12. WHERE
  13. ROWNUM<10

消耗磁盘读取最多的sqltop5

  1. SELECT
  2. disk_reads,sql_text
  3. FROM
  4. (
  5. SELECT
  6. sql_text,DENSE_RANK()OVER(ORDERBYdisk_readsDESC)disk_reads_rank
  7. FROM
  8. v$sql
  9. )
  10. WHERE
  11. disk_reads_rank<=5;

ORACLE分页查询

  1. SELECT
  2. *
  3. FROM
  4. (
  5. SELECT
  6. ROW_.*,ROWNUMROWNUM_
  7. FROM
  8. (
  9. SELECT*FROMTABLE_NAME
  10. )ROW_
  11. )
  12. WHERE
  13. ROWNUM_>0
  14. ANDROWNUM_<=5

ORACLE查询一行数据

  1. SELECT
  2. *
  3. FROM
  4. (
  5. SELECT*FROMTABLE_NAME
  6. )A
  7. WHERE
  8. ROWNUM=1

查询IO大于10000的sql

  1. SELECT
  2. b.usernameusername,a.disk_readsREADS,a.executionsexec,a.disk_reads/decode(
  3. a.executions,1,a.executions
  4. )rds_exec_ratio,a.sql_textstatement
  5. FROM
  6. v$sqlareaa,dba_usersb
  7. WHERE
  8. a.parsing_user_id=b.user_id
  9. ANDa.disk_reads>100000
  10. ORDERBY
  11. a.DISK_READSDESC;

解析时间大于执行时间

  1. SELECT
  2. EXECUTIONS,DISK_READS,BUFFER_GETS,ROUND(
  3. (BUFFER_GETS-DISK_READS)/BUFFER_GETS,2
  4. )Hit_radio,ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,sql_TEXT
  5. FROM
  6. V$sqlAREA
  7. WHERE
  8. EXECUTIONS>0
  9. ANDBUFFER_GETS>0
  10. AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0

性能最差sql

  1. SELECT
  2. hash_value,buffer_gets,parse_calls,sql_text
  3. FROM
  4. V$sqlAREA
  5. WHERE
  6. buffer_gets>10000000
  7. ORdisk_reads>1000000
  8. ORDERBY
  9. buffer_gets+100*disk_readsDESC;

查看表空间

  1. SELECTUpper(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')
  2. ||'%'"使用比",F.TOTAL_BYTES"空闲空间(M)",F.MAX_BYTES"最大块(M)"
  3. FROM(SELECTTABLESPACE_NAME,Round(Sum(BYTES)/(1024*1024),2)TOTAL_BYTES,Round(Max(BYTES)/(1024*1024),2)MAX_BYTES
  4. FROMSYS.DBA_FREE_SPACE
  5. GROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,Round(Sum(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MB
  6. FROMSYS.DBA_DATA_FILESDD
  7. GROUPBYDD.TABLESPACE_NAME)D
  8. WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME
  9. ORDERBY1;

猜你在找的Oracle相关文章