查询处于锁表中的表

SELECT
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#
FROM
v$locked_objectl,all_objectso,v$sessions
WHERE
l.object_id=o.object_id
ANDl.session_id=s.SID
ORDERBY
SID,s.serial#;

删除掉系统锁定的此记录

ALTERSYSTEMKILLSESSION'SID,serial#';

查询最慢的sql

SELECT
*
FROM
(
SELECT
parsing_user_id,executions,sortscommand_type,disk_reads,sql_text
FROM
v$sqlarea
ORDERBY
disk_readsDESC
)
WHERE
ROWNUM<10

消耗磁盘读取最多的sqltop5

SELECT
disk_reads,sql_text
FROM
(
SELECT
sql_text,DENSE_RANK()OVER(ORDERBYdisk_readsDESC)disk_reads_rank
FROM
v$sql
)
WHERE
disk_reads_rank<=5;

ORACLE分页查询

SELECT
*
FROM
(
SELECT
ROW_.*,ROWNUMROWNUM_
FROM
(
SELECT*FROMTABLE_NAME
)ROW_
)
WHERE
ROWNUM_>0
ANDROWNUM_<=5

ORACLE查询一行数据

SELECT
*
FROM
(
SELECT*FROMTABLE_NAME
)A
WHERE
ROWNUM=1

查询IO大于10000的sql

SELECT
	b.usernameusername,a.disk_readsREADS,a.executionsexec,a.disk_reads/decode(
		a.executions,1,a.executions
	)rds_exec_ratio,a.sql_textstatement
FROM
	v$sqlareaa,dba_usersb
WHERE
	a.parsing_user_id=b.user_id
ANDa.disk_reads>100000
ORDERBY
	a.DISK_READSDESC;

解析时间大于执行时间

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
ANDBUFFER_GETS>0
AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0

性能最差sql

SELECT
	hash_value,buffer_gets,parse_calls,sql_text
FROM
	V$sqlAREA
WHERE
	buffer_gets>10000000
ORdisk_reads>1000000
ORDERBY
	buffer_gets+100*disk_readsDESC;

查看表空间

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