1.Linux系统环境下
1.1查看系统使用情况,进程相关信息
top
ps -ef|grep ora_
1.2.查看系统日志
tail -f /var/log/messages
1.3.查看系统空间使用情况
df -h
1.4.查看oracle告警日志
[root@localhost ~]# find / -name alert*.log
/oracle/admin/orclbdump/alert_orcl.log
/opt/oracle/product/9.2.0/rdbms/log/alert_orcl.log
vim /oracle/admin/orcl/bdump/alert_orcl.log
vim /opt/oracle/product/9.2.0/rdbms/log/alert_orcl.log
du -sh /opt/oracle/product/9.2.0/rdbms/log/alert_orcl.log
du -sh /oracle/admin/orcl/bdump/alert_orcl.log
1.5.查看监听状态
lsnrctl status
1.6.查看监听日志信息
[root@localhost ~]# find / -name listen*.log
/opt/oracle/product/9.2.0/network/log/listener.log
tial -n 100 -f /opt/oracle/product/9.2.0/network/log/listener.log
du -sh /opt/oracle/product/9.2.0/network/log/listener.log
oracle的监听日志太大,正确的删除步骤
(1)进入$ORACLE_HOME/network/log,查看日志大小 du -sh
(2)把 listen log 关闭.
lsnrctl set log_status off;
(3)把日志改名,mv listener.log listener.log_bak
(4)启动listen log
lsnrctl set log_status on;此时检查log目录下,会自动生成一个新的log文件,rm掉之前的监听文件即可
2.oracle环境下
2.1查看数据库状态
select instance_name,status from v$instance;
2.2.查看数据文件的状态记录,如果是offline并做恢复
--set pause on 使用分屏查看 按回车下一页
set pagesize 999
col file_name for a50
select file_name,status from dba_data_files;
2.3.检查表空间状态
select tablespace_name,status from dba_tablespaces;
2.4.查看表空间使用情况
select * from user_objects where object_type='PROCEDURE';
select a.file_id fileid,
a.file_name 文件名,
a.tablespace_name 表空间名,
a.bytes total_bytes,
(a.bytes-sum(nvl(b.bytes,0))) 已使用,
sum(nvl(b.bytes,0)) 未使用,
sum(nvl(b.bytes,0))/(a.bytes)*100 未使用率
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id
group by a.tablespace_name,a.file_id,a.bytes,a.file_name
order by a.file_id;
set linesize 999
col filename for a40
select a.file_id fileid,
a.file_name filename,
a.tablespace_name tbsname,
a.bytes total_bytes,
(a.bytes-sum(nvl(b.bytes,0))) used,
sum(nvl(b.bytes,0)) free,
sum(nvl(b.bytes,0))/(a.bytes)*100 "free%"
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id
group by a.tablespace_name,a.file_id,a.bytes,a.file_name
order by a.file_id;
select a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
round((total - free),2) "表空间使用大小",
round(total/(1024*1024*1024),2) "表空间大小(G)",
round(free/(1024*1024*1024),2) "表空间剩余大小(G)",
round((total - free)/(1024*1024*1024),2) "表空间使用大小(G)",
round((total - free)/total,4)*100 "使用率%"
from (select tablespace_name,sum(bytes) free
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;
2.5.查看table的空间使用情况
analyze table scott.t1 compute statistics;
select table_name,blocks,empty_blocks
from dba_tables
where table_name='T1';
2.6.控制文件、日志文件状态
v$controlfile
v$log、v$logfile
select group#,thread#,sequence#,members,archived,status from v$log;
col member for a40
select * from v$logfile;
2.7.查看无效的数据库对象
col owner for a20
col object_name for a20
col object_type for a20
select owner,object_name,object_type from dba_objects where status ='INVALID';
2.8.检查不起作用的约束
col owner for a10
col constraint_name for a20
col table_name for a20
col constraint_type for a10
select owner,constraint_name,table_name,constraint_type,status from dba_constraints where status ='DISABLED' and constraint_type='P';
2.9.检查无效的trigger
select owner,trigger_name,table_name,status from dba_triggers where status ='DISABLED';
2.10.查看当前系统时间
[oracle@zyx admin]$ date +"%Y-%m-%d %H:%M:%S"
2016-04-28 18:20:37
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2016-04-28 18:21:01
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30130773/viewspace-2093641/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2093641/