求会话等待的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;
[] [] []
![数据库进阶：数据库管理员日常工作中必备的sql列表[2]](/UploadFiles/2.jpg)