ITPub博客

有关一致性读的过程确实非常复杂...希望有一天能以试验的方式清晰的解读这个过程...[@more@]CREATING A READ CONSISTENT VIEW STEPS 1. Read the Data Block. If the block is resident in memory, create a clone to perform the undo. 2. Read the Row Header. 3. Check the LockByte to determine if there is an ITL entry. 4. Read the ITL to determine the Transaction ID.Automatic UNDO Internals5. Read the Transaction Table. If the transaction has been committed and has a System Commit Number less than the query’s

doc解释的很清楚，看doc吧The number of undo segments created and brought online is a function of the SESSIONS parameter. The algorithm is roughly 1 undo segment for each 5 sessions. All undo segments are placed online when the sessions parameter is set to 46 or greater. The lower limit was not tested, as the minimum value for the sessions parameter in the test database was 16, which was derived from the minimum number of processes (10) for the database. These are sized according to the autoalloca

当并发事务的数量超过在线undo segment的数量时undo segment被smon自动创建， 我记得不知道在哪儿看到过说：undo segment的创建会记录在alert中，但是没有发现...还是记错了...One of the benefits of AUM is the dynamic creation of undo segments as needed. When the number of concurrent transactions exceeds the number of online undo segments, more segments are brought online. If there are offline segments in the current undo tablespace, they are the first ones to be used. If no offline segments exist, new ones

When a transaction needs more space in an undo segment, there is a fixed algorithm the determines when extents are reused.. The transaction will first attempt to reclaim expired extents from the current segment, then expired extents from another segment. The third method will be to autoextend the datafile(s), if this is set. Next, the unexpired extents from the current segment and other segments will be allocated, as long as the extents do not contain uncommitted transactions. Finally, an error

什么是BLOCK CLEANOUT！[@more@]BLOCK CLEANOUT When a data block is altered, important transaction information is written to the block along with the data changes. When the transaction is committed, the block is not rewritten to reflect the new transaction state. The next transaction, whether a SELECT or INSERT/UPDATE/DELETE, will ‘discover’ the out of date information and update the block to reflect the committed state of the previous transaction. This is called ‘delayed block cleanout.’ It i

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

晕死了，当修改os登陆用户密码时，ibm rescue and recovery总是会自动出来让我们修改密码，前端时间不小心设置了密码，结果昨天机器貌似中毒了，想恢复下系统，nnd，结果密码忘了，google一下找到了一篇文章，很受用。1.通过在run里执行msconfig---->startup----->ibmprc(取消)来屏蔽修改os user密码时弹出修改ibm rescue and recovery窗口2.如果真给ibm rescue and recovery设置了密码而又忘记了密码，那么把C:IBMSHARE下的文件ibmpwhp.pyc请空，不要删除，删除了我尝试了一下好像不好用，还是会提示旧密码的；执行步骤1时会自动删

没有实质性的结论，暂时记个过程吧。[@more@]RMAN> backup database;启动 backup 于 26-3月 -09使用通道 ORA_DISK_1通道 ORA_DISK_1: 启动全部数据文件备份集通道 ORA_DISK_1: 正在指定备份集中的数据文件输入数据文件 fno=00001 name=E:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF输入数据文件 fno=00003 name=E:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF输入数据文件 fno=00004 name=E:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF输入数据文件 fno=00007 name=E:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS02.DBF通道 ORA_DISK_1: 正

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

讲课时给学员举的一个例子，顺手记录一下！[@more@]create or replace trigger trigg_traceafter insert or update or delete on t1 for each rowdeclare -- local variables here v_id int; v_name varchar2(10); v_flag varchar2(10);begin if inserting then v_id := :new.id; v_name :=:new.name; v_flag :='insert'; else if updating then v_flag :='update' ; else v_flag :='delete' ; end if ; v_id := :old.id; v_name :=:old.name; end if ; insert int

当表空间中的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数据库