当我们创建了一个表空间并指定了数据文件，在操作系统上就是出现这个文件。但是如果我们将这个文件开启了自动扩展，只要有新的数据存入，这个文件就会越来越大，直到达到文件或者操作系统的限制。
但是有时候明明表是空的，但是却还是占据了很大的空间。这时候就需要我们降低表的高水位线，使得表所使用的段变小，释放多余空间。然后通过resize改变数据文件大小。已达到释放操作系统空间的作用。
实验一：
降低高水位线(truncate方式)缩小数据文件大小

--创建一个表空间并指定对应的数据文件
VAST@prod > create tablespace vdedutbs datafile '/u01/app/oracle/oradata/prod/vdedutbs01.dbf' size 10M autoextend on;
Tablespace created.
--在这个表空间上创建一张大表
VAST@prod > create table ote tablespace vdedutbs as select * from dba_objects;
Table created.
--再次插入数据，将表变的更大一些
VAST@prod > insert into ote select * from ote;
75354 rows created
VAST@prod > commit;
Commit complete.
--查看此时数据文件大小
VAST@prod > set line 200
VAST@prod > col file_name for a50
VAST@prod > select file_id,
2 file_name,
3 tablespace_name,
4 autoextensible,
5 bytes / 1048576 total,
6 user_bytes / 1048576 used,
7 online_status
8 from dba_data_files
9 where tablespace_name = 'VDEDUTBS';
FILE_ID FILE_NAME TABLESPACE_NAME AUT TOTAL USED ONLINE_
---------- -------------------------------------------------- ------------------------------ --- ---------- ---------- -------
7 /u01/app/oracle/oradata/prod/vdedutbs01.dbf VDEDUTBS YES 19 18 ONLINE
--查看表空间上的各个段
VAST@prod > col segment_name for a10
VAST@prod > select owner, segment_name, segment_type, tablespace_name, bytes / 1048576
2 from dba_segments
3 where tablespace_name = 'VDEDUTBS';
OWNER SEGMENT_NA SEGMENT_TYPE TABLESPACE_NAME BYTES/1048576
------------------------------ ---------- ------------------ ------------------------------ -------------
VAST OTE TABLE VDEDUTBS 18
--降低高水位线
VAST@prod > truncate table ote;
Table truncated.
--查看表空间上的各个段
VAST@prod > select owner, segment_name, segment_type, tablespace_name, bytes / 1048576
2 from dba_segments
3 where tablespace_name = 'VDEDUTBS';
OWNER SEGMENT_NA SEGMENT_TYPE TABLESPACE_NAME BYTES/1048576
------------------------------ ---------- ------------------ ------------------------------ -------------
VAST OTE TABLE VDEDUTBS .0625
--resize数据文件
VAST@prod > alter database datafile '/u01/app/oracle/oradata/prod/vdedutbs01.dbf' resize 10M;
Database altered.
--查看此时数据文件大小
VAST@prod > select file_id,
2 file_name,
3 tablespace_name,
4 autoextensible,
5 bytes / 1048576 total,
6 user_bytes / 1048576 used,
7 online_status
8 from dba_data_files
9 where tablespace_name = 'VDEDUTBS';
FILE_ID FILE_NAME TABLESPACE_NAME AUT TOTAL USED ONLINE_
---------- -------------------------------------------------- ------------------------------ --- ---------- ---------- -------
7 /u01/app/oracle/oradata/prod/vdedutbs01.dbf VDEDUTBS YES 10 9 ONLINE

前几天出现这么一个情况，想讲一个空的数据文件的大小减小一部分，查询表空间的使用率以及表空间下是否有表的存在，发现都没有。但是却一直报ORA-03297错误
实验二：

--表空间使用率
set line 200
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 order by used_pct desc;
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------------------------------ ---------- ---------- ----------
SYSTEM 720 717.69 99.68%
SYSAUX 620 580.13 93.57%
EXAMPLE 345.63 310.13 89.73%
VASTTBS 421.81 227.56 53.95%
UNDOTBS1 105 50.44 48.04%
USERS 196.25 4.5 2.29%
VDEDUTBS 10 1 10%
--修改文件大小为5M
VAST@prod > alter database datafile '/u01/app/oracle/oradata/prod/vdedutbs01.dbf' resize 5M;
alter database datafile '/u01/app/oracle/oradata/prod/vdedutbs01.dbf' resize 5M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
--查看VEDUDTBS下有哪些表
VAST@prod > select * from dba_tables where tablespace_name='VDEDUTBS';
no rows selected
明明修改的大小小于使用大小，且表空间下无表的存在为什么不能缩小数据文件呢
--查看表空间上的各个段
VAST@prod > col segment_name for a30
VAST@prod > /
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1048576
------------------------------ ------------------------------ ------------------ ------------------------------ -------------
VAST BIN$RpdF3cZzdTTgU2VQqMBzGg==$0 TABLE VDEDUTBS 9
这时查看段发现，在这个表空间内存在着一个被删除的表
--查看回收站
VAST@prod > show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
OTE BIN$RpdF3cZzdTTgU2VQqMBzGg==$0 TABLE 2017-01-21:16:13:00
此表已经不再使用，将他从回收站中清除干净
VAST@prod > purge table ote;
Table purged.
--修改文件大小为5M
VAST@prod > alter database datafile '/u01/app/oracle/oradata/prod/vdedutbs01.dbf' resize 5M;
Database altered.

修改成功
原来是由于之前将一个表删除，但是没有真的被删除，而是被放在的回收站中。

ITPub博客

RESIZE DATAFILE与ORA-03297

最新文章