测试alter table shrink space compact cascade及学习user_tables相关列的含义
来源：互联网 发布：电视网络机顶盒怎么用 编辑：程序博客网 时间：2024/05/16 04:50
--测试alter tableshrink spacecompact cascade及学习user_tables相关列的含义
SQL> alter table test_shrink enable row movement;--alter table shrink space须开启行移动
Table altered.
SQL> select table_name,blocks,empty_blocks,num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),row_movement from user_tables where table_name='TEST_SHRINK';--查测试表相关信息
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS TO_CHAR(LAST_ANALYZ ROW_MOVE
------------------------------ ---------- ------------ ---------- ------------------- -------- --blocks是真正使用的块数 num_rows 表中行的记录数
TEST_SHRINK ENABLED
SQL> select segment_name,blocks from user_segments where segment_type='TABLE' and segment_name='TEST_SHRINK';--查询测试表segment
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
TEST_SHRINK 12288
SQL> exec dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'TEST_SHRINK',cascade=>true);--分析下测试表
PL/SQL procedure successfully completed.
SQL> select table_name,blocks,empty_blocks,num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),row_movement from user_tables where table_name='TEST_SHRINK';--再次查看测试表相关信息
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS TO_CHAR(LAST_ANALYZ ROW_MOVE
------------------------------ ---------- ------------ ---------- ------------------- --------
TEST_SHRINK 12040 0 862741 2010-08-28 14:17:40 ENABLED
SQL> select segment_name,blocks from user_segments where segment_type='TABLE' and segment_name='TEST_SHRINK';
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
TEST_SHRINK 12288
SQL> delete from test_shrink where rownum<=200000;--删除20w记录从测试表
200000 rows deleted.
SQL> commit;
Commit complete.
SQL> select segment_name,blocks from user_segments where segment_type='TABLE' and segment_name='TEST_SHRINK';--测试表segment,发现删除segment没有变化哟
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
TEST_SHRINK 12288
SQL> alter table test_shrink shrink space;--没变化是吧，用alter shrink试下看看测试表segment有变化吗
Table altered.
SQL> select segment_name,blocks from user_segments where segment_type='TABLE' and segment_name='TEST_SHRINK';---这下测试表(blocks表示分配给测试表segment的block个数）segment变了吧,从原来的12288块到8952块，少
了近4000块
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
TEST_SHRINK 8952
SQL> exec dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'TEST_SHRINK',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select table_name,blocks,empty_blocks,num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),row_movement from user_tables ---user_tables中的blocks总是小于user_segments的blockswhere
table_name='TEST_SHRINK';--
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS TO_CHAR(LAST_ANALYZ ROW_MOVE
------------------------------ ---------- ------------ ---------- ------------------- --------
TEST_SHRINK 8807 0 659108 2010-08-28 14:35:01 ENABLED
SQL>
小结:
小结：
1,alter table shrink space compact cascade;对大表或大索引操作会产生大量日志
2,alter table shrink space;--加上compact选项仅重新整理segment 空间，并压缩表的记录在以后进行release空间.但数据库并不调整hwm及释放空间.为了释放空间.你必须再发布alter table shrink space
--compact用于把一个长操作分割为两个较短的操作
--须开启行移动
--cascade会级联对其依赖对象（比如上面的索引）进行压紧操作
3,它的一些限制条件：
在集群表，long列的表不能采用shrink 操作
在基于函数索引或位图联接索引不支持shrink操作
就是你指定了cascade选项，也不能shrink 索引组织表的映射表
压缩表不能用shrink操作
构建了on commit物化视图的主表，在shrink操作后，rowid实化视图必须重建
4,alter table shrink space;--仅可对automatic segment management tablespace方式的table,index_orginized table or overflow segment,index,partition,lob segment,mv,mv log
--压缩segment,调整hwm,并马上释放空间
5,user_tables某些列（请查对官方手册，若标有*标记，表示此列须收集统计信息才会被填充,比如num_rows，所以及时分析表很重要啊
blocks表示使用的块数 empty_blocks 表示从未使用的块数 avg_row_len 表示每个行的长度（以byte计）
nested 表示是否为nested table(值为y or n)
iot_type表示是否为index-orginized table(值为iot,iot_overflow,若不是iot,值为null)
temporary表示在当前会话是否仅仅看到对象上面的数据
global_stats表示（对于分区表），是收集了全表的统计信息（值为yes)还是根据分区表的基础表或子分区估计统计信息（值 为no)
duration 表示临时表中数据的保持时间（值sys$session表记录仅在会话期间保持），而值sys$transaction在commit后删除
skip_corrupt表示oracle在检查表或索引中标记为破坏状态的块,是否进行忽略处理呢（值为enabled or disabled),为了 enabled必须用dbms_repair.skip_corrupt_blocks标记坏块
---测试学习user_segment相关列含义及user_tables相关列含义，且二者字典的关联
SQL> select table_name,blocks,blocks*8/1024 mb,empty_blocks,num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),row_movement from user_tables where table_name='TEST_SHRINK';
TABLE_NAME BLOCKS MB EMPTY_BLOCKS NUM_ROWS TO_CHAR(LAST_ANALYZ ROW_MOVE
------------------------------ ---------- ---------- ------------ ---------- ------------------- -------- ###表有68m左右
TEST_SHRINK 8807 68.8046875 0 659108 2010-08-28 14:35:01 ENABLED
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_name='TEST_SHRINK'; ##表有69m 左右(同上面68m 差不多）
SEGMENT_NAME MB
--------------------------------------------------------------------------------- ----------
TEST_SHRINK 69.9375
SQL> select segment_name,segment_type,initial_extent,next_extent,pct_increase,min_extents,max_extents from user_segments where segment_name='TEST_SHRINK';---请注意initial_extent列，为65536bytes，也就是一个8
block的extent,请继续对应查看user_extents字典
SQL> select segment_name,segment_type,initial_extent,next_extent,pct_increase,min_extents,max_extents,extents from user_segments where segment_name='TEST_SHRINK';##有80个extent
SEGMENT_NAME SEGMENT_TYPE INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE MIN_EXTENTS MAX_EXTENTS EXTENTS
--------------------------------------------------------------------------------- ------------------ -------------- ----------- ------------ ----------- ----------- ----------
TEST_SHRINK TABLE 65536 1 2147483645 80
SQL>
SQL> select count(*) from user_extents where segment_name='TEST_SHRINK';--这不，对应上了，就是分配了80个extent为测试表segment
COUNT(*)
----------
80
SQL> select segment_name,extent_id,bytes,blocks from user_extents where segment_name='TEST_SHRINK';##oracle在分配extent时会根据不同算法可能每个extent的大小不一样，也就是每个extent包含的blocks个数不同
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 0 65536 8
TEST_SHRINK 1 65536 8
TEST_SHRINK 2 65536 8
TEST_SHRINK 3 65536 8
TEST_SHRINK 4 65536 8
TEST_SHRINK 5 65536 8
TEST_SHRINK 6 65536 8
TEST_SHRINK 7 65536 8
TEST_SHRINK 8 65536 8
TEST_SHRINK 9 65536 8
TEST_SHRINK 10 65536 8
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 11 65536 8
TEST_SHRINK 12 65536 8
TEST_SHRINK 13 65536 8
TEST_SHRINK 14 65536 8
TEST_SHRINK 15 65536 8
TEST_SHRINK 16 1048576 128
TEST_SHRINK 17 1048576 128
TEST_SHRINK 18 1048576 128
TEST_SHRINK 19 1048576 128
TEST_SHRINK 20 1048576 128
TEST_SHRINK 21 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 22 1048576 128
TEST_SHRINK 23 1048576 128
TEST_SHRINK 24 1048576 128
TEST_SHRINK 25 1048576 128
TEST_SHRINK 26 1048576 128
TEST_SHRINK 27 1048576 128
TEST_SHRINK 28 1048576 128
TEST_SHRINK 29 1048576 128
TEST_SHRINK 30 1048576 128
TEST_SHRINK 31 1048576 128
TEST_SHRINK 32 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 33 1048576 128
TEST_SHRINK 34 1048576 128
TEST_SHRINK 35 1048576 128
TEST_SHRINK 36 1048576 128
TEST_SHRINK 37 1048576 128
TEST_SHRINK 38 1048576 128
TEST_SHRINK 39 1048576 128
TEST_SHRINK 40 1048576 128
TEST_SHRINK 41 1048576 128
TEST_SHRINK 42 1048576 128
TEST_SHRINK 43 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 44 1048576 128
TEST_SHRINK 45 1048576 128
TEST_SHRINK 46 1048576 128
TEST_SHRINK 47 1048576 128
TEST_SHRINK 48 1048576 128
TEST_SHRINK 49 1048576 128
TEST_SHRINK 50 1048576 128
TEST_SHRINK 51 1048576 128
TEST_SHRINK 52 1048576 128
TEST_SHRINK 53 1048576 128
TEST_SHRINK 54 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 55 1048576 128
TEST_SHRINK 56 1048576 128
TEST_SHRINK 57 1048576 128
TEST_SHRINK 58 1048576 128
TEST_SHRINK 59 1048576 128
TEST_SHRINK 60 1048576 128
TEST_SHRINK 61 1048576 128
TEST_SHRINK 62 1048576 128
TEST_SHRINK 63 1048576 128
TEST_SHRINK 64 1048576 128
TEST_SHRINK 65 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 66 1048576 128
TEST_SHRINK 67 1048576 128
TEST_SHRINK 68 1048576 128
TEST_SHRINK 69 1048576 128
TEST_SHRINK 70 1048576 128
TEST_SHRINK 71 1048576 128
TEST_SHRINK 72 1048576 128
TEST_SHRINK 73 1048576 128
TEST_SHRINK 74 1048576 128
TEST_SHRINK 75 1048576 128
TEST_SHRINK 76 1048576 128
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
TEST_SHRINK 77 1048576 128
TEST_SHRINK 78 1048576 128
TEST_SHRINK 79 6225920 760
80 rows selected.
SQL> select 8*8*1024*1024 from dual;
8*8*1024*1024
-------------
67108864
SQL> select 8*8*1024 from dual;
8*8*1024----------
65536
- 测试alter table shrink space compact cascade及学习user_tables相关列的含义
- 测试alter table shrink space compact cascade及学习user_tables相关列的含义
- alter table XXX shrink space compact/cascade详解
- shrink space cascade/compact
- alter table move和alter table shrink space的区别
- alter table move跟shrink space的区别 [很好!]--顺便想下如何做测试!
- 182 Examine the following command: SQL> ALTER TABLE booking SHRINK SPACE COMPACT; Which activity is
- 187 Examine the following command: SQL> ALTER TABLE booking SHRINK SPACE COMPACT; Which activity is
- alter table move跟shrink space的区别
- alter table move跟shrink space的区别
- alter table move跟shrink space的区别
- alter table move跟shrink space的区别
- alter table move跟shrink space的区别
- alter table move 与shrink space的区别
- alter table move跟shrink space的区别
- alter table move跟shrink space的区别
- alter table move 与shrink space的区别
- alter table move跟shrink space的区别
- typedef用法小结
- Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No bean named 'http://10
- VC2005:无法执行添加/移除操作,因为代码元素是只读的....多个解决办法
- 全面解读WM_NOTIFY
- 关void (*signl(int signo,void(*func)(int)))(int);的解析
- 测试alter table shrink space compact cascade及学习user_tables相关列的含义
- 《微型电脑应用》2011年第11期刊登出《万能数据库查询分析器中的事务管理在Oracle中的应用》
- 如何做优秀的营销员呢
- PowerDesigner生成MySQL5.0中带有Index的代码有错误
- Oracle动态修改表的字段
- 使用TCP+winsock写的一个简单服务器程序（转）
- Flash p2p服务（代号Cirrus）新加的两个特性
- 利用PHP模版生成静态的HTML
- 一个人不去逼自己一把，不知道自己有多优秀。
