查看oracle是否正常、表空间 （AIX）

REF

http://blog.itpub.net/23556054/viewspace-1115506/

 

===================================================================

查看 oracle 数据库是否启动：

一、LINUX:

1、用  ps -ef|grep ora  查看,是否有oracle的进程,linux下还可以用ipcs查看

2、查看监听的方法

  cd /oracle/product/10.2.0/db_1/bin/
  lsnrctl status
  启动监听
  lsnrctl start

Message 1053 not found; No message file for product=network, facility=TNSMessage 1020 not found; No message file for product=network, facility=TNSMessage 1021 not found; No message file for product=network, facility=TNSMessage 1022 not found; No message file for product=network, facility=TNSMessage 1023 not found; No message file for product=network, facility=TNSMessage 1026 not found; No message file for product=network, facility=TNSMessage 1034 not found; No message file for product=network, facility=TNSMessage 1024 not found; No message file for product=network, facility=TNSMessage 1025 not found; No message file for product=network, facility=TNSMessage 1040 not found; No message file for product=network, facility=TNSMessage 1033 not found; No message file for product=network, facility=TNSMessage 1028 not found; No message file for product=network, facility=TNSMessage 1415 not found; No message file for product=network, facility=TNS  Message 1050 not found; No message file for product=network, facility=TNS
  Message 1050 not found; No message file for product=network, facility=TNS
Message 1029 not found; No message file for product=network, facility=TNSMessage 1411 not found; No message file for product=network, facility=TNS
  Message 1408 not found; No message file for product=network, facility=TNS
  Message 1408 not found; No message file for product=network, facility=TNS
Message 1411 not found; No message file for product=network, facility=TNS
  Message 1408 not found; No message file for product=network, facility=TNS
Message 1411 not found; No message file for product=network, facility=TNS
  Message 1408 not found; No message file for product=network, facility=TNS
Message 1052 not found; No message file for product=network, facility=TNS#

 

===================================================================

二、windows:

进到数据库里面看数据库当前状态
select instance_name,status from v$instance;

如果status = open 就说明oracle服务正常。
查看监听，执行lsnrctl  status，就可以看监听所对应的实例。
是否有scott用户，可以查看dba_users这个表的用户状态。

==================================================================

三、检查操作系统空间使用状态
   Linux命令：df -h或df -g
   window:查看磁盘管理器

==================================================================

四、表空间检查

 1、select tablespace_name,sum(bytes)/1024/1024 as total_size
    from dba_data_files
    group by tablespace_name

2、select tablespace_name,sum(bytes)/1024/1024 as total_size
    from dba_free_space

    group by tablespace_name

 

查看Oracle表空间使用率

--查看数据库表空间使用率
select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct
from (
select tablespace_name, sum(bytes) /1024/1024 as MB
from dba_free_space group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files group by tablespace_name) total     
where free.tablespace_name = total.tablespace_name
order by used_pct desc;

 

--查看表空间总大小、使用率、剩余空间
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%"
from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name

 

 

 

==================================================================

五、启动过程：
sqlplus /nolog    or  (sqlplus sys/passwd as sysdba)
conn sys/password as sysdba
SQL> select status from v$instance
ORA-01034: ORACLE not available ------------shutdown
SQL> startup nomount         ------------started
SQL> alter database mount   ------------mounted
SQL> alter database open     ------------open
==================================================================

6、检查表空间碎片
select tablespace_name, sqrt(MAX(blocks)/sum(blocks))*(100/sqrt(sqrt(COUNT(blocks)))) as

"FSFI%"
 from dba_free_space
 group by tablespace_name

==================================================================