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