truncate table 会不会释放datafile的空间？如何才能释放?

下面我将做一这一实验来证明
oracle 10g r2

system@ORCL> create tablespace anbob datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' size 40m;
Tablespace created.
anbob@ORCL> create table testspace as select * from all_objects;
Table created.
anbob@ORCL> select segment_name,tablespace_name from user_segments where segment_name='TESTSPACE';
SEGMENT_NAME         TABLESPACE_NAME
-------------------- --------------------
TESTSPACE            TT
anbob@ORCL> select count(*) from testspace;
  COUNT(*)
----------
     40770
anbob@ORCL> insert into testspace select * from testspace;
40770 rows created.
anbob@ORCL> /
81540 rows created.
anbob@ORCL> /
163080 rows created.
anbob@ORCL> select count(*) from testspace;
  COUNT(*)
----------
    326160
anbob@ORCL>exec dbam_stats.gather_table_states(user,'TESTSPACE');

anbob@ORCL> alter table testspace move tablespace anbob;
Table altered.
anbob@ORCL> select tablespace_name,bytes/1024/1024 from user_free_space where tablespace_name='ANBOB';
TABLESPACE_NAME      BYTES/1024/1024
-------------------- ---------------
ANBOB                         2.9375
anbob@ORCL> select dbms_metadata.get_ddl('TABLE','TESTSPACE') from dual;
DBMS_METADATA.GET_DDL('TABLE','TESTSPACE')
--------------------------------------------------------------------------------
  CREATE TABLE "ANBOB"."TESTSPACE"
   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "ANBOB"
  
看ddl目的在看INITIAL 参数，因为truncate 是会按这个参数指定的大小新分配一个segment
anbob@ORCL> truncate table testspace;
Table truncated.
anbob@ORCL> select tablespace_name,bytes/1024/1024 from user_free_space where tablespace_name='ANBOB';
TABLESPACE_NAME      BYTES/1024/1024
-------------------- ---------------
ANBOB                         39.875
anbob@ORCL> conn system/oracle
Connected.
system@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 5m;
Database altered.

resize 成功，再做一个如果truncate 的表不在表空间的末端看是不是可以？

system@ORCL>conn anbob/anbob
anbob@ORCL> insert into testspace select * from all_objects;
insert into testspace select * from all_objects
*
ERROR at line 1:
ORA-01653: unable to extend table ANBOB.TESTSPACE by 128 in tablespace ANBOB
system@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' autoextend on;
Database altered.
system@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> insert  into testspace select * from all_objects;
40770 rows created.
anbob@ORCL> /
40770 rows created.
anbob@ORCL> insert  into testspace select * from testspace;
81540 rows created.
anbob@ORCL> /
163080 rows created.
anbob@ORCL> commit;
Commit complete.
anbob@ORCL> exec dbms_stats.GATHER_TABLE_STATS(USER,'TESTSPACE');
PL/SQL procedure successfully completed.
anbob@ORCL> select segment_name,bytes/1024/1024 from USER_segments where segment_name='TESTSPACE';
SEGMENT_NAME         BYTES/1024/1024
-------------------- ---------------
TESTSPACE                         37
anbob@ORCL> conn system/oracle
Connected.
system@ORCL>  select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ANBOB';
FILE_NAME                                                    TABLESPACE_NAME              MB
------------------------------------------------------------ -------------------- ----------
/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf              ANBOB                        38

anbob@ORCL> create table testspace2 tablespace anbob as  select * from testspace;
Table created.
anbob@ORCL> exec dbms_stats.GATHER_TABLE_STATS(USER,'TESTSPACE2');
PL/SQL procedure successfully completed.
anbob@ORCL> conn system/oracle
Connected.
system@ORCL>  select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ANBOB';
FILE_NAME                                                    TABLESPACE_NAME              MB
------------------------------------------------------------ -------------------- ----------
/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf              ANBOB                   74.0625
system@ORCL> select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name='ANBOB';
no rows selected
sys@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> truncate table TESTSPACE;
Table truncated.
anbob@ORCL> exec dbms_stats.GATHER_TABLE_STATS(USER,'TESTSPACE');
PL/SQL procedure successfully completed.
anbob@ORCL> conn / as sysdba
Connected.
sys@ORCL> select tablespace_name,bytes/1024/1024 from dba_free_sPAce where tablespace_name='ANBOB'; 
TABLESPACE_NAME      BYTES/1024/1024
-------------------- ---------------
ANBOB                        36.9375

sys@ORCL> select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ANBOB';
FILE_NAME                                                    TABLESPACE_NAME              MB
------------------------------------------------------------ -------------------- ----------
/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf              ANBOB                   74.0625
sys@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m;
alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
sys@ORCL> ALTER TABLESPACE anbob COALESCE;
Tablespace altered.
sys@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m;
alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
anbob@ORCL> drop table TESTSPACE purge;
Table dropped.
sys@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m;
alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
再造一个表出来，让他覆盖刚才truncate的空间，从datafile size 可以看出；
sys@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> create table testspace tablespace anbob as select * from testspace2 ;
Table created.
anbob@ORCL> conn / as sysdba
Connected.
sys@ORCL> select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ANBOB';
FILE_NAME                                                    TABLESPACE_NAME              MB
------------------------------------------------------------ -------------------- ----------
/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf              ANBOB                   74.0625
sys@ORCL> select tablespace_name,bytes/1024/1024 from dba_free_sPAce where tablespace_name='ANBOB'; 
no rows selected
解决方法 exp/imp expdp/impdp导出那些表
anbob@ORCL> drop  table testspace purge;
Table dropped.
anbob@ORCL> drop  table testspace2 purge;
Table dropped.
anbob@ORCL> conn system/oracle
Connected.
system@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m;
Database altered.
system@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 5m;
Database altered.

note:
1,truncate table，不会立即清除数据从存储中，只做标记为无效，后面的事务可以覆盖使用，这就是为什么有些老牛做的工具可以找回truncate的数据，原理就是他们把truncate的数据可以从数据文件中dump出来，再抽取，导入。所以你truncate了表发现错了千万不要再动数据库了找人恢复
2，truncate table 的数据如果在表空间的末端，那么datafile 是可以resize的，否则是不可以的
3，如果segment又没在文件的末端，存储比较紧张，那么解决方法也有转储出来，如exp，把表drop 掉，再imp,这样就可以resize了，因为表空间上已经没有可以阻挡的segment type(如果dump过block的话会发现那些类型的)
4,如果表空间没有free block，在dba_free_space视图是没有那条表空间的记录的