oracle 删除用户及相关表数据，释放磁盘空间

来源于：http://www.itpub.net/thread-513609-1-1.html

http://bbs.csdn.net/topics/330251089

http://blog.csdn.net/rznice/article/details/6107650

1、首先要得到USERS01.DBF的file_id

SELECT file_name,file_id FROM dba_data_files WHERE file_name like '%USER%';

2、统计这个数据文件有哪些段，各占多少空间，然后再考虑是drop还是truncate等处理操作。

select owner,segment_name,sum(bytes) 
  from dba_extents
where file_id=如上的file_id
group by owner,segment_name
order by 3 desc;

3、找到哪个用的空间多了，处理一下吧。

不论使用delete还是truncate删除数据，ORACLE数据文件都不会自动收缩；
如果要收缩数据文件，delete不能降低HWM,delete后再alter table XXX move;
或者使用truncate后再resize数据文件试试
sql>truncate test1
sql>alter database datafile 'users01.dbf' resize 200m;

可以通过resize datafile来减小数据文件的大小。
首先，要查清楚数据文件的真实使用空间。可以通过查询dba_extents达到这个目的。oatbs表空间使用的数据文件为E:/Oracle/PRODUCT/10.2.0/ORADATA/ORCL/OATBS.DBF

SQL> select file_name,e.file_id, sum(e.bytes)/1024/1024 as MB from dba_extents e join dba_data_files f on e.file_id=f.file_id group by file_name,e.file_id;

FILE_NAME FILE_ID MB
-------------------------------------------------------------------------------- ---------- ----------
E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/USERS01.DBF 4 3.1875
E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/OATBS.DBF 8 73.375
E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSAUX01.DBF 3 253.8125
E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/EXAMPLE01.DBF 5 77.625
E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/UNDOTBS01.DBF 2 42.8125
E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSTEM01.DBF 1 500.625

6 rows selected

可以看出OATBS.DBF实际使用空间只有73MB。resize datafile的时候不可以小过这个大小。

SQL> alter database datafile 8 resize 80m;

Database altered

SQL>

OATBS.DBF 文件由原先的100M减少为现在的80M。

但我使用时会报错：ORA-03297:文件包含在请求的resize值以外使用的数据

解决方法很复杂，见另一篇文章Oracle调整表空间大小ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据问题_cm_0205的博客-CSDN博客