返回首页
当前位置: 主页 > 网络编程 > Jsp实例教程 >

linux中oracle的日常维护命令大全(2)

时间:2011-04-26 20:50来源:知行网www.zhixing123.cn 编辑:麦田守望者

SQL> Select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
2 from (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B
where A.tablespace_name=B.tablespace_name;
3

TABLESPACE_NAME USED_PERCENT
------------------------------ ------------
UNDOTBS1 33.25
SYSAUX 99.0364583
RISENET .0125
USERS 62.5
SYSTEM 98.6067708
EXAMPLE 68.25
PERFSTAT 28.7625

7 rows selected.

12 . 检查一些扩展异常的对象
select segment_name,segment_type,tablespace_name,
(extents/max_extents)*100 Percent from sys.DBA_segments
where max_extents!=0 and (extents/max_extents)*100>=95
order by percent;

SQL> select segment_name,segment_type,tablespace_name,
2 (extents/max_extents)*100 Percent
3 from sys.DBA_segments
4 where max_extents!=0 and (extents/max_extents)*100>=95
5 order by percent;

no rows selected

如果有记录返回,则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数.

13 . 检查system表空间内的内容
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and owner!='SYS'
and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM'
and owner!='SYS' and owner!='SYSTEM';
SQL> select distinct(owner) from dba_tables
2 where tablespace_name='SYSTEM' and
3 owner!='SYS' and owner!='SYSTEM'
4 union
5 select distinct(owner) from dba_indexes
6 where tablespace_name='SYSTEM' and
7 owner!='SYS' and owner!='SYSTEM';

OWNER
------------------------------
MDSYS
OLAPSYS
OUTLN

如果有记录返回,则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间,同时应该检查这些对象属主的缺省表空间值,

14. 检查对象的下一扩展与表空间的最大扩展值
select a.table_name,a.next_extent,a.tablespace_name
from all_tables a,
(select tablespace_name,max(bytes) as big_chunk
from dba_free_space group by tablespace_name) f
where f.tablespace_name=a.tablespace_name
and a.next_extent>f.big_chunk
union
select a.index_name,a.next_extent,a.tablespace_name
from all_indexes a,
(select tablespace_name,max(bytes) as big_chunk
from dba_free_space
group by tablespace_name) f
where f.tablespace_name=a.tablespace_name
and a.next_extent>f.big_chunk;

SQL> select a.table_name,a.next_extent,a.tablespace_name
2 from all_tables a,
3 (select tablespace_name,max(bytes) as big_chunk
4 from dba_free_space group by tablespace_name) f
5 where f.tablespace_name=a.tablespace_name
6 and a.next_extent>f.big_chunk
7 union
8 select a.index_name,a.next_extent,a.tablespace_name
9 from all_indexes a,
10 (select tablespace_name,max(bytes) as big_chunk
11 from dba_free_space
12 group by tablespace_name) f
13 where f.tablespace_name=a.tablespace_name
14 and a.next_extent>f.big_chunk;

no rows selected

如果有记录返回,则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值,需调整相应表空间的存储参数

------分隔线----------------------------
标签(Tag):数据库 oralce
------分隔线----------------------------
推荐内容
猜你感兴趣