oracle常用数据库巡检语句

1、检查表空间大小（避免由于空间不足导致的故障，可以监测数据增长情况）

select dbf.tablespace_name,
dbf.totalspace "当前大小(M)",
dbf.totalblocks as "数据块",
(dbf.totalspace-dfs.freespace) "已使用大小(M)",
dfs.freespace "剩余大小(M)",
dfs.freeblocks "剩余数据块",
dbf.totalsize "最大可扩展大小(G)",
(dfs.freespace / dbf.totalspace) * 100 "剩余百分比"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks,
sum(t.MAXBYTES)/1024/1024/1024 totalsize
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);

2、检查日志文件增长（避免日志文件增长过快，如果过快应该增大日志文件大小）

select 
to_char(first_time,'yyyy-mm-dd:hh24'),
count(*) 
from v$log_history 
group by to_char(first_time,'yyyy-mm-dd:hh24') 
order by 1;

3、检查无效的对象（清理数据库使用）

select owner,
object_name,
object_type 
from dba_objects 
where status='INVALID';

4、等待会话

select sid,event,sql_id,machine from v$session where wait_class<>'Idle';

5、正在执行的SQL

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
  from v$session a, v$sqlarea b
where a.sql_address = b.address;

6、查询某个时间段执行过的SQL

select SQL_TEXT,FIRST_LOAD_TIME,SQL_FULLTEXT
  from v$sqlarea
where FIRST_LOAD_TIME between '2015-04-03/10:00:00' and
       '2015-04-03/11:00:00' order by FIRST_LOAD_TIME

7、索引的综合查询

select 
user_indexes.table_name "表名", 
user_indexes.index_name "索引名",
uniqueness "索引类型", 
column_name "索引列"
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;

8、查询死进程

select 
b.owner,
b.object_name,
a.session_id,
a.locked_mode 
from v$locked_object a,dba_objects b
 where b.object_id = a.object_id;

处理死锁

SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID';
ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';

SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';
KILL -9  “刚才查出的SPID”

9、查询一段代码在那个包里出现中出现

SELECT *
  FROM ALL_SOURCE
 WHERE TYPE = 'PACKAGE BODY'
   AND OWNER = 'MARTYRWEB'
   and upper(text) like '%MERGE %'