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
s
e
s
s
i
o
n
a
,
v
session a, v
sessiona,vsqlarea 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 %’
