select t.segment_name,t.BYTES/1024/1024/1024 GB,t.segment_type
from user_segments t
where t.segment_type in ('TABLE','TABLE PARTITION')
order by nvl(t.BYTES/1024/1024/1024,0) desc;
select 'truncate table '|| t.TABLE_NAME ||';'
from user_tables t
where t.TABLE_NAME like 'LOG%';
2.2 收缩表空间
select a.tablespace_name,
a.file_name,
a.totalsize as totalsize_MB,
b.freesize as freesize_MB,
'ALTER DATABASE DATAFILE ''' || a.file_name || ''' RESIZE ' ||
round((a.totalsize - b.freesize) + 200) || 'M;' as "alter datafile"
from (select a.file_name,
a.file_id,
a.tablespace_name,
a.bytes / 1024 / 1024 as totalsize
from dba_data_files a) a,
(select b.tablespace_name,
b.file_id,
sum(b.bytes / 1024 / 1024) as freesize
from dba_free_space b
group by b.tablespace_name, b.file_id) b
where a.file_id = b.file_id
and b.freesize > 100
and a.tablespace_name in ('TBS_LOG_DATA')
order by a.tablespace_name
将上一步的 alter datafile语句拷贝出来执行:
有部分报错:
ORA-03297: file contains used data beyond requested RESIZE value
select 'alter table '||t.TABLE_NAME||' enable row movement;',
'alter table '||t.TABLE_NAME||' shrink space cascade;'
from user_tables t
where t.TABLE_NAME like 'LOG%';
清理完碎片之后，重新执行，依旧报错 。
2.4 直接把相关的表drop掉
select 'drop table '|| t.TABLE_NAME ||'purge;'
from user_tables t
where t.TABLE_NAME like 'LOG%';
drop掉表之后，重新执行，依旧报错 。
2.5 把该表空间下其它的表移出此表空间
万能的itpub上有个博客:
Truncate table 或者 drop table 收缩数据文件,经常遇到ORA-03297: file contains used data beyond requested RESIZE value 查询dba_free_space 也有空闲空间 。经过查询MOS(Doc ID 1029252.6)得知
If you have a large extent in the middle of a datafile, and some object taking up room at the end of the datafile, you can use the query FINDEXT.SQL below to find this object. If you export this object, then drop it, you should then free up contiguous space at the end of your datafile so you will be able to resize it smaller.
Make sure you leave enough room in the datafile for importing the object back into the tablespace.
意思是说如果空闲的extent如果在文件的中间,此时无法进行resize ,必须把尾部的object drop 然后重建 再resize datafile 。以下是本人做的测试;
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M;
alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
无法进行resize
下面把tab1 drop 再测试
SQL> drop table tab1 purge;
Table dropped.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M;
alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
SQL> drop table tab2 purge
? 2 ?;
Table dropped.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 6M;
Database altered.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 1M;
Database altered.
可以收缩tab1的空间
note:
收缩数据文件和两个因素有关
1 降低高水位
2 free extent在datafile 的尾部
本篇文章直接解释了第二个
如果空闲的extent如果在文件的中间,此时无法进行resize ,必须把尾部的object drop 然后重建 再resize datafile 。
也就是说同时期该用户下其它表的写入，也在这个数据文件下，那么就不能进行resize 。
把其它表移动到users表空间:
select 'alter index '||index_NAME||' rebuild tablespace users;' from user_indexes where TABLE_NAME not like 'LOG_%';
select 'alter table '||TABLE_NAME||' move tablespace users;' from user_tables where TABLE_NAME not like 'LOG_%';
