-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