一.原表大小

SQL> SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments where segment_name ='HSC_CDR1710';

TABLENAME                                    B         KB         MB
----------------------------------- ---------- ---------- ----------
HSC_CDR1710                   276824064     270336        264

二.TRUNCATE 后实际占用空间未变

SQL> truncate table HSC_CDR1710;

Table truncated.

SQL> SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments where segment_name ='HSC_CDR1710';

TABLENAME                                    B         KB         MB
----------------------------------- ---------- ---------- ----------
HSC_CDR1710                   276824064     270336        264

三.用DROP storage也未变化

SQL> truncate table HSC_CDR1710 DROP storage;

Table truncated.

SQL> SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments where segment_name ='HSC_CDR1710';

TABLENAME                                    B         KB         MB
----------------------------------- ---------- ---------- ----------
HSC_CDR1710                   276824064     270336        264

四.下面这个办法空间减少了.

SQL> alter table HSC_CDR1710  deallocate   UNUSED KEEP 0;

Table altered.



SQL> SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments where segment_name ='HSC_CDR1710';

TABLENAME                                    B         KB         MB
----------------------------------- ---------- ---------- ----------
HSC_CDR1710                      327680        320      .3125


五.另外,在未用普通TRUNCATE条件下,直接对原表DROP storage方式TRUNCATE也是不变的
SQL> SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments where segment_name ='HSC_CDR1711';

TABLENAME                               B         KB         MB
------------------------------ ---------- ---------- ----------
HSC_CDR1711              343932928     335872        328

SQL> truncate table HSC_CDR1711 DROP storage;

Table truncated.

SQL> SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments where segment_name ='HSC_CDR1711';

TABLENAME                               B         KB         MB
------------------------------ ---------- ---------- ----------
HSC_CDR1711              343932928     335872        328

SQL> alter table TBL_HISTORYCDR1711  deallocate   UNUSED KEEP 2;

Table altered.

用KEEP 0 也不会能会使11 G 不能导出空表,不用吧加个数如1?(看第七条)

SQL> SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments where segment_name ='TBL_HISTORYCDR1711';

TABLENAME                               B         KB         MB
------------------------------ ---------- ---------- ----------
TBL_HISTORYCDR1711                 327680        320      .3125


六.最后,建个PROCEDURE,用这个PROCEDURE 来检查表空间情况
建PROCEDURE(建立前可执行一下,EXEC SHOW_SPACE('表名'),一般是没有这个PROCEDURE,
也可取一个另系统不可能用的名称如 SHOW_SAPCE_H等
如create or replace procedure show_space_h

create or replace procedure show_space
( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
as
    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_last_used_block           number;
    procedure p( p_label in varchar2, p_num in number )
    is
    begin
        dbms_output.put_line( rpad(p_label,40,'.') ||
                              p_num );
    end;
begin

    dbms_space.unused_space
    ( segment_owner     => p_owner,
      segment_name      => p_segname,
      segment_type      => p_type,
      partition_name    => p_partition,
      total_blocks      => l_total_blocks,
      total_bytes       => l_total_bytes,
      unused_blocks     => l_unused_blocks,
      unused_bytes      => l_unused_bytes,
      last_used_extent_file_id => l_LastUsedExtFileId,
      last_used_extent_block_id => l_LastUsedExtBlockId,
      last_used_block => l_last_used_block );

    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    p( 'Last Used Block', l_last_used_block );
end;
/
Procedure created.


设置输出报告
SQL> SET SERVEROUTPUT ON

检查表空间
SQL> exec show_space('TBL_BADCDR1709');
Total Blocks............................18432
Total Bytes.............................150994944
Unused Blocks...........................512
Unused Bytes............................4194304
Last Used Ext FileId....................6
Last Used Ext BlockId...................47872
Last Used Block.........................512

PL/SQL procedure successfully completed.


不用时,可DROP 掉这个PROCEDURE
SQL> DROP PROCEDURE SHOW_SPACE;

Procedure dropped.


七.alter table TBL_BADCDR1709 deallocate   UNUSED KEEP 0;
后的空间情况:
SQL> EXEC SHOW_SPACE('TBL_BADCDR1709');
Total Blocks............................40
Total Bytes.............................327680
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................9
Last Used Ext BlockId...................58752
Last Used Block.........................34

而再加49152后结果一样,加49153报错
SQL> alter table TBL_BADCDR1709 deallocate   UNUSED KEEP 49152;

Table altered.

SQL> alter table TBL_BADCDR1709 deallocate   UNUSED KEEP 49153;
alter table TBL_BADCDR1709 deallocate   UNUSED KEEP 49153
*
ERROR at line 1:
ORA-03230: segment only contains 6 blocks of unused space above high water mark