//首先查出来表空间使用率
SELECT a.tablespace_name "SYSTEM",total 表空间大小,free 表空间剩余大小,
(total-free) 表空间使用大小,
ROUND((total-free)/total,4)*100 "使用率 %"
FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name;
//上面查的不准
select total.tablespace_name,round(total.MB,2) as Total_MB,round(total.MB-free.MB, 2)
as Used_MB,round((1-free.MB/total.MB)*100, 2) as Used_Pct from (select tablespace_name,
sum(bytes)/1024/1024 as MB from dba_free_space group by tablespace_name) free,(select tablespace_name,
sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name=total.tablespace_name;
//然后查询表空间使用路径
select a.file#,
a.name,
a.bytes / 1024 / 1024 CurrentMB,
ceil(HWM * a.block_size / 1024 / 1024) Resizeto,
(a.bytes - HWM * a.block_size) /1024 / 1024 releaseMB,
‘alter database datafile ‘‘‘ || a.name || ‘‘‘ resize ‘ ||
ceil(HWM * a.block_size / 1024 / 1024) || ‘M;‘ ResizeCmd
from v$datafile a,
(select file_id, max(block_id + blocks - 1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM * a.block_size) > 0
order by 5
//扩容
alter tablespace 表名
add datafile ‘e:\oradb\data2_data3.dbf‘ //扩容表空间路径相同名字不同
size 10240m //扩容大小
autoextend on
next 500m maxsize 20480m;
//然后执行
1.用以下这句查找空表并生成执行命令
select ‘alter table ‘||table_name||‘ allocate extent;‘ from user_tables where num_rows=0;
原文:https://www.cnblogs.com/YingJian/p/11451014.html