本文已参与 ⌈新人创作礼⌋ 活动，一起开启掘金创作之路\

Oracle数据库表空间使用情况查询语句

语句一、

select u.*,round(p.avg_use_per_day_mb,1) avg_used_per_day_mb,round((max_size_mb-size_used_mb)/p.avg_use_per_day_mb) tbs_exhaust_days,
case when  u.MAX_FREE_RATE<7 and round((max_size_mb-size_used_mb)/p.avg_use_per_day_mb)<30 then '*'
                        else null end care
from (select a.tablespace_name,b.size_used_mb,a.data_size_mb,
         round(100-b.size_used_mb/a.data_size_mb*100) free_rate,
         a.max_size_mb, a.max_size_mb - b.size_used_mb free_mb,
         round(100-b.size_used_mb/a.max_size_mb*100) max_free_rate
         from
                 (select tablespace_name,round(sum(bytes/1024/1024)) data_size_mb,round(sum(case when maxbytes>bytes then maxbytes else bytes end)/1024/1024) max_size_mb
         from dba_data_files group by tablespace_name) a,
                 (select tablespace_name,round(sum(bytes/1024/1024)) size_used_mb
         from dba_segments group by tablespace_name) b
         where a.tablespace_name=b.tablespace_name order by 6 desc,4 desc) u,
         (select name,avg(use_per_day_mb)+0.0001 avg_use_per_day_mb 
                  from (select x.name,x.rdate,(x.used_blocks-lag(x.used_blocks) over (partition by name order by rdate))*y.block_size/1024/1024 use_per_day_mb
                from 
                                   (select to_char(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'),'yyyy-mm-dd') rdate ,name,max(tablespace_usedsize) used_blocks from dba_hist_tbspc_space_usage a,v$tablespace b
                                where a.tablespace_id=b.ts#
                                group by to_char(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'),'yyyy-mm-dd'),name order by name  ) x,dba_tablespaces y
         where x.name=y.tablespace_name )
                 where use_per_day_mb is not null
        group by name) p
where u.TABLESPACE_NAME=p.name
and u.TABLESPACE_NAME not like '%UNDO%'
and u.TABLESPACE_NAME not like '%TEMP%'
order by 4,7;

查询结果如图：

语句二、

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1

查询结果为下图：

附：\

其他语句：\

select segment_name,tablespace_name,bytes b,bytes/1024 kb,bytes/1024/1024 mb,bytes/1024/1024/1024 gb from
user_segments where segment_type = 'TABLE' and tablespace_name = 'SDC_DATA'
select blocks,empty_blocks,num_rows from user_tables where table_name = 'TS_BI_ANA_USR_PORTRAIT_SIGLE'
alter table TS_BI_ANA_USR_PORTRAIT_SIGLE enable row movement;
alter table TS_BI_ANA_USR_PORTRAIT_SIGLE shrink space;
alter table TS_BI_ANA_USR_PORTRAIT_SIGLE disable row movement;
alter table TS_BI_ANA_USR_PORTRAIT_SIGLE nologging;
select table_name,logging from user_tables;
select count(*) from TS_BI_ANA_USR_PORTRAIT_SIGLE where to_timestamp(opt_time,'yyyy-mm-dd hh24:mi:ss,ff3') < to_timestamp('20190101 11:00:00,000','yyyy-mm-dd hh24:mi:ss,ff3')
declare
maxrows number default 100000;
delete_ct number default 0;
begin
select count(1)/maxrows  into delete_ct from TS_BI_ANA_USR_PORTRAIT_SIGLE where 
to_timestamp(opt_time,'yyyy-mm-dd hh24:mi:ss,ff3') < to_timestamp('20190102 00:00:00,000','yyyy-mm-dd hh24:mi:ss,ff3');
for i in 1..TRUNC(delete_ct)+1
loop
delete TS_BI_ANA_USR_PORTRAIT_SIGLE nologging where
to_timestamp(opt_time,'yyyy-mm-dd hh24:mi:ss,ff3') < to_timestamp('20190102 00:00:00,000','yyyy-mm-dd hh24:mi:ss,ff3') and rownum <= maxrows;
commit;
end loop ;
end;