在本节主要检查相关Oracle对象的状态，包含：检查Oracle控制文件状态，检查Oracle在线日志状态，检查Oracle表空间的状态，检查Oracle所有数据文件状态，检查Oracle所有表、索引、存储过程、触发器、包等对象的状态，检查Oracle所有回滚段的状态，总共六个部分。
1.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”应该为空。状态为空表示控制文件状态正常
1.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”显示为空表示正常。
1.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。
1.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”。
1.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';
1.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”。
1.7. 检查用户下的表
select *
from user_tables;
1.8. 检查用户默认表空间
select
username,default_tablespace, temporary_tablespace from dba_users where USERNAME='SCOTT';
1.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';
1.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;
1.11.
检查一个表的创建时间
select
object_name,created from dba_objects where
object_name=upper('&table_name');
1.12.
检查某个表的大小
select
sum(bytes)/(1024*1024) as "size(M)" from dba_segments
where
segment_name=upper('&table_name');
1.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;
