oracle 10G新功能验证

SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'EMP';
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS
SCOTT EMP 3
SES2>select rownum from scott.emp where rownum=1 for update nowait;
ROWNUM
2.强行终止后
已经有Full Blocks,空的block在减少
3.正常完成后
free blocks没有了
补充一些
----------------------------------
段回缩(适用于ASSM表空间)
适用表，索引，大对象，IOT，物化视图
alter table tbname row movement
▽dbms_space.space_usage 整理前输出
Segment Owner = SCOTT
Segment Name = EMP
Unformatted Blocks = 16
0 - 25% free blocks= 0
25- 50% free blocks= 6366
move命令:
SQL> alter table emp move;
ORA-01652: unable to extend temp segment by 8 in tablespace AUTOSEG_TST
AUTOSEG_TST 表空间不足
SQL> select f.tablespace_name,d.file_name,f.bytes/1024 KBytes,f.blocks,d.autoextensible
----------
1
shrink比起move最大的不同是在object上没有排他锁.
可以从LOCKED_MODE列上看出,在Move命令里该值是6,即排他锁
而在shrink命令里该值是3,行级锁
所以在不停止业务的情况下可以对表进行重组
◇2 shrink命令执行途中即使被强行终止,也可以完成一部分的空间整理
ORA-01502: index 'SCOTT.PK_DEPT' or partition of such index is in unusable state
SQL> alter index pk_dept rebuild;
Index altered.
SQL> select /*+ index(dept pk_dept) */ * from dept where rownum=1;
25- 50% free blocks= 2
50- 75% free blocks= 0
75-100% free blocks= 0
Full Blocks = 4567
使用dbms_space.space_usage可以推算出表shrink的状况
1.整理前
没有Full Blocks,许多25%空的block
where segment_name = 'EMP';
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS
----- ------------ ---------- ---------- ----------
SCOTT EMP 65536 8 1
限制
cluster中的表
有long类型的表
有on_commit物化视图的表
有基于rowid物化视图的表
大对象(LOB)索引
--------------------------
继续前回的shrink功能介绍
前回:/viewthread.php?tid=18436
SES2>select rownum from scott.emp where rownum=1 for update nowait;
ORA-00054:
SES1> alter table emp shrink space;
SES2> select l.oracle_username, objname,l.locked_mode from v$locked_object l,obj$ o
50- 75% free blocks= 0
75-100% free blocks= 36
Full Blocks = 0
▽整理中终止
SQL> alter table emp shrink space;
ORA-00028: your session has been killed
▽强行终止后,表的态
Segment Owner = SCOTT
Segment Name = EMP
Unformatted Blocks = 16
0 - 25% free blocks= 1
25- 50% free blocks= 2808
50- 75% free blocks= 0
from dba_free_space f,dba_data_files d,dba_tables t
where f.tablespace_name = d.tablespace_name
and f.tablespace_name = t.tablespace_name
and t.owner='SCOTT' and t.table_name='EMP';
TABLESPACE_NAME FILE_NAME KBYTES BLOCKS AUTOEXT
--------------- ---------------------------------------- ------- ------- -------
move命令:
移动后index无效,必须重新建
SQL> alter table dept move;
Table altered.
SQL> select owner,index_name,status from dba_indexes where table_owner='SCOTT'
AUTOSEG_TST /export/home/ora10g/oradata/AUTOSEG1.DBF 64 8 NO
shrink命令:
SQL> alter table emp shrink space;
Table altered.
◇4 无须重建索引
where l.object_id=o.obj#;
ORACLE_USERNAME OBJNAME LOCKED_MODE
------------------------------ ------------------------------ -----------
10G新功能验证(1)
10G新功能验证
我们知道当我们对表进行大量的delete操作后,系统的性能会有明星的下降.
在10G以前我们是怎么做的呢?
1.exp/imp
2.alter table xxx move
10g提供一个新的功能
alter table tablenm shrink space
◆环境
Linux 2.4.9-e.24enterprise
Oracle10g EE Release 10.1.0.2.0
◆alter table xxx shrink space VS. alter table xxx move
◇3 即使没有足够的表空间也可以用shrink
move命令必须在对应的表空间里有足够的空间
SES2>select l.oracle_username, objname,l.locked_mode from v$locked_object l,obj$ o
where l.object_id=o.obj#;
ORACLE_USERNAME OBJNAME LOCKED_MODE
如果是这样的话和alter table xxx move的功能基本上还是一样的,他必定有更强大的地方
◆alter table xxx shrink space VS. alter table xxx move
◇1. 在线的shrink
SES1>alter table emp move;
and table_name='DEPT';
OWNER INDEX_NAME STATUS
----- ---------- --------
SCOTT PK_DEPT UNUSABLE ←(index无效)
SQL> select /*+ index(dept pk_dept) */ * from dept where rownum=1;
------------------------------ ------------------------------ -----------
SCOTT EMP 6
※Segment缩小命令必须开启行移动功能
SQL> alter table emp enable row movement;
Table altered.
SQL> alter table emp shrink space;
Table altered.