Oracle表空间管理

查看表空间剩余空间

SELECT SUM(bytes) / (1024 * 1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
where TABLESPACE_NAME = 'tablespace name' 
GROUP BY tablespace_name;

SELECT a.tablespace_name as tablespace_name,to_char(b.total/1024/1024/1024,999999.99) as Total_GB,
to_char((b.total-a.free)/1024/1024/1024,999999.99) as Used_GB,
to_char(a.free/1024/1024/1024,999999.99) as Free_GB,
to_char(round((total-free)/total,4)*100,999.99) as Used_Rate
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
--AND a.tablespace_name='UNDOTBS3'
ORDER BY a.tablespace_name;

查看表占用的空间

select SEGMENT_NAME,SUM(bytes/1024/1024/1024) from dba_segments
where owner='schema'
AND TABLESPACE_NAME = 'tablespace name'
GROUP BY SEGMENT_NAME
ORDER BY SUM(bytes/1024/1024/1024) DESC;

回收空间

alter table TABLE_NAME enable row movement;
alter table TABLE_NAME shrink space compact;    --只整理碎片 不回收空间，  
alter table TABLE_NAME shrink space;            --整理碎片并回收空间。
alter table TABLE_NAME shrink space cascade;    --整理碎片回收空间 并连同表的级联对象一起整理(比如索引)
alter table TABLE_NAME disable row movement;