ITPub博客

也就是说，不加using backup controlfile的情况。在普通的recover database 或者 recover tablespace, recover datafile时， Oracle会以当前controlfile所纪录的SCN为准，利用archive log和 redo log的redo entry, 把相关的datafile 的 block恢复到“当前controlfile所纪录的SCN”而某些情况下，Oracle需要把数据恢复到比当前controlfile所纪录的SCN还要靠后的位置（比如说，control file是backup controlfile , 或者 controlfile是根据trace create的。）,这时候，就需要用using backup controlfile. 恢复就不会受“当前controlfile所纪录

嵌套循环的程序逻辑相当于for i in .. loop for j in .. loop if (matched)output end if;end loop;end loop;所以它相对于merge join和hash join来说比较快的返回1行因为merge join要先sort两个表之后,才开始做joinhash join要先构造驱动表的hash tablealter session set events '10046 trace name context forever, level 12';用10046跟踪了一下：怎么看“第一次fetch的结果都是1行”呢，还有每次fetch返回多少行呢？以下是部分dump内容：PARSE #3:c=10000,e=4853,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=36791549501158BINDS #3:EXEC #

I/O bound 指的是系统的CPU效能相对硬盘/内存的效能要好很多，此时，系统运作，大部分的状况是 CPU 在等 I/O (硬盘/内存) 的读/写，此时 CPU Loading 不高。CPU bound 指的是系统的 硬盘/内存 效能 相对 CPU 的效能 要好很多，此时，系统运作，大部分的状况是 CPU Loading 100%，CPU 要读/写 I/O (硬盘/内存)，I/O在很短的时间就可以完成，而 CPU 还有许多运算要处理，CPU Loading 很高。计算密集型 (CPU-bound) 在多重程序系统中，大部份时间用来做计算、逻辑判断等CPU动作的程序称之CPU bound。例如一个计算圆周率至小数点一千位以下的程

The crash recovery process is a special form of recovery, which happens the first time an Oracle database instance is started after a crash (or SHUTDOWN ABORT). In crash recovery, the goal is to bring the datafiles to a transaction-consistent state, preserving all committed changes up to the point when the instance failed.Like crash recovery, datafile media recovery is intended to restore database integrity. However, there are a number of important differences between the two:1.Media recovery mu

Recent entries in the redo thread of an Oracle instance are addressed using a 3-part redo byte address, or RBA. An RBA is comprised of the log file sequence number (4 bytes) the log file block number (4 bytes) the byte offset into the block at which the redo record starts (2 bytes) RBAs are not necessarily unique within their thread, because the log file sequence number may be reset to 1 in all threads if a database is opened with the RESETLOGS option. RBAs are used in the following important wa

我们知道Oracle通过两种方式从表中获取数据：· 通过ROWID（通常使用索引扫描时）· 通过全表扫描如果通过ROWID读取数据，表中的区间数就不是读性能的一个因素（如果使用并行查询，那么一个表中有较多的区间的数量可以明显提高I/O的性能），Oracle将通过ROWID直接找到需要的行，并获取相应数据。如果是全表扫描，那么区间的尺寸大小就有可能导致性能问题。因为全表扫描时，Oracle会一次读取多个Blocks。每次读取的块数将受初始化参数DB_FILE_MULTIBLOCK_READ_COUNT和操作系统的I/O缓冲区大小的限制。比如说，如果Oracle Block的大小是4KB，

/dev = DEVices/etc = ETCetera/lib = LIBrary/proc = PROCesses/sbin = Superuser BINaries/tmp = TeMPorary/usr = Unix Shared Resources/var = VARiable ?FIFO = First In, First OutGRUB = GRand Unified BootloaderIFS = Internal Field SeperatorsLILO = LInux LOaderMySQL = My是最初作者女儿的名字，SQL = Structured Query LanguagePHP = Personal Home Page Tools = PHP Hypertext PreprocessorPS = Prompt StringPerl = "Pratical Extraction and Report Language" = "Pathologically Eclectic Rubbish Lister&

Oracle在数据库内部通过相应的算法转换来进行数据存储,本文简单介绍Oracle的Number型数值存储及转换.这个内容是为了回答留言板上的2119号问题. 我们可以通过DUMP函数来转换数字的存储形式,一个简单的输出类似如下格式: SQL> select dump(1) from dual;DUMP(1)------------------Typ=2 Len=2: 193,2 DUMP函数的输出格式类似: 类型 <[长度]>，符号/指数位 [数字1，数字2，数字3，......，数字20] 各位的含义如下: 1.类型: Number型,Type=2 (类型代码可以从Oracle的文档上查到) 2.长度:指存储的字节数 3.符号/指数位 在存储上,Orac

現在被Oracle 9i的NVarchar的Unicode問題困擾很久了，在網上看到一篇文章KB-Oracle 9i NVarchar求生守則可 以在C#里解決這個問題，我想知道如何在Delphi 2009里解決此問題（delphi 2009現已支持Unicode了），或者如何 在PL/SQL里用SQL語句直接插入Unicode字符 附KB-Oracle 9i NVarchar求生守則原文 KB-Oracle 9i NVarchar求生守則 同事反應，一個CharacterSet設為ZHT16BIG5的Oracle 9i的資料庫，其中某個Table的NVarchar2欄位完全不接受非BIG5字元，存入時會變成"?"。攪和了近一個月，反覆嘗試了多種工具、方法，也請教了朋友、

PURPOSE ------- In this document we are going to explain how to check that asynchronous I/O (AIO) is working. AIO can be enabled in Oracle 9i 9.2 and higher. SCOPE & APPLICATION ------------------- Many times there is a requirement to check if Asynchronous I/O is working on Linux Platform, so we can try to use it for our datafiles access inside database. SOLUTION -------- slabinfo maintains statistics about objects in memory. Some of the structs used by Asynchronous I/O a

將大的事務切割成小的事務，這樣可以減少undo的需求及鎖的掌控時間example：1. 創建一個大表lock_test01，創建遊標並使用它declarecursor test_cur isselect * from lock_test01 where name='YAO';v_test lock_test01%rowtype;beginopen test_cur;loopfetch test_cur into v_test;insert into lock_test02 values(v_test.name,v_test.pnl);commit;exit when test_cur%notfound;end loop;end;/2.開啟另一個sqlplus, 查看先前鎖的狀況SQL> SELECT sid,type,id1,id2,lmode,request,block FROM V$LOCK WHERE SID=9;SQL> SELECT sid,type

启用SHARED SERVERS和关闭只需要把SHARED_SERVERS设置为大于1的值，系统即转为SHARED SERVER。关闭SHARED SERVERS只需要把该参数设置为0。SQL> alter system set shared_servers=2; #更改已成功系统已更改。SQL> select name,status from v$shared_server; #结果显示有S000和S001两个SHARED SERVERS PROCESS正运行NAME STATUS---- ----------------S000 WAIT(COMMON)S001 WAIT(COMMON)SQL> alter system set dispatchers="(protocol=tcp)(dispatcher=2)";系统已更改。SQL> select name,status,accept as

Oracle Query processing 的程序當一個查詢statement送出時，他的執行程序為：(from Jonathan Lewis web site) Perform syntax check Perform semantic check Perform hash function Perform library cache lookup If hash value found then .....If command is identical to existing one in cache then ..........If the objects referenced in the cached command are the same as the ones in the new command then ...............This is a soft parse, go to step 11 This is a hard parse, build parse tree --> (parse 程序)

Oracle allows the assignment of special escape characters to tell Oracle that the character is interpreted literally. Certain characters such as the underscore “_” are not interpreted literally because they have special meaning within Oracle.In the example below, we want to find all Oracle parameter that relate to I/O, so we are tempted to use the filter LIKE “%_io_%’. Below we will select from the x$ksppi fixed table, filtering with the LIKE clause:SQL> select ksppinm from x$ksppi whe