home/oradata/powerdes/orctstu01.dbf
-rw-r-----. 1 oracle oinstall 32G May 1618:06 /home/oradata/powerdes/orctstu01.dbf
[oracle@edustu4 ~]$
也是32G左右，和实际的磁盘的数据文件的大小一致，
看看这个最大4193920的数据块所在的extent对应的segment信息是否是已经被drop到的table？
select segment_name,owner from dba_extentswhere block_id=3507584;
SQL> select segment_name,owner fromdba_extents where block_id=1942656;
SEGMENT_NAME
--------------------------------------------------------------------------------
OWNER
------------------------------
RES_APPROVE_SHARE
ORCTSTU
SQL>
6，分析问题所在
这个表不是一件被drop的哪些表记录，表RES_APPROVE_SHARE正在被应用程序使用着，也就说明了报错，所以resize的时候报错ORA-03297: file contains used data beyond requested RESIZE value，block不能释放掉，因为正在被使用。
这个时候通过正常的resize已经不能解决问题了，怎么办？可以采用expdp、impdp的方式重新生成新的表空间和数据文件，然后删除旧的表空间和数据文件。
7，开始新建表空间
create tablespace ORCTSTU_2
logging
datafile '/home/oradata/powerdes/orctstu02.dbf'
size 50m
autoextend on
next 50m ;
alter table RES_APPROVE_SHARE move ORCTSTU_2;
ORA-14133: ALTER TABLE MOVE cannot becombined with other operations
alter table orctstu.RES_APPROVE_SHARE movetablespace ORCTSTU_2;
8，使用expdp导出数据
先建立管道目录
CREATEOR REPLACE DIRECTORY dir_dump_t1 AS'/home/oracle/expdpimpdp';
开始导出export
[oracle@edustu4~]$ expdp orctstu/testpd2015@PD1directory=dir_dump_t1 schemas=orctstu dumpfile=TEST2_PD_20150518.dmp
Export:Release 11.2.0.1.0 - Production on Mon May 18 17:06:42 2015
Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction
Withthe Partitioning, OLAP, Data Mining and Real Application Testing options
Starting"ORCTSTU"."SYS_EXPORT_SCHEMA_02": orctstu/********@PD1 directory=dir_dump_t1schemas=orctstu dumpfile=TEST2_PD_20150518.dmp
Estimatein progress using BLOCKS method...
Processingobject type SCHEMA_EXPORT/TABLE/TABLE_DATA
Totalestimation using BLOCKS method: 7.483 GB
Processingobject type SCHEMA_EXPORT/USER
Processingobject type SCHEMA_EXPORT/SYSTEM_GRANT
.....................................................
Processingobject type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
.. exported "ORCTSTU"."RES_APPROVE_CONTENT" 44.01 MB 350923 rows
.. exported "ORCTSTU"."RECEIPT_BILL" 569.3 MB 2064823 rows
.....................................................
.. exported "ORCTSTU"."ZS_PLAN_LEAVE_MESSAGE" 0 KB 0 rows
.. exported "ORCTSTU"."ZS_PLAN_MESSAGE" 0 KB 0 rows
Mastertable "ORCTSTU"."SYS_EXPORT_SCHEMA_02" successfullyloaded/unloaded
******************************************************************************
Dumpfile set for ORCTSTU.SYS_EXPORT_SCHEMA_02 is:
/home/oracle/expdpimpdp/TEST2_PD_20150518.dmp
Job"ORCTSTU"."SYS_EXPORT_SCHEMA_02" successfully completed at17:11:13
[oracle@edustu4~]$
9，开始使用import导入数据
9.1 清理旧数据
删除用户
drop user orctstu cascade;
删除表空间
drop tablespace orctstu including contents anddatafiles;
然后重启oracle
shutdown immediate
startup
查看磁盘空间，已经释放出来了
[oracle@edustu4 expdpimpdp]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 57G 21G 34G 38% /
tmpfs 12G 2.1G 10G 18% /dev/shm
/dev/sda1 194M 32M 153M 18% /boot
/dev/mapper/vg001-lv001
63G 12G 49G 20% /home/oradata
df:`/root/.gvfs': Permission denied
[oracle@edustu4expdpimp