ITPub博客

Dump undo blocks associated with specific transaction Alter system dump undo block ‘segment_name’ xid usn slot seq; [@more@]SQL> insert into t select object_id,object_name from dba_objects;已创建11354行。SQL> select xidusn,xidslot,xidsqn,xid from v$transaction; XIDUSN XIDSLOT XIDSQN XID---------- ---------- ---------- ---------------- 2 15 212 02000F00D4000000SQL> select ubafil,ubasqn,ubarec,start_ubafil,start_ubasqn,start_ubarec from v$transaction;

留下了很多疑问，暂时记录一下slot被覆盖之前会被保存下来吗？这个我想肯定不会.........如果是这样，为什么在覆盖之后没有出现ora-01555?[@more@]rollback segment rbs2中一共有21个slot，连续启动20个事务占去20个slot，目的只是为了测试的需要，只留一个slot不停的操作事务，目的是能让其他事务覆盖slot从而测试consistentread的过程和ora-01555:20个事务启动之后的slot使用如下：session 2:SQL> select xidslot,start_time from v$transaction order by xidslot;XIDSLOT START_TIME------- -------------------- 0 04/07/09 0

这是一个delay block cleanout的最基本的测试，slot被覆盖的情况再讨论。[@more@]session 1:SQL> insert into tt select * from tt;已创建180096行。SQL> select xidusn from v$transaction; XIDUSN---------- 37SQL> select usn,name from v$rollname where usn=37; USN NAME---------- ------------------------------ 37 _SYSSMU37$session 2--analyze table是ddl命令，在session 1中执行此命令会commit事务SQL> analyze table tt compute statistics;表已分析。--验证一下事务还在SQL> select

值得仔细研究[@more@]Undo/rollbackOverview Undo/rollback segment is used for transaction rollback and read consistent or flashback query. There’re two management methods: auto and manual, respectively called undo and rollback. The basic structure is the same. Every database should have two type undo/rollback space: system and user data. The system undo/rollback is allocated from system tablespace, it mainly used for dictionary data. In RAC/OPS environment, every instance has its own private undo

貌似测的还不够精准，同时没有找到具体介绍10%的相应doc，暂时记录一下。[@more@]SQL> alter system set sga_target=0;系统已更改。SQL> show parameter db_cacheNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_cache_advice string ONdb_cache_size big integer 88MSQL> alter system set db_cache_size=80m;系统已更改。SQL> create table tt tablespace users as select * from dba_objects;表已创建。SQL> insert into tt select *from tt;已创建11389行。SQL&gt

The U stands for "upper bound commit", meaning that the SCN is an upper bound for the time at which it committed. Note that the U flag in the ITL is also set for a fast commit, but the two cases can be distinguished by the presence or absence of row-level locks. 本例中测试的仅仅是fast commit[@more@]SQL> select id,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno from t;ID FNO BNO---------- ---------- ----------1 1 329622 1 329623 1 329624 1 329625 1 3

itl在被覆盖之前放在了事务的undo chain(单向链表的尾)的start_ubablk中以备之后构造consistent read而使用。[@more@]SQL> create table t(id int) initrans 1;表已创建。SQL> insert into t values(1);已创建 1 行。SQL> select id,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno from t; ID FNO BNO---------- ---------- ---------- 1 1 32962--=============================== session 2:(session 2的这个操作没有任何实际意义，

究竟是被保存在覆盖它的这个事务所使用的undo的chain的end block还是start block中这个例子中没有得到论证，改天再验证。这里先临时记录一下。[@more@]session 1:SQL> create table t1(id int) initrans 1;表已创建。SQL> insert into t1 values(1);已创建 1 行。SQL> select id,rowid from t1; ID ROWID---------- ------------------ 1 AAAC49AABAAAIIaAAASQL> select id,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno from t1; ID FNO BNO----

以前写过一篇文章介绍事务使用的undo block其实一条单向链表，那么undo header上记录的dba到底是这条单向链表的头还是尾，经验证是头，我猜测也应该是头，因为当rollback事务时才能以最快的速度找到rollback的起点，当然这样做的目的可能不仅仅是为了rollback操作。[@more@]SQL> create table tt1 tablespace users as select *from dba_objects;表已创建。SQL> insert into tt1 select * from tt1;已创建11280行。SQL> select xidusn,ubafil,ubablk,start_ubafil,start_ubablk from v$transaction;XIDUSN UBAFIL UBABLK START_U

有未提交的事务依然断开，比较恐怖，断开时自动做了提交，其实就相当于做了disconnect[@more@]SQL> select * from t1;未选定行SQL> insert into t1 values(1,'a');已创建 1 行。SQL> select * from t1;select * from t1*第 1 行出现错误:ORA-02396: 超出最大空闲时间, 请重新连接SQL> exit从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options 断开C:>sqlplus c/c@abcSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 19 14:21:53 20

当表空间中的segment上有事务存在时该表空间在其他session中无法read only。[@more@]session 1:SQL> CREATE TABLE T1(ID INT) TABLESPACE TEST;表已创建。SQL> INSERT INTO T1 VALUES(1);已创建 1 行。session 2:SQL> alter tablespace test read only;--上面操作会一直挂在这里（等待）session 1：SQL> commit;提交完成。--提交完成之后，session 2的read only操作完成如下：SQL> alter tablespace test read only;表空间已更改。session 1:SQL> INSERT INTO T1 VALUES(2);已创建 1 行。SQL> alter tablespace test

flashback_transaction_query的数据完全来自undo datafile![@more@]flashback_transaction_query是一个视图，定义如下create or replace view sys.flashback_transaction_query asselect xid, start_scn, start_timestamp,decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)commit_scn, commit_timestamp,logon_user, undo_change#, operation, table_name, table_owner,row_id, undo_sqlfrom sys.x$ktuqqrysys.x$ktuqqry是一个动态性能表--=======================================SQL> shutdown immediate数据库

--其实主要介绍了undo中的2条单向链表，明白了这2条链，有助于我们更深入的研究undo...[@more@]--创建一个测试表tt，注意按照object_id事先排序了SQL> create table tt tablespace users as select * from dba_objects order by object_id;表已创建。--查询一下object_id的最大值和最小值后面分析会用到SQL> select min(object_id),max(object_id) from tt;MIN(OBJECT_ID) MAX(OBJECT_ID)-------------- -------------- 2 10327SQL> update tt set object_id=1;已更新9882行。--查看事务使用undo的情况SQL&

http://www.ixora.com.au/notes/redo_latching.htmhttp://www.ixora.com.au/notes/log_buffer_usage.htmhttp://www.ixora.com.au/notes/log_block_size.htm碰到了随手记录一下！[@more@]http://www.ixora.com.au/notes/redo_latching.htmhttp://www.ixora.com.au/notes/log_buffer_usage.htmhttp://www.ixora.com.au/notes/log_block_size.htm

困扰了很久的问题得以解决，比较兴奋，尽管对我们使用而言可能用处不大！[@more@]SQL> create table t(id int ,name varchar2(10)) tablespace users;表已创建。SQL> insert into t values(1,'abc');已创建 1 行。SQL> insert into t values(2,'bcd');已创建 1 行。SQL> insert into t values(3,'cde');已创建 1 行。SQL> insert into t values(123,'test');已创建 1 行。SQL> insert into t values(321,'testabc');已创建 1 行。SQL> commit;提交完成。SQL> create index idx_t_id on t(id desc);索引已创建。SQ

困扰自己很久的问题终于解决了,以前有个误区，以为这个值是指hwm以下blocks的avg space！[@more@]其实没啥用途，只是以前一直是我心中的疑问，突然解惑，总结一下，分享！--=================================有关这个字段先看看doc上的解释：AVG_SPACE* NUMBER Average amount of free space, in bytes, [b][color=Red]in a data block allocated to the table [/color][/b]--=================================另外我心中有一个疑问：一个block是被使用过还是从未被使用过oracle是根据block上的啥标记来判断的？itl？该疑问与本贴内容

在assm下，shrink时可以结合dbms_space.space_usage来观察空间的shrink情况[@more@]　　alter table tbname row movement--=======================================　　保持HWM　　alter table tbname shrink space compact;　　回缩表与HWM　　alter table tbname shrink space;　　回缩表与相关索引　　alter table tbname shrink space cascade;　　回缩索引　　alter index idxname shrink space;如果index space被shrink的话，index的hwm也会下降，这一点可以通过dump index segment header block 观察到，另外值得注意的是shrink之

在线重建一个index时无意中发现了这样的问题，varchar2类型的列不能超过3201，这可能是一个内部限制。SQL> alter index idx_t rebuild online;alter index idx_t rebuild online*第 1 行出现错误:ORA-00604: 递归 SQL 级别 1 出现错误ORA-01450: 超出最大的关键字长度 (3215)[@more@]SQL> desc t名称 是否为空? 类型----------------------------------------- -------- ----------------------------ID NUMBER(38)NAME VARCHAR2(3202)SQL> alter index idx_t rebuild online;alter index idx_t rebuild online*第 1 行出现错误:

11g v$process中增加了一个有用的字段tracefile！[@more@]11g，v$process中增加了一个tracefile字段，该字段记录的是进程产生的tracefile，一个看起来不起眼的字段却给我带来了很大的方便，以前我们要确认进程所产生的trace文件需要通过下面方法：1.首先需要知道当前的sessionidSQL> select sid from v$mystat where rownum=1; SID---------- 1442.之后需要知道该session所对应的process addressSQL> select paddr from v$session where sid=144;PADDR--------226958503.根据process address进一步需要知道os process(t

利用这个功能结合参数skip_unusable_indexes 在做大数据量的dml操作时可能会非常的有用！[@more@]SQL> select * from t2;ID----------12SQL> create index idx_t2 on t2(id);索引已创建。SQL> select index_name, status from dba_indexes where index_name='IDX_T2';INDEX_NAME STATUS------------------------------ --------IDX_T2 VALIDSQL> alter index idx_t2 unusable;索引已更改。SQL> select index_name, status from dba_indexes where index_name='IDX_T2';INDEX_NAME STATUS------------------------------ --