ITPub博客

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

原创 Linux操作系统 作者:yxyup 时间:2007-08-26 00:00:00 0 删除 编辑
shrink space 的整个过程就是在做delete和insert
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不是逼不得已的时候,还是不用为好

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7364032/viewspace-17474/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-06

  • 博文量
    164
  • 访问量
    1314238