PHP code:



SQL>  select segment_name,bytes/1024/1024 from dba_segments where segment_name='TEST_SHRINK';    





SEGMENT_NAME         BYTES/1024/1024


-------------------- ---------------


TEST_SHRINK                  300.375





SQL> delete test_shrink where rownum<100000;





99999 rows deleted.





SQL> commit;





Commit complete.





SQL>  delete test_shrink where rownum<100000;





99999 rows deleted.





SQL> commit;





Commit complete.








SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='TEST_SHRINK';    





SEGMENT_NAME         BYTES/1024/1024


-------------------- ---------------


TEST_SHRINK                  300.375





SQL> select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size');





       SID STATISTIC#      VALUE


---------- ---------- ----------


       136        134  259857896





SQL> alter table test_shrink enable row movement;





Table altered.





SQL> alter table test_shrink shrink space;





Table altered.





SQL> select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size');





       SID STATISTIC#      VALUE


---------- ---------- ----------


       136        134  541072808





SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='TEST_SHRINK';    





SEGMENT_NAME         BYTES/1024/1024


-------------------- ---------------


TEST_SHRINK                  175.875











SQL>select * from v$locked_object





    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   PROCESS


---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------


LOCKED_MODE


-----------


         9          4        145      10388        136 YXYUP                          oracle                         11064


          3


          


          


          





logminer日志





QL> select operation,sql_redo from v$logmnr_contents;





OPERATION


--------------------------------


SQL_REDO


--------------------------------------------------------------------------------


DELETE


delete from &quot;YXYUP&quot;.&quot;TEST_SHRINK&quot; where &quot;OWNER&quot; = 'SYS' and &quot;OBJECT_NAME&quot; = 'USE


R_QUEUE_PUBLISHERS' and &quot;SUBOBJECT_NAME&quot; IS NULL and &quot;OBJECT_ID&quot; = '4623' and &quot;D


ATA_OBJECT_ID&quot; IS NULL and &quot;OBJECT_TYPE&quot; = 'VIEW' and &quot;CREATED&quot; = TO_DATE('20-JA


N-07', 'DD-MON-RR') and &quot;LAST_DDL_TIME&quot; = TO_DATE('20-JAN-07', 'DD-MON-RR') and


&quot;TIMESTAMP&quot; = '2007-01-20:04:25:26' and &quot;STATUS&quot; = 'VALID' and &quot;TEMPORARY&quot; = 'N'


 and &quot;GENERATED&quot; = 'N' and &quot;SECONDARY&quot; = 'N' and ROWID = 'AAACiUAAEAAANLOAAq';





INSERT





OPERATION


--------------------------------


SQL_REDO


--------------------------------------------------------------------------------


insert into &quot;YXYUP&quot;.&quot;TEST_SHRINK&quot;(&quot;OWNER&quot;,&quot;OBJECT_NAME&quot;,&quot;SUBOBJECT_NAME&quot;,&quot;OBJECT


_ID&quot;,&quot;DATA_OBJECT_ID&quot;,&quot;OBJECT_TYPE&quot;,&quot;CREATED&quot;,&quot;LAST_DDL_TIME&quot;,&quot;TIMESTAMP&quot;,&quot;STATU


S&quot;,&quot;TEMPORARY&quot;,&quot;GENERATED&quot;,&quot;SECONDARY&quot<img src="images/smilies/23.gif" smilieid="206" border="0" alt="" /> values ('SYS','USER_QUEUE_PUBLISHERS',NU


LL,'4623',NULL,'VIEW',TO_DATE('20-JAN-07', 'DD-MON-RR'),TO_DATE('20-JAN-07', 'DD


-MON-RR'),'2007-01-20:04:25:26','VALID','N','N','N');





DELETE


delete from &quot;YXYUP&quot;.&quot;TEST_SHRINK&quot; where &quot;OWNER&quot; = 'PUBLIC' and &quot;OBJECT_NAME&quot; = '


USER_QUEUE_PUBLISHERS' and &quot;SUBOBJECT_NAME&quot; IS NULL and &quot;OBJECT_ID&quot; = '4624' and





OPERATION


--------------------------------


SQL_REDO


--------------------------------------------------------------------------------


 &quot;DATA_OBJECT_ID&quot; IS NULL and &quot;OBJECT_TYPE&quot; = 'SYNONYM' and &quot;CREATED&quot; = TO_DATE(


'20-JAN-07', 'DD-MON-RR') and &quot;LAST_DDL_TIME&quot; = TO_DATE('20-JAN-07', 'DD-MON-RR'


) and &quot;TIMESTAMP&quot; = '2007-01-20:04:25:26' and &quot;STATUS&quot; = 'VALID' and &quot;TEMPORARY&quot;


 = 'N' and &quot;GENERATED&quot; = 'N' and &quot;SECONDARY&quot; = 'N' and ROWID = 'AAACiUAAEAAANLOA


Ar';





INSERT


insert into &quot;YXYUP&quot;.&quot;TEST_SHRINK&quot;(&quot;OWNER&quot;,&quot;OBJECT_NAME&quot;,&quot;SUBOBJECT_NAME&quot;,&quot;OBJECT


_ID&quot;,&quot;DATA_OBJECT_ID&quot;,&quot;OBJECT_TYPE&quot;,&quot;CREATED&quot;,&quot;LAST_DDL_TIME&quot;,&quot;TIMESTAMP&quot;,&quot;STATU





OPERATION


--------------------------------


SQL_REDO


--------------------------------------------------------------------------------


S&quot;,&quot;TEMPORARY&quot;,&quot;GENERATED&quot;,&quot;SECONDARY&quot<img src="images/smilies/23.gif" smilieid="206" border="0" alt="" /> values ('PUBLIC','USER_QUEUE_PUBLISHERS'


,NULL,'4624',NULL,'SYNONYM',TO_DATE('20-JAN-07', 'DD-MON-RR'),TO_DATE('20-JAN-07


', 'DD-MON-RR'),'2007-01-20:04:25:26','VALID','N','N','N');





......................











可以看出shrink space 的整个过程都是在做delete和insert 操作,


感觉这个操作在生产DB不是逼不得已的时候,还是不用为好.


_____________________________