Oracle11G日常维护手册

Oracle Database 日常维护手册 目录 1 登陆到数据库 3 1 1 服务器端配置 Listener 3 1 2 客户端 tnsnames 4 1 3 检查 Oracle Listener 5 1 4 登陆数据库的方式 5 1 5 数据库的启动 5 1 6 关闭数据库 6 2 用户管理 6 2 1 检察用户 profile 6 2 2 查看用户 profile 参数 6 3 检查数据库基本状况 7 3 1 检查数据库创建日期 7 3 2 检查数据库版本信息 7 3 3 检查实例状态 7 3 4 查看前台进程 7 3 5 查看数据库连接的 session 8 3 6 查看连接到数据库的模式 8 3 7 查看并发连接数 8 3 8 查看最大的连接 processes 8 3 9 监控系统后台进程 8 3 10 查看数据库初始化参数 8 3 11 检查 PGA 使用情况 8 3 12 检查 SGA 状态 8 3 13 检查 Oracle 服务进程 8 3 14 检查 Oracle 监听状态 9 3 15 检查监听进程是否存在 10 3 16 检查操作系统日志文件 10 3 17 检查 oracle 日志文件 10 3 18 检查 Oracle 核心转储目录 10 3 19 检查 Root 用户和 Oracle 用户的 email 11 4 检查 Oracle 对象状态 11 4 1 检查 Oracle 控制文件状态 11 4 2 检查 Oracle 在线日志状态 11 4 3 检查 Oracle 表空间的状态 12 4 4 检查 Oracle 所有数据文件状态 12 4 5 检查无效对象 12 4 6 检查所有回滚段状态 13 4 7 检查用户下的表 13 4 8 检查用户默认表空间 13 4 9 检查当前用户角色及权限 13 4 10 检查用户下的各个表的大小 13 4 11 检查一个表的创建时间 13 4 12 检查某个表的大小 13 4 13 检查每个表占用磁盘空间情况 14 5 检查 Oracle 相关资源的使用情况 14 5 1 检查 Oracle 初始化文件中相关参数值 14 5 2 检查数据库连接情况 15 5 3 检查系统磁盘空间 16 5 4 检查表空间使用情况 16 5 5 检查一些扩展异常的对象 18 5 6 检查表空间碎片情况 18 5 7 检查 system 表空间内的内容 18 5 8 检查对象的下一扩展与表空间的最大扩展值 19 5 9 检查 flash recovery area 空间 19 6 检查 Oracle 数据库性能 19 6 1 查询表空间读写情况 19 6 2 查询 redo log buffer 的繁忙程度 20 6 3 判断 undo 表空间的使用情况 20 6 4 分析日志组切换频率 21 6 5 查看等待事件 21 6 6 检查数据库 cpu I O 内存性能 22 6 7 内存使用情况 22 6 8 系统 I O 情况 22 6 9 系统负载情况 23 6 10 查看是否有僵死进程 23 6 11 检查缓冲区命中率 23 6 12 检查共享池命中率 24 6 13 检查排序区 24 6 14 检查日志缓冲区 24 6 15 检查失效的索引 24 6 16 检查不起作用的约束 24 6 17 检查无效的 trigger 25 6 18 检查尚未建立索引的表 25 6 19 检查运行时间长的 SQL 25 6 20 检查性能差的前 10 条 SQL 25 6 21 查看占 io 较大的正在运行的 session 25 6 22 检查消耗 CPU 最高的 PID 对应的 SQL 25 6 23 检查占用 CPU 多的 session 26 6 24 检查表空间的 IO 26 6 25 检查临时表空间 IO 26 6 26 检查锁和等待 26 7 检查数据库安全性 27 7 1 检查系统安全日志信息 27 7 2 检查登录失败的日志 27 7 3 检查用户修改密码 27 8 数据表空间日常维护 27 8 1 查看表空间的一些信息 27 8 2 创建表空间 28 8 3 表空间扩容 28 8 4 创建大数据文件 29 8 5 数据表空间文件迁移 29 8 6 不停机移动表空间文件 30 9 存储过程管理 30 9 1 找出特定用户的存储过程 30 9 2 通过表名找出存储过程 30 9 3 查看存储过程内容 30 10 触发器管理 30 10 1 找出数据库中所有触发器 30 10 2 找出特定用户的触发器 31 10 3 找出当前用户定义的触发器 31 10 4 查看某个用户自定义的触发器内容 31 10 5 查看某个表关联的触发器 31 10 6 查看当前用户所有触发器及存储过程 31 11 Redo Log 管理 31 11 1 离线迁移日志文件 31 11 2 在线更改 Redo Log 文件容量 32 1 登陆到数据库 1 1 服务器端配置 Listener LISTENER DESCRIPTION LIST DESCRIPTION ADDRESS PROTOCOL IPC KEY racdb1 ADDRESS PROTOCOL TCP HOST 192 168 137 10 PORT 1521 ADR BASE LISTENER u01 app oracle SID LIST LISTENER SID LIST SID DESC BEQUEATH CONFIG GLOBAL DBNAME racdb1 SID NAME racdb1 ORACLE HOME u01 app oracle product 11 2 0 dbhome 1 PRESPAWN CONFIG PRESPAWN MAX 20 PRESPAWN LIST PRESPAWN DESC PROTOCOL tcp POOL SIZE 2 TIMEOUT 1 说明 GLOBAL DBNAME racdb1 Service 名称 在客户端一定配置和他相同 SID NAME racdb1 实例名称 这个要和 SID 相同 GLOBAL DBNAME 可以不等于 SID NAME 客户端根据 tnsname ora 中的 SERVICE NAME 和地址 ADDRESS PROTOCOL TCP HOST racdb1 PORT 1521 到这个地址去访问监听器 然后监听器根据文件 lisnter ora 文件中 的 GLOBAL NAME 来判断是否有一个 GLOBAL DBNAME 和 SERVICE NAME 相等 如果相 等 则建立客户端到 SID 标识的服务端实例的连接 在客户端上我们可以使用 tnsping 命令 来测试 1 2 客户端 tnsnames racdb1 description address list address protocol tcp host 192 168 137 10 port 1521 connect data service name racdb1 ur a 说明 这里的 service name racdb1 就是在服务器端的 GLOBAL DBNAME racdb1 oracle racdb1 tnsping racdb1 TNS Ping Utility for Linux Version 11 2 0 1 0 Production on 21 JAN 2015 14 51 49 Copyright c 1997 2009 Oracle All rights reserved TNS 03502 Insufficient arguments Usage tnsping oracle racdb1 tnsping racdb1 TNS Ping Utility for Linux Version 11 2 0 1 0 Production on 21 JAN 2015 14 51 55 Copyright c 1997 2009 Oracle All rights reserved Used parameter files Used TNSNAMES adapter to resolve the alias Attempting to contact description address list address protocol tcp host racdb1 port 1521 connect data service name racdb1 ur a OK 0 msec 1 3 检查 Oracle Listener lsnrctl stop lsnrctl start lsnrctl status lsnrctl service 1 4 登陆数据库的方式 说明 修改登陆 oracle 认证模式 默认情况下我们 oracle 安装好后是使用操作系统用户的验证 所以这里如果我们使用 sys 用 户不用密码就可以登录 如果我们想使用 oracle 密码文件验证的话我们就要进入下列文件夹 cd u01 app oracle product 11 2 0 dbhome 1 network admin 修改 sqlnet ora 增加下列命令 SQLNET AUTHENTICATION SERVICES NONE 说明 配置了 tnsnames 登录数据库方式 oracle racdb1 sqlplus scott 111111 racdb1 sqlplus nolog 使用 scott 登陆到指定数据库 racdb1 conn sys 111111 racdb1 AS SYSDBA 察看登陆到了哪个数据库实例 select instance name from v instance 使用 sys 用户登陆 conn sys change on installer as sysdba 用 sysdba 登陆 conn as sysdba 使用 sys 用户登录 conn sys change on install as sysdba conn as sysdba 连接数据库 conn scott 111111 使用 scott 进行连接 1 5 数据库的启动 数据库启动方式 方式 含义 startup 启动实例 装载数据库 打开数据库 startup nomount 启动实例 不加载数据库 startup mount 启动实例 加载数据库但不打开数据库 startup restrict 启动过程中限制访问数据库 startup force 强制数据库启动 startup pfile oracle app oracle product 10g 使用非缺省参数文件启动数据库 以特定文件中指定参数启动 数据库 本例为 方式 含义 dbs initminos ora oracle app oracle product 11g dbs initminos ora startup 启动实例 装载数据库 打开数据库 startup open racdb1 startup nomount 启动数据库实例 该步骤只是启动了一个数据库实例 在此状态下我们可以访问下列结构文件 Select from v instance Select from v bgprocess Select from v sga 利用以前读取的参数文件查找控制文件 这些控制文件包含数据文件名和重做日志名 然后将 数据库装载 alter database mount 一旦这一步完成我们就可以看到下列状态 select from v database select from v tablespace select from v log 实例验证数据文件及日志文件并启动数据库 alter database open 打开数据库 startup restrict 启动过程中限制访问数据库 alter system enable restricted session grant restrict session to scott 上述命令是只有用户拥有 restrict 权限才可以连接 启动数据库后起的进程 select name DESCRIPTION from v bgprocess where paddr 00 1 6 关闭数据库 shutdown normal 等待用户完成工作然后关闭 Shutdown transactional 等待用户完成工作但是强制关闭连接 shutdown immediate 立即关闭 当前所有做工作回滚到一致状态 断开其连接 shutdown abort 强制关闭 非干净关闭 下次重起后要回滚日志 2 用户管理 2 1 检察用户 profile select username profile from dba users where username SCOTT 2 2 查看用户 profile 参数 select from dba profiles where profile DEFAULT 3 检查数据库基本状况 3 1 检查数据库创建日期 Select Created Log Mode From V Database CREATED LOG MODE 2014 8 17 1 NOARCHIVELOG 3 2 检查数据库版本信息 Select version from Product component version where SUBSTR PRODUCT 1 6 Oracle 3 3 检查实例状态 SQL SELECT inst id instance name host name VERSION TO CHAR startup time yyyy mm dd hh24 mi ss startup time status archiver database status FROM gv instance SELECT inst id dbid NAME TO CHAR created yyyy mm dd hh24 mi ss created log mo de TO CHAR version time yyyy mm dd hh24 mi ss version time open mode FROM gv database INST ID DBID NAME CREATED LOG MODE VERSION TIME OPEN MODE 1 94264408 RACDB1 2014 08 17 16 34 32 NOARCHIVELOG 2014 08 17 16 34 32 REA D WRITE 说明 其中 STATUS 表示 Oracle 当前的实例状态 必须为 OPEN DATABASE STATUS 表示 Oracl e 当前数据库的状态 必须为 ACTIVE SQL select name log mode open mode from v database NAME LOG MODE OPEN MODE RACDB1 ARCHIVELOG READ WRITE 说明 其中 LOG MODE 表示 Oracle 当前的归档方式 ARCHIVELOG 表示数据库运行在归档模式下 NOARCHIVELOG 表示数据库运行在非归档模式下 在我们的系统中数据库必须运行在归档 方式下 3 4 查看前台进程 ps C oracle o rsz sid cmd grep LO free m egrep v total buffers awk print 1 3 说明 Oracle 的前台进程是操作系统进程 它和 oracle session 一一对应 官方建议 oracle sesson 1 5 process 20 3 5 查看数据库连接的 session SQL select count from v session 3 6 查看连接到数据库的模式 select program from v process order by program select d NAME s NAME from v dispatcher d v shared server s v circuit c where d PADDR c DISPATCHER and s PADDR c SERVER 说明 S012 以 s001 等就是共享模式连接 3 7 查看并发连接数 select count from v session where status ACTIVE 3 8 查看最大的连接 processes SQL select value from v parameter where name processes 3 9 监控系统后台进程 SQL Select name Description From V BGPROCESS Where Paddr 00 3 10 查看数据库初始化参数 select name value from v parameter where isbasic TRUE order by name 说明 这些参数是当前数据库已经应用的参数 3 11 检查 PGA 使用情况 select name value from v pgastat where name in maximum PGA allocated total PGA allocated 3 12 检查 SGA 状态 SELECT request misses request failures FROM v shared pool reserved Select component current size min size max size from v sga dynamic components REQUEST MISSES REQUEST FAILURES 0 0 说明 期望结果 request misses 和 request failures 应该接近于 0 巡检说明 request misses 是保留列表没有满足请求的可用内存片从而开始利用 LRU 列表 刷新对象的次数 request failures 是未找到满足请求的内存次数 3 13 检查 Oracle 服务进程 oracle racdb1 ps ef grep ora grep v grep VALUE u01 app oracle diag rdbms racdb1 racdb1 trace cat u01 app oracle diag rdbms racdb1 racdb1 trace alert racdb1 log grep ora cat u01 app oracle diag rdbms racdb1 racdb1 trace alert racdb1 log grep err oracle racdb1 trace cat u01 app oracle diag rdbms racdb1 racdb1 trace alert racdb1 log grep fail 说明 Oracle 在运行过程中 会在警告日志文件 alert SID log 中记录数据库的一些运行情况 数 据库的启动 关闭 启动时的非缺省参数 数据库的重做日志切换情况 记录每次切换的 时间 及如果因为检查点 checkpoint 操作没有执行完成造成不能切换 会记录不能切换的 原因 对数据库进行的某些操作 如创建或删除表空间 增加数据文件 数据库发生的错 误 如表空间不够 出现坏块 数据库内部错误 ORA 600 等 定期检查日志文件 根据 日志中发现的问题及时进行处理 问题 处理 启动参数不对 检查初始化参数文件 因为检查点操作或归档操作没有完成造 重做日志不能切换 如果经常发生这样的情况 可以考虑增加重做日 日志文件组 想办法提高检查点或归档操作的效 率 有人未经授权删除了表空间 检查数据库的安全问题 是否密码太简单 如有 必要 撤消某些用户的系统权限 出现坏块 检查是否是硬件问题 如磁盘本生有坏块 如果 不是 检查是那个数据库对象出现了坏块 对这 个对象进行重建 表空间不够 增加数据文件到相应的表空间 出现 ORA 600 根据日志文件的内容查看相应的 TRC 文件 如果 是 Oracle 的 bug 要及时打上相应的补丁 3 18 检查 Oracle 核心转储目录 SQL select value from v parameter where name core dump dest VALUE u01 app oracle diag rdbms racdb1 racdb1 cdump oracle racdb1 cdump ls u01 app oracle diag rdbms racdb1 racdb1 cdump trc wc l 说明 如果上面命令的结果每天都在增长 则说明 Oracle 进程经常发生核心转储 这说明某些用 户进程或者数据库后台进程由于无法处理的原因而异常退出 频繁的核心转储特别是数据 库后台进程的核心转储会导致数据库异常终止 Oracle 常用日志文件位置查询 select from v parameter where name background dump dest select from v parameter where name user dump dest select from v parameter where name core dump dest select from v parameter where name audit file dest select from v parameter where name audit syslog level 3 19 检查 Root 用户和 Oracle 用户的 email oracle racdb1 racdb1 tail n 200 var mail oracle oracle racdb1 racdb1 tail n 200 var mail root 说明 查看有无与 Oracle 用户相关的出错信息 4 检查 Oracle 对象状态 在本节主要检查相关 Oracle 对象的状态 包含 检查 Oracle 控制文件状态 检查 Oracle 在 线日志状态 检查 Oracle 表空间的状态 检查 Oracle 所有数据文件状态 检查 Oracle 所有 表 索引 存储过程 触发器 包等对象的状态 检查 Oracle 所有回滚段的状态 总共六 个部分 4 1 检查 Oracle 控制文件状态 SQL select status name from v controlfile STATUS NAME u01 app oracle oradata racdb1 control01 ctl u01 app oracle flash recovery area racdb1 control02 ctl 说明 输出结果应该有 2 条以上 包含 2 条 的记录 STATUS 应该为空 状态为空表示控制文件 状态正常 4 2 检查 Oracle 在线日志状态 SQL select group status type member from v logfile GROUP STATUS TYPE MEMBER 3 ONLINE u01 app oracle oradata racdb1 redo03 log 2 ONLINE u01 app oracle oradata racdb1 redo02 log 1 ONLINE u01 app oracle oradata racdb1 redo01 log 说明 输出结果应该有 3 条以上 包含 3 条 记录 STATUS 应该为非 INVALID 非 DELETED 注 STATUS 显示为空表示正常 4 3 检查 Oracle 表空间的状态 SQL select tablespace name status from dba tablespaces TABLESPACE NAME STATUS SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE SCOTTSPACE06 ONLINE 6 rows selected 说明 输出结果中 STATUS 应该都为 ONLINE 4 4 检查 Oracle 所有数据文件状态 SQL select name status from v datafile NAME STATUS u01 app oracle oradata racdb1 system01 dbf SYSTEM u01 app oracle oradata racdb1 sysaux01 dbf ONLINE u01 app oracle oradata racdb1 undotbs01 dbf ONLINE u01 app oracle oradata racdb1 users01 dbf ONLINE u01 app oracle oradata racdb1 SCOTTSPACE06 dbf ONLINE 说明 输出结果中 STATUS 应该都为 ONLINE 或者 SQL select file name status from dba data files FILE NAME STATUS u01 app oracle oradata racdb1 users01 dbf AVAILABLE u01 app oracle oradata racdb1 undotbs01 dbf AVAILABLE u01 app oracle oradata racdb1 sysaux01 dbf AVAILABLE u01 app oracle oradata racdb1 system01 dbf AVAILABLE u01 app oracle oradata racdb1 SCOTTSPACE06 dbf AVAILABLE 说明 输出结果中 STATUS 应该都为 AVAILABLE 4 5 检查无效对象 select owner object name object type from dba objects where status VALID and owner SYS and owner SYSTEM no rows selecte 说明 如果有记录返回 则说明存在无效对象 若这些对象与应用相关 那么需要重新编译生成 这个对象 或者 SELECT owner object name object type FROM dba objects WHERE status INVALID 4 6 检查所有回滚段状态 SQL select segment name status from dba rollback segs SEGMENT NAME STATUS SYSTEM ONLINE SYSSMU10 3550978943 ONLINE SYSSMU9 1424341975 ONLINE SYSSMU8 2012382730 ONLINE SYSSMU7 3286610060 ONLINE SYSSMU6 2443381498 ONLINE SYSSMU5 1527469038 ONLINE SYSSMU4 1152005954 ONLINE SYSSMU3 2097677531 ONLINE SYSSMU2 2232571081 ONLINE SYSSMU1 3780397527 ONLINE 11 rows selected 说明 输出结果中所有回滚段的 STATUS 应该为 ONLINE 4 7 检查用户下的表 select from user tables 4 8 检查用户默认表空间 select username default tablespace temporary tablespace from dba users where USERNAME SCOTT 4 9 检查当前用户角色及权限 select from dba role privs where GRANTEE SCOTT select from dba sys privs where GRANTEE SCOTT select from dba tab privs where GRANTEE SCOTT 4 10 检查用户下的各个表的大小 Select Segment Name Sum bytes 1024 1024 MB From dba Extents where OWNER SCOTT Group By Segment Name order by sum bytes 1024 1024 desc 4 11 检查一个表的创建时间 select object name created from dba objects where object name upper 4 12 检查某个表的大小 select sum bytes 1024 1024 as size M from dba segments where segment name upper 4 13 检查每个表占用磁盘空间情况 select b file id file ID b tablespace name tablespace name b bytes Bytes b bytes sum nvl a bytes 0 used sum nvl a bytes 0 free sum nvl a bytes 0 b bytes 100 Percent from dba free space a dba data files b where a file id b file id group by b tablespace name b file id b bytes order by b file id 5 检查 Oracle 相关资源的使用情况 在本节主要检查 Oracle 相关资源的使用情况 包含 检查 Oracle 初始化文件中相关的参数 值 检查数据库连接情况 检查系统磁盘空间 检查 Oracle 各个表空间使用情况 检查一 些扩展异常的对象 检查 system 表空间内的内容 检查对象的下一扩展与表空间的最大扩 展值 总共七个部分 5 1 检查 Oracle 初始化文件中相关参数值 SQL select resource name max utilization initial allocation limit value from v resource limit RESOURCE NAME MAX UTILIZATION INITIAL ALLOCATION LIMIT VALUE processes 63 150 150 sessions 72 247 247 enqueue locks 34 3010 3010 enqueue resources 38 1304 UNLIMITED ges procs 0 0 0 ges ress 0 0 UNLIMITED ges locks 0 0 UNLIMITED ges cache ress 0 0 UNLIMITED ges reg msgs 0 0 UNLIMITED ges big msgs 0 0 UNLIMITED ges rsv msgs 0 0 0 gcs resources 0 0 0 gcs shadows 0 0 0 dml locks 0 1084 UNLIMITED temporary table locks 0 UNLIMITED UNLIMITED transactions 0 271 UNLIMITED branches 0 271 UNLIMITED cmtcallbk 1 271 UNLIMITED max rollback segments 11 271 65535 sort segment locks 1 UNLIMITED UNLIMITED RESOURCE NAME MAX UTILIZATION INITIAL ALLOCATION LIMIT VALUE k2q locks 0 494 UNLIMITED max shared servers 1 UNLIMITED UNLIMITED parallel max servers 0 10 3600 23 rows selected 说明 若 LIMIT VALU MAX UTILIZATION select count from v session select s osuser os user name decode sign 48 command 1 to char command Action Code to char command action p program oracle process status session status s terminal terminal s program program s username user name s fixed table sequence activity meter query 0 memory 0 max memory 0 cpu usage s sid s serial serial num from v session s v process p where s paddr p addr and s type USER order by s username s osuser SQL select sid serial username program machine status from v session 说明 其中 SID 会话 session 的 ID 号 SERIAL 会话的序列号 和 SID 一起用来唯一标识一个会话 USERNAME 建立该会话的用户名 PROGRAM 这个会话是用什么工具连接到数据库的 STATUS 当前这个会话的状态 ACTIVE 表示会话正在执行某些任务 INACTIVE 表示当前会 话没有执行任何操作 如果建立了过多的连接 会消耗数据库的资源 同时 对一些 挂死 的连接可能需要手 工进行清理 如果 DBA 要手工断开某个会话 则执行 一般不建议使用这种方式去杀掉 数据库的连接 这样有时候 session 不会断开 容易引起死连接 建议通过 sid 查到操作系 统的 spid 使用 ps ef grep spidno 的方式确认 spid 不是 ORACLE 的后台进程 使用操作系统 的 kill 9 命令杀掉连接 alter system kill session SID SERIAL 注意 上例中 SID 为 1 到 10 USERNAME 列为空 的会话 是 Oracle 的后台进程 不要对这 些会话进行任何操作 5 3 检查系统磁盘空间 oracle racdb1 racdb1 df h Filesystem Size Used Avail Use Mounted on dev mapper VgMasterRoot LogVolRoot 9 5G 433M 8 6G 5 tmpfs 935M 315M 621M 34 dev shm dev mapper VgMasterU01 LogVolU01 20G 6 7G 12G 37 u01 dev sda1 9 5G 91M 9 0G 1 boot dev mapper VgMasterHome LogVolHome 9 5G 72M 9 0G 1 home dev mapper VgMasterTemp LogVolTmp 3 7G 7 7M 3 5G 1 tmp dev mapper VgMasterUsr LogVolUsr 9 5G 1 3G 7 8G 15 usr dev mapper VgMasterVar LogVolVar 9 5G 118M 8 9G 2 var 5 4 检查表空间使用情况 SELECT df file id df file name df size mb NVL free maxfree 0 maxfree mb ROUND NVL free free mb 0 2 free mb 100 ROUND 100 0 NVL free free mb 0 df size mb 2 pct used ROUND 100 0 NVL free free mb 0 df size mb 2 pct free FROM SELECT file id file name tablespace name BYTES 1048576 size mb FROM dba data files df SELECT file id SUM BYTES 1048576 free mb TRUNC MAX BYTES 1024 1024 2 maxfree FROM dba free space GROUP BY file id free WHERE df file id free file id ORDER BY 7 TABLESPACE NAME TOTAL FREE Free SYSTEM 680 6 1 SYSAUX 530 28 5 USERS 5 3 60 UNDOTBS1 90 70 78 SCOTTSPACE06 5 4 80 SQL 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 TABLESPACE NAME USED PERCENT SYSAUX 95 259433962 UNDOTBS1 20 347222222 USERS 31 25 SYSTEM 99 172794117 SCOTTSPACE06 20 SELECT D TABLESPACE NAME FILE NAME FILE NAME SPACE SUM SPACE M SPACE NVL F REE SPACE 0 USED SPACE M ROUND 1 NVL FREE SPACE 0 SPACE 100 2 USED RATE AUTOEXTENSIBLE FROM SELECT FILE ID FILE NAME TABLESPACE NAME ROUND SUM BYTES 1024 1024 2 S PACE SUM BLOCKS BLOCKS FROM DBA DATA FILES GROUP BY TABLESPACE NAME FILE ID FILE NAME D SELECT FILE ID TABLESPACE NAME ROUND SUM BYTES 1024 1024 2 FREE SPACE FROM DBA FREE SPACE GROUP BY TABLESPACE NAME FILE ID E SELECT FILE ID AUTOEXTENSIBLE FROM DBA DATA FILES F WHERE D TABLESPACE NAME E TABLESPACE NAME AND D FILE ID E FILE ID AND D FILE ID F FILE ID UNION ALL if have tempfile SELECT D TABLESPACE NAME FILE NAME FILE NAME SPACE SUM SPACE M USED SPAC E USED SPACE M ROUND NVL USED SPACE 0 SPACE 100 2 USED RATE AUTOEXTENSIBLE FROM SELECT FILE ID FILE NAME TABLESPACE NAME ROUND SUM BYTES 1024 1024 2 SPACE S UM BLOCKS BLOCKS FROM DBA TEMP FILES GROUP BY TABLESPACE NAME FILE ID FILE NAME D SELECT FILE ID TABLESPACE NAME ROUND SUM BYTES USED 1024 1024 2 USED SPACE ROUND SUM BYTES FREE 1024 1024 2 FREE SPACE FROM V TEMP SPACE HEADER GROUP BY TABLESPACE NAME FILE ID E SELECT FILE ID AUTOEXTENSIBLE FROM DBA TEMP FILES F WHERE D TABLESPACE NAME E TABLESPACE NAME AND D FILE ID E FILE ID AND D FILE ID F FILE ID ORDER BY TABLESPACE NAME FILE NAME 说明 如果空闲率 Free 小于 10 以上 包含 10 则注意要增加数据文件来扩展表空间而不要 是用数据文件的自动扩展功能 请不要对表空间增加过多的数据文件 增加数据文件的原 则是每个数据文件大小为 2G 或者 4G 自动扩展的最大限制在 8G 5 5 检查一些扩展异常的对象 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 no rows selected 说明 如果有记录返回 则这些对象的扩展已经快达到它定义时的最大扩展值 对于这些对象要 修改它的存储结构参数 5 6 检查表空间碎片情况 select tablespace name count chunks max bytes 1024 1024 max chunk sum bytes 1024 1024 total space from dba free space group by tablespace name 说明 其中 CHUNKS 列表示表空间中有多少可用的空闲块 每个空闲块是由一些 连续的 Oracle 数据块组成 如果这样的空闲块过多 比如平均到每个数据 文件上超过了 100 个 那么该表空间的碎片状况就比较严重了 可以尝试用以 下的 SQL 命令进行表空间相邻 碎片的接合 如果最大可用块 max chunk 与总大小 total space 相比太小 要考虑接合表空 间碎片或重建某些数据库对象 碎片接合的方法 alter tablespace users coalesce 5 7 检查 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 说明 如果记录返回 则表明 system 表空间内存在一些非 system 和 sys 用户的对象 应该进一步 检查这些对象是否与我们应用相关 如果相关请把这些对象移到非 System 表空间 同时应 该检查这些对象属主的缺省表空间值 5 8 检查对象的下一扩展与表空间的最大扩展值 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 no rows selected 说明 如果有记录返回 则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值 需 调整相应表空间的存储参数 5 9 检查 flash recovery area 空间 SELECT substr name 1 30 name space limit AS quota space used AS used space reclaimable AS reclaimable number of files AS files FROM v recovery file dest 说明 默认情况下开启归档后的文件存放路径 可以更改路径 监控该区域空间是否正常 防止 数据库被挂起 6 检查 Oracle 数据库性能 记录数据库的 cpu 使用 IO 内存等使用情况 使用 vmstat iostat sar top 等命令进行信息 收集并检查这些信息 判断资源使用情况 6 1 查询表空间读写情况 SQL SELECT name phyrds phywrts FROM V datafile df V filestat fs WHERE df file fs file NAME PHYRDS PHYWRTS u01 app oracle oradata racdb1 system01 dbf 11356 563 u01 app oracle oradata racdb1 sysaux01 dbf 1869 3219 u01 app oracle oradata racdb1 undotbs01 dbf 32 1449 u01 app oracle oradata racdb1 users01 dbf 90 0 u01 app oracle oradata racdb1 SCOTTSPACE06 dbf 3 0 说明 其中 PHYRDS 代表已完成的物理读次数 PHYWRTS 代表 DBWR 完成的物理写次数 数据库的 IO 负载系统的瓶颈在于磁盘 IO 所以一般可以通过操作系统的一些命令来确认 一个系统是否是存在 IO 负载问题 比如 iostat sar 等 6 2 查询 redo log buffer 的繁忙程度 SQL SELECT retries value entries valu