求會話等待的file_id/block_id
col event format a
col ptext format a
col ptext format a
col ptext format a
select sideventptext p ptext p ptext p
from v$session_wait
where event not like %SQL% and event not like %rdbms% and event not like %mon% order by event;
select namewait_time from v$latch l where exists (select from (select sideventptext p ptext p ptext p
from v$session_wait
where event not like %SQL% and event not like %rdbms% and event not like %mon%
) x where xp= llatch#);
求會話等待的對象
col owner format a
col segment_name format a
col segment_type format a
select ownersegment_namesegment_type
from dba_extents
where file_id = &file_id and &block_id between block_id and block_id + blocks ;
求buffer cache中的塊信息
select oOBJECT_TYPE substr(oOBJECT_NAME) objname bobjd bstatus count(bobjd)
from v$bh b dba_objects o
where bobjd = odata_object_id and oowner = & group by oobject_type oobject_namebobjd bstatus ;
求日志文件的空間使用
select leleseq current_log_sequence# *cpcpodr_bno/lelesiz percentage_full
from x$kcccp cpx$kccle le
where leleseq =cpcpodr_seq;
求等待中的對象
select /*+rule */ ssid susername wevent oowner osegment_name osegment_type
opartition_name wseconds_in_wait seconds wstate
from v$session_wait w v$session s dba_extents o
where wevent in (select name from v$event_name where parameter = file#
and parameter = block# and name not like control%)
and oowner <> sys and wsid = ssid and wp = ofile_id and wp >= oblock_id and wp < oblock_id + oblocks
求當前事務的重做尺寸
select value
from v$mystat v$statname
where v$mystatstatistic# = v$statnamestatistic# and v$statnamename = redo size;
喚醒smon去清除臨時段
column pid new_value Smon
set termout off
select ppid from sysv_$bgprocess bsysv_$process p where bname = SMON and paddr = bpaddr
/
set termout on
oradebug wakeup &Smon
undefine Smon
求回退率
select bvalue/(avalue + bvalue)avaluebvalue from v$sysstat av$sysstat b
where astatistic#= and bstatistic#=;
求DISK READ較多的SQL
select stsql_text from v$sql sv$sqltext st
where saddress=staddress and shash_value=sthash_value and sdisk_reads > ;
求DISK SORT嚴重的SQL
select sessusername sqlsql_text sortblocks
from v$session sess v$sqlarea sql v$sort_usage sort
where sessserial# = sortsession_num
and sortsqladdr = sqladdress
and sortsqlhash = sqlhash_value and sortblocks > ;
求對象的創建代碼
column column_name format a
column sql_text format a
select dbms_metadataget_ddl(TABLE&) from dual;
select dbms_metadataget_ddl(INDEX&) from dual;
求表的索引
set linesize
select aindex_nameacolumn_namebstatus bindex_type
from user_ind_columns auser_indexes b
where aindex_name=bindex_name and atable_name=&;
求索引中行數較多的
select index_nameblevelnum_rowsCLUSTERING_FACTORstatus from user_indexes where num_rows > and blevel >
select table_nameindex_nameblevelnum_rowsCLUSTERING_FACTORstatus from user_indexes where status <> VALID
求當前會話的SIDSERIAL#
select sid serial# from v$session where audsid = SYS_CONTEXT(USERENVSESSIONID);
求表空間的未用空間
col mbytes format
select tablespace_namesum(bytes)// mbytes from dba_free_space group by tablespace_name;
求表中定義的觸發器
select table_nameindex_typeindex_nameuniqueness from user_indexes where table_name=&;
select trigger_name from user_triggers where table_name=&;
求未定義索引的表
select table_name from user_tables where table_name not in (select table_name from user_ind_columns);
執行常用的過程
exec print_sql(select count(*) from tab);
exec show_space(table_name);
求free memory
select * from v$sgastat where name=free memory;
select anamesum(bvalue) from v$statname av$sesstat b where astatistic# = bstatistic# group by aname;
查看一下誰在使用那個可以得回滾段或者查看一下某個可以得用戶在使用回滾段找出領回滾段不斷增長的事務再看看如何處理它是否可以將它commit再不行就看能否kill它查看當前正在使用的回滾段的用戶信息和回滾段信息:
set linesize
SELECT rname ROLLBACK SEGMENT NAME lsid ORACLE PIDpspid SYSTEM PID susername ORACLE USERNAME
FROM v$lock l v$process p v$rollname r v$session s
WHERE lsid = ppid(+) AND ssid=lsid AND TRUNC(lid(+)/) = rusn AND ltype(+) = TX AND llmode(+) = ORDER BY rname;
[] [] []
From:http://tw.wingwit.com/Article/program/SQL/201311/16150.html
