当表空间满了的时候,dba_free_space视图就不会存在表空间相关信息,导致关联的时候这种表空间的信息丢失
给个我遇到的代码,你可以参考:
原代码:
select
iv_date ,
df.tablespace_name ,
round(dd.bytes / 1024 / 1024, 2) ,
round((dd.bytes - df.bytes) / 1024 / 1204, 2) ,
round((df.bytes) / 1024 / 1024, 2) ,
round((dd.bytes - df.bytes) / dd.bytes, 4)*100 ,
round((df.bytes) / dd.bytes, 4)*100 ,
1
from (select f.tablespace_name tablespace_name,
sum(nvl(f.bytes, 0)) bytes
from dba_free_space f
where substr(f.tablespace_name,1,8)='TBS_LBI_'
group by f.tablespace_name) df,
(select d.tablespace_name tablespace_name,
sum(nvl(d.bytes,0)) bytes
from dba_data_files d
where substr(d.tablespace_name,1,8)='TBS_LBI_'
group by d.tablespace_name) dd
where df.tablespace_name = dd.tablespace_name;
------------------------------------------------------------------
修正后:
select
iv_date ,
dd.tablespace_name ,
round(dd.bytes / 1024 / 1024, 2) ,
round((dd.bytes - nvl(df.bytes,0)) / 1024 / 1204, 2) ,
round(nvl(df.bytes,0) / 1024 / 1024, 2) ,
round((dd.bytes - nvl(df.bytes,0)) / dd.bytes, 4)*100 ,
round(nvl(df.bytes,0) / dd.bytes, 4)*100 ,
1
from (select f.tablespace_name tablespace_name,
sum(nvl(f.bytes, 0)) bytes
from dba_free_space f
where substr(f.tablespace_name,1,8)='TBS_LBI_'
group by f.tablespace_name) df,
(select d.tablespace_name tablespace_name,
sum(nvl(d.bytes,0)) bytes
from dba_data_files d
where substr(d.tablespace_name,1,8)='TBS_LBI_'
group by d.tablespace_name) dd
where df.tablespace_name(+)= dd.tablespace_name;