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博客

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

最新文章