shrink 完成也

begin

 dbms_stats.gather_table_stats(ownname=>'U190717',tabname=>'T_HS_INIVBALANCEEXP_H',cascade=>true);

 end;

 /

空间还没有缩小

实在收缩不了 就expdp后再impdp试试

收缩的意思是由于增删改等操作导致存在数据碎片，收缩后可以回收这部分空间，如果你这个表没有什么增删改操作，那没啥可以收缩的啊

可以试试move表。shrink理论上是可以的：

SQL> create table t pctfree 0 as select d.* from dba_objects d,
  2  ( select 1 from dual connect by level <= 10 );

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2  blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE_MB",
  3  round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE_MB",
  4  round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB"
  5  from dba_tables
  6  where owner = user
  7  and table_name = 'T';

    BLOCKS   NUM_ROWS AVG_ROW_LEN TOTAL_SIZE_MB ACTUAL_SIZE_MB FRAGMENTED_SPACE_MB
---------- ---------- ----------- ------------- -------------- -------------------
     13769     780490         132        107.57          98.25                9.32

1 row selected.

SQL>
SQL> delete from t where mod(object_id,2) = 0 ;

389690 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> select
  2  blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE_MB",
  3  round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE_MB",
  4  round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB"
  5  from dba_tables
  6  where owner = user
  7  and table_name = 'T';

    BLOCKS   NUM_ROWS AVG_ROW_LEN TOTAL_SIZE_MB ACTUAL_SIZE_MB FRAGMENTED_SPACE_MB
---------- ---------- ----------- ------------- -------------- -------------------
     13769     780490         132        107.57          98.25                9.32

1 row selected.

SQL>
SQL> alter table t enable row movement;

Table altered.

SQL> alter table t shrink space;

Table altered.

SQL>
SQL> select
  2  blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE_MB",
  3  round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE_MB",
  4  round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB"
  5  from dba_tables
  6  where owner = user
  7  and table_name = 'T';

    BLOCKS   NUM_ROWS AVG_ROW_LEN TOTAL_SIZE_MB ACTUAL_SIZE_MB FRAGMENTED_SPACE_MB
---------- ---------- ----------- ------------- -------------- -------------------
     13769     780490         132        107.57          98.25                9.32

1 row selected.

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2  blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE_MB",
  3  round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE_MB",
  4  round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB"
  5  from dba_tables
  6  where owner = user
  7  and table_name = 'T';

    BLOCKS   NUM_ROWS AVG_ROW_LEN TOTAL_SIZE_MB ACTUAL_SIZE_MB FRAGMENTED_SPACE_MB
---------- ---------- ----------- ------------- -------------- -------------------
      6813     390800         132         53.23           49.2                4.03

1 row selected.