shrink space 的整个过程就是在做delete和insert

[php]

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 "YXYUP"."TEST_SHRINK" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'USE
R_QUEUE_PUBLISHERS' and "SUBOBJECT_NAME" IS NULL and "OBJECT_ID" = '4623' and "D
ATA_OBJECT_ID" IS NULL and "OBJECT_TYPE" = 'VIEW' and "CREATED" = TO_DATE('20-JA
N-07', 'DD-MON-RR') and "LAST_DDL_TIME" = TO_DATE('20-JAN-07', 'DD-MON-RR') and
"TIMESTAMP" = '2007-01-20:04:25:26' and "STATUS" = 'VALID' and "TEMPORARY" = 'N'
and "GENERATED" = 'N' and "SECONDARY" = 'N' and ROWID = 'AAACiUAAEAAANLOAAq';

INSERT

OPERATION
--------------------------------
SQL_REDO
--------------------------------------------------------------------------------
insert into "YXYUP"."TEST_SHRINK"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT
_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATU
S","TEMPORARY","GENERATED","SECONDARY") 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 "YXYUP"."TEST_SHRINK" where "OWNER" = 'PUBLIC' and "OBJECT_NAME" = '
USER_QUEUE_PUBLISHERS' and "SUBOBJECT_NAME" IS NULL and "OBJECT_ID" = '4624' and

OPERATION
--------------------------------
SQL_REDO
--------------------------------------------------------------------------------
"DATA_OBJECT_ID" IS NULL and "OBJECT_TYPE" = 'SYNONYM' and "CREATED" = TO_DATE(
'20-JAN-07', 'DD-MON-RR') and "LAST_DDL_TIME" = TO_DATE('20-JAN-07', 'DD-MON-RR'
) and "TIMESTAMP" = '2007-01-20:04:25:26' and "STATUS" = 'VALID' and "TEMPORARY"
= 'N' and "GENERATED" = 'N' and "SECONDARY" = 'N' and ROWID = 'AAACiUAAEAAANLOA
Ar';

INSERT
insert into "YXYUP"."TEST_SHRINK"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT
_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATU

OPERATION
--------------------------------
SQL_REDO
--------------------------------------------------------------------------------
S","TEMPORARY","GENERATED","SECONDARY") 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不是逼不得已的时候,还是不用为好.
_____________________________________________
[/php]

我在做删除10000000行后的shrink,已经快三个小时了，还没有结束，真是汗！以前只是在小表上做。日志已经产生1.5g。

他好像是一行一行的在处理，加的锁是3，行独占。

[QUOTE][i]最初由 lcmlsj 发布[/i]
[B]他好像是一行一行的在处理，加的锁是3，行独占。 [/B][/QUOTE]


row-x(sx)

这个一直是这样的吧 后面的行往前移 然后降低高水位

呵呵，不错的试验，支持

alter table test_shrink enable row movement命令本身就是为了做dml来move row啊
跟flashback table有点像，其实就是在做dml操作，即使数据都一样rowid也不一样了
做一下trace应该也可以看出来吧

对于24小时的系统，Shrink 起来不是会有问题？

[QUOTE][i]最初由 bluemoon0083 发布[/i]
[B]alter table test_shrink enable row movement命令本身就是为了做dml来move row啊
跟flashback table有点像，其实就是在做dml操作，即使数据都一样rowid也不一样了
做一下trace应该也可以看出来吧 [/B][/QUOTE]

顶:) , 执行flashback table的时候,要求enable row movement也想到这个

这样看来，若表上有索引，那更麻烦！

redo size会爆掉的，基本上在大表上没用

挺好的一个实验:rose:

行驶一下斑竹权利
鼓励一下原创

其实oracle本身就不建议在生产系统业务忙的时候做shrink，所以有一个中断的参数，可以在任务忙的时候先中止，在业务不忙的时候再做。
其实如果不做shrink那如何有效的处理碎片的问题，还是和以前一样，imp或exp或是CATS等等？要是磁盘空间不足的情况下，这是做不了的，而且会以停止业务为带价。所以，shrink还是很大的一个进步。

受教了

受教了!

如果是分区表shink 表现

我测试了一下 shrink 一个表的某个分区，测试结果是被shrink的分区数据是不能被访问的，所以在线的大表想用 shrink很难。

heh,不错

[[i] 本帖最后由 brotherxiao 于 2008-1-28 13:20 编辑 [/i]]

only works for small table and non-critical time....

如果的确是数据少而占用大量的空间，联机重定义啦，基本上用不到那么多的开销的，效果相同