Oracle-常用监控SQL
2008-12-17 13:06:31 来源:WEB开发网select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
17. 表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
18、找使用CPU多的用户session
12是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
20.监控log_buffer的使用情况:(值最好小于1%,否则增加log_buffer 的大小)
select rbar.name,rbar.value,re.name,re.value,(rbar.value*100)/re.value||'%' "radio"
from v$sysstat rbar,v$sysstat re
where rbar.name='redo buffer allocation retries'
and re.name='redo entries';
19、查看运行过的SQL语句:
SELECT SQL_TEXT
FROM V$SQL
- ››oracle 中 UPDATE nowait 的使用方法
- ››Oracle ORA-12560解决方法
- ››Oracle 10g RAC 常用维护命令
- ››Oracle如何在ASM中定位文件的分布
- ››Oracle的DBMS_RANDOM.STRING 的用法
- ››oracle 外部表导入时间日期类型数据,多字段导入
- ››Oracle中查找重复记录
- ››oracle修改用户登录密码
- ››Oracle创建删除用户、角色、表空间、导入导出等命...
- ››Oracle中登陆时报ORA-28000: the account is lock...
- ››Oracle数据库在配置文件中更改最大连接数
- ››Oracle中在pl/sql developer修改表的两种方式
更多精彩
赞助商链接