oracle表空间释放

1）查询表空间信息

select
	a.tablespace_name as "表空间名",
	total as "表空间大小",
	free as "表空间剩余大小",
	(total - free) as "表空间使用大小",
	total/(1024*1024*1024) as "表空间大小(G)",
	free/(1024*1024*1024) as "表空间剩余大小(G)",
	(total - free)/(1024*1024*1024) as "表空间使用大小(G)",
	round((total-free)/total,4) * 100 as "使用率%"
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
order by
	"使用率%" desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2）查询指定表空间下各个表的表空间使用情况

select
    segment_name, 
	round(sum(bytes)/(1024*1024),2) as size_mb
from
    dba_segments
where
    tablespace_name  = 'USERS'
    and segment_type = 'TABLE'
group by
    segment_name
order by
    size_mb desc;
1
2
3
4
5
6
7
8
9
10
11
12

3-1）可以直接释放

truncate table tablename;
1

3-2) 可以move

执行

ALTER TABLE table_name MOVE
1

此语句重新组织表。这将创建一个新的表结构，并将所有行都移动到新的表空间中。通过重新组织表，可以释放已删除数据的空间，并释放与Undo段相关联的空间。

3-3）重新排列

执行

ALTER TABLE table_name ENABLE ROW MOVEMENT
1

语句，然后再执行

ALTER TABLE table_name SHRINK SPACE COMPACT
1

语句。这将导致表行在表中进行重新排列，删除的空间将被回收。