Oracle数据库多个表空间使用情况查询

-- 1. 统计数据表空间
select a.tablespace_name,a.total_tbs,b.one_tbs,f_data_div(one_tbs,total_tbs) from
(select tablespace_name,round(sum(bytes)/1024/1024,0) total_tbs from dba_data_files
where tablespace_name in ('xxxx_TABLESPACE','xxxx','xxxx_TABLESPACE')
group by tablespace_name) a
LEFT JOIN
(select tablespace_name,round(sum(bytes)/1024/1024,0) one_tbs from dba_segments
where tablespace_name in ('xxxx_TABLESPACE','xxxx','xxxx_TABLESPACE')
group by tablespace_name) b
ON a.tablespace_name = b.tablespace_name
order by f_data_div(one_tbs,total_tbs) desc;

依赖函数：

create or replace function f_data_div(div_up number,div_down number)
return number as
div_ratio number(10,2) ;
begin
  if (div_down = 0 or div_down is null or div_up is null) then
      div_ratio := 0 ;
  else
      div_ratio := round(div_up/div_down * 100,2) ;
  end if;
return div_ratio;
end;

-- 2. 查看正在执行的SQL脚本
select b.sql_text,a.* from gv$session a,gv$sqlarea b
where a.sql_id = b.sql_id
and b.sql_text <> 'SELECT sysdate FROM dual' ;

-- 3. 查询死锁
select c.sid,
c.SERIAL#,
a.OBJECT_ID,
a.ORACLE_USERNAME,
a.OS_USER_NAME,
b.object_name,
d.sql_text
from gv$locked_object a, dba_objects b, gv$session c,gv$sqlarea d
where a.OBJECT_Id = b.object_id
and a.SESSION_ID = c.SId
and c.sql_id=d.sql_id ;

-- 4. 查询当前数据库连接数（1节点1690；2节点230，如果1节点超过2000，需要预警）
select inst_id,count(*)  from gv$session
group by inst_id;