clay111的备忘文章收集

oracle相关的东西

                                      

1、根据unix进程查询oracle正在执行的sql语句。
 select /*+ ORDERED */ sql_text
from v$sqltext a
where (a.hash_value,a.ADDRESS) in
(
select decode(sql_hash_value,'0',PREV_HASH_VALUE,sql_hash_value),
       decode(sql_address,'0',PREV_SQL_ADDR,SQL_ADDRESS)
   from v$session b
where b.paddr = (
select addr from v$process c
where c.spid = 32637
)
)
order by piece asc

32637为系统的进程号

2、查询系统耗时的sql

select username,sid,opname,
      round(sofar*100 / totalwork,0) || '%' as progress,
      time_remaining,sql_text
from v$session_longops , v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value

3、查询耗费io的sql

select username,sid,opname,
      round(sofar*100 / totalwork,0) || '%' as progress,
      time_remaining,sql_text ,time_remaining ,PARSE_CALLS,DISK_READS
from v$session_longops , v$sql
where
 DISK_READS >100
and sql_address = address
and sql_hash_value = hash_value

ORA-01536错误提示就是超出了磁盘配额限制，因为表空间还很空，并且是可以自动扩展的，所以只能是磁盘配额问题。

alter user XXX quota unlimited on users

ALTER USER XXX QUOTA UNLIMITED ON XXX;

ALTER USER XXX QUOTA UNLIMITED ON XXX;

除了表空间，还要考虑是不是临时表空间

【作者: clay111】【访问统计:】【2006年09月21日 星期四 13:29】【 加入博采】【打印】