查询处于锁表中的表
- 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#';
消耗磁盘读取最多的sqltop5
- 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
解析时间大于执行时间
查看表空间
- 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;