Oracle 資料表空間查詢

標籤：io   使用   sp   c   on   cti   r   bs   oracle   

一、普通查詢

SELECT Total.name TablespaceName,
       Free_space,
       (total_space - Free_space) Used_space,
       total_space
  FROM (select tablespace_name, sum(bytes / 1024 / 1024) Free_Space
          from sys.dba_free_space
         group by tablespace_name) Free,
       (select b.name, sum(bytes / 1024 / 1024) TOTAL_SPACE
          from sys.v_$datafile a, sys.v_$tablespace B
         where a.ts# = b.ts#
         group by b.name) Total
 WHERE Free.Tablespace_name = Total.name;

 

二、根據Oracle 不活動串連使用者查詢使用空間

select t.owner 名稱,sum(t.BYTES)/1024/1024/1024 使用大小_G from dba_segments t
where t.TABLESPACE_NAME=‘USERS‘ and t.owner not in(
select DISTINCT z.USERNAME from (
select t.USERNAME,DECODE(t.STATUS,‘INACTIVE‘,‘等待操作‘,‘ACTIVE‘,‘執行狀態‘,‘KILLED‘,‘標註-KILL‘) 狀態,
t.SCHEMA#,t.SCHEMANAME,t.MACHINE 機器名 from v$Session t where t.SCHEMANAME<>‘SYS‘ and t.SCHEMA#<>0
) z )
group by t.owner order by t.owner,sum(t.BYTES)/1024/1024/1024

Oracle 資料表空間查詢