---------------------------------
收缩表空间
---------------------------------
1.查看表的使用状况
select sum(bytes)/1024/1024/1024 Gb from user_segments where segment_name='WIP_WO';
2.开启行迁移功能
alter table wip_wo enable row movement;
3.整理表空间内的碎片
alter table wip_wo shrink space compact;
4.收缩表空间
alter table wip_wo shrink space;
5.查看收缩后的表空间
SQL> select sum(bytes)/1024/1024 M from user_segments where segment_name='WIP_WO';
6.收缩index的空间
SQL> alter index WIP_WO_INX1 shrink space;
Index altered.
Elapsed: 00:00:00.52
SQL> alter index WIP_WO_INX2 shrink space;
Index altered.
Elapsed: 00:00:00.82
SQL> alter index WIP_WO_INX3 shrink space;
Index altered.
Elapsed: 00:00:02.57
SQL> alter index WIP_WO_PK1 shrink space;
Index altered.
Elapsed: 00:00:00.60
SQL> alter index WIP_WO_UN1 shrink space;
注:
如果table很大,在收缩表空间时会产生大量的archivelog[@more@]
收缩表空间
---------------------------------
1.查看表的使用状况
select sum(bytes)/1024/1024/1024 Gb from user_segments where segment_name='WIP_WO';
2.开启行迁移功能
alter table wip_wo enable row movement;
3.整理表空间内的碎片
alter table wip_wo shrink space compact;
4.收缩表空间
alter table wip_wo shrink space;
5.查看收缩后的表空间
SQL> select sum(bytes)/1024/1024 M from user_segments where segment_name='WIP_WO';
6.收缩index的空间
SQL> alter index WIP_WO_INX1 shrink space;
Index altered.
Elapsed: 00:00:00.52
SQL> alter index WIP_WO_INX2 shrink space;
Index altered.
Elapsed: 00:00:00.82
SQL> alter index WIP_WO_INX3 shrink space;
Index altered.
Elapsed: 00:00:02.57
SQL> alter index WIP_WO_PK1 shrink space;
Index altered.
Elapsed: 00:00:00.60
SQL> alter index WIP_WO_UN1 shrink space;
注:
如果table很大,在收缩表空间时会产生大量的archivelog[@more@]
参考:http://hi.baidu.com/bailang3106/blog/item/6f2785eefdd2a4212cf5342d.html
http://black4371.blog.163.com/blog/static/3202390220104141941895/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/789833/viewspace-1037362/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/789833/viewspace-1037362/