-- check the block running sql
select sid,serial#,sql_id,blocking_session,username from v$session where blocking_session>0;
-- find the current running sql
SELECT OSUSER,SERIAL#,SQL_TEXT,blocking_session
FROM V$SESSION, V$SQL
WHERE
V$SESSION.SQL_ADDRESS=V$SQL.ADDRESS
-- find all error objects
select * from dba_errors
-- check tablespace status
select t2.tname "tablespace",
round(t2.total, 2) "total/M",
round(t2.total - nvl(t1.free, 0), 2) "used/M",
round(nvl(t1.free, 0), 2) "free/M",
round((1 - (nvl(t1.free, 0) / t2.total)) * 100, 2) used_percent
from (select sum(nvl(a.bytes, 0)) / (1024 * 1024) free,
a.tablespace_name tname
from dba_free_space a
group by a.tablespace_name) t1,
(select sum(b.bytes) / (1024 * 1024) total, b.tablespace_name tname
from dba_data_files b
group by b.tablespace_name) t2
where t1.tname(+) = t2.tname
union
select tablespace_name || inst_id "tablespace",
total_blocks * vp.value / 1024 / 1024 "total/M",
used_blocks * vp.value / 1024 / 1024 "used/M",
free_blocks * vp.value / 1024 / 1024 "free/M",
round((used_blocks / total_blocks * 100), 2) used_percent
from gv$sort_segment gs, v$parameter vp
where vp.name = 'db_block_size'
order by used_percent;
-- check db objects status
select * from dba_errors