您好,登录后才能下订单哦!
这篇文章主要讲解了“oracle表优化方法教程”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“oracle表优化方法教程”吧!
1、建立实验表
create table t_pctfree
(
id number,
name varchar2(2000),
name1 varchar2(2000),
name2 varchar2(2000),
name3 varchar2(2000),
name4 varchar2(2000)
)
tablespace users
pctfree 10;
Table created.
2、添加数据,先只写id,其他值为null
SQL>insert into t_pctfree(id) values(2);
1 row created.
SQL> commit;
Commit complete.
3、使用dump查看数据块,确定id为2的数据只存在于一个块中
SQL> select dbms_rowid.rowid_block_number(rowid) block_id from t_pctfree where id=2;
BLOCK_ID
----------
47547
SQL> alter system dump datafile 5 block 51031;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
[oracle@localhost trace]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
tab 0, row 0, @0x1f92
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f8c
tl: 6 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 2] c1 03
tab 0, row 2, @0x1f86
SQL> var n number;
SQL> exec dbms_stats.convert_raw_value('c103',:n);
PL/SQL procedure successfully completed.
SQL> print :n
N
----------
2
4、修改id为2的数据,使当前块剩余空间容纳不下修改后的数据
SQL> update scott.t_pctfree set name=dbms_random.string('u', 2000) where id=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
.
5、查看修改后的块状态
SQL> select dbms_rowid.rowid_block_number(rowid) block_id from t_pctfree where id=2;
BLOCK_ID
----------
47547
SQL> alter system dump datafile 5 block 51031;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
[oracle@localhost trace]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
tab 0, row 1, @0xdec
tl: 9 fb: --H----- lb: 0x1 cc: 0
nrid: 0x0140c75b.0
6、发现已产生行迁移,源块只留下了迁移块的地址,通过nrid查看迁移目标块信息
SQL> select dbms_utility.DATA_BLOCK_ADDRESS_FILE(to_number('0140c75b', 'xxxxxxxxxx')) file#,dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(to_number('0140c75b','xxxxxxxxxx')) block# from dual;
FILE# BLOCK#
---------- ----------
551035
SQL> alter system dump datafile 5 block 51035;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
[oracle@localhost trace]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
tab 0, row 0, @0x17a1
tl: 2015 fb: ----FL-- lb: 0x1 cc: 2
hrid: 0x0140b9bb.1
col 0: [ 2] c1 03
col 1: [2000]
5a 52 57 58 53 54 45 4a 50 4e 56 43 4c 55 4e 4e 4d 47 59 49 51 50 44 41 41
4b 4d 47 56 52 49 5a 51 55 47 54 54 5a 51 41 49 5a 55 57 43 58 46 42 54 55
7、可以看到迁移目标块只有hrid,没有nrid,说明只出现了行迁移,没有出现行链接
感谢各位的阅读,以上就是“oracle表优化方法教程”的内容了,经过本文的学习后,相信大家对oracle表优化方法教程这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。