windows下Oracle日常维护指南

整理by wufh

1.在命令行下启动Oracle管理控制台:cmd->  oemapp console
//svrmgrl  sqlplus/nolog
2.版本：select * from v$version;
3.安装日期：select created from v$database;
4.检查oracle监听进程：cmd->lsnrctl status
5.实例状态：select instance_name ,version,status ,database_status from v$instance;
          select name,log_mode,open_mode from v$database;
6.oracle控制文件状态：select * from v$controlfile;
7.日志状态：select * from v$logfile;
8.表空间状态：select tablespace_name,status from dba_tablespaces;
9.所有文件状态：select name,status from v$datafile;
10.检查所有表、索引、存储过程、触发器和包等对象的状态：
select owner,object_name,object_type from dba_objects where status!='valid' and owner!='sys' and owner!='system';
11.检查oracle所有回滚段的状态：select segment_name,status from dba_rollback_segs;
12.检查Oracle初始化文件中相关的参数值:
select resource_name,max_utilization,initial_allocation,limit_value from v$resource_limit;
13.检查Oracle各个表空间的增长情况:
select
A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
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;
14.检查一些扩展异常的对象:
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;
15.检查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 客户成功 我们成功 17
Oracle日常维护指导手册
owner!='SYS' and owner!='SYSTEM';
如果有记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值，具体方法可以寻求杭州Call Center的技术支持。
16.检查对象的下一扩展与表空间的最大扩展值:
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 客户成功 我们成功 18
Oracle日常维护指导手册
and a.next_extent > f.big_chunk;
sql>exit
no rows selected
如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表空间的存储参数，具体方法可以寻求杭州Call Center的技术支持。
17.数据库表空间使用情况监控:
select tablespace_name,
count(*) chunks ,
max(bytes/1024/1024) max_chunk
  from dba_free_space
group by tablespace_name;
表空间相邻碎片的接合:alter tablespace 表空间名 coalesce;
18.查看数据库的连接情况:
select sid,serial#,username,program,machine,status
from v$session;
DBA要手工断开某个会话:alter system kill session 'SID,SERIAL#';
19.控制文件的备份:
alter database backup controlfile to '/home/backup/control.bak';or
alter database
 backup controlfile to trace;
20.检查数据库文件的状态:select file_name,status from dba_data_files;
21.检查数据库定时作业的完成情况:select job,log_user,last_date,failures from dba_jobs;