<html>਍㰀琀椀琀氀攀㸀䄀氀戀攀爀琀 瘀愀渀 搀攀爀 匀攀氀 㨀 匀漀氀瘀椀渀最 伀爀愀挀氀攀 䈀氀漀挀欀 䌀漀爀爀甀瀀琀椀漀渀㰀⼀琀椀琀氀攀㸀ഀഀ
<body>਍ഀഀ
<h1>Some notes on handling Oracle corruption</h1>਍ഀഀ
<B>Version</B>	    : 1.1<br>਍㰀䈀㸀䐀愀琀攀㰀⼀䈀㸀ऀऀ㨀 ㈀㠀⼀㄀　⼀㈀　㄀　㰀戀爀㸀ഀഀ
<B>By</B>		    : Albert van der Sel<br>਍㰀䈀㸀吀礀瀀攀 漀昀 搀漀挀㰀⼀䈀㸀ऀ㨀 䤀琀✀猀 樀甀猀琀 愀 昀攀眀 渀漀琀攀猀 漀渀 伀爀愀挀氀攀 䈀氀漀挀欀 愀渀搀 䘀椀氀攀 挀漀爀爀甀瀀琀椀漀渀 椀渀 瘀攀爀礀 猀椀洀瀀氀攀 眀漀爀搀猀⸀ 䤀琀✀猀 渀漀 洀漀爀攀 琀栀愀渀 ∀攀渀琀爀礀 氀攀瘀攀氀∀⸀㰀戀爀㸀ഀഀ
<B>For who</B>                : For anyone who likes a short orientation on the subject.<br>਍ഀഀ
<hr/>਍㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍ഀഀ
<br>਍䄀氀琀栀漀甀最栀 礀漀甀 搀漀 渀漀琀 渀攀攀搀 琀漀 戀攀 愀渀 攀砀瀀攀爀琀Ⰰ 椀琀 椀猀 愀猀猀甀洀攀搀 琀栀愀琀 礀漀甀 栀愀瘀攀 愀琀 氀攀愀猀琀 愀 ∀爀攀愀猀漀渀愀戀氀攀∀ 氀攀瘀攀氀 漀昀 欀渀漀眀氀攀搀最攀 漀昀 伀爀愀挀氀攀⸀㰀戀爀㸀ഀഀ
<br>਍匀漀 眀栀愀琀 挀愀渀 礀漀甀 攀砀瀀攀挀琀 琀漀 昀椀渀搀 椀渀 琀栀椀猀 渀漀琀攀㼀㰀戀爀㸀ഀഀ
<br>਍䌀栀愀瀀琀攀爀 ㄀ 樀甀猀琀 搀攀猀挀爀椀戀攀猀 琀栀攀 挀爀攀愀琀椀漀渀 漀昀 愀 ✀匀䄀䰀䔀匀✀ 搀愀琀愀戀愀猀攀 眀栀椀挀栀 眀椀氀氀 戀攀 甀猀攀搀 琀漀 猀椀洀甀氀愀琀攀 猀攀瘀攀爀愀氀 猀漀爀琀猀 漀昀 挀漀爀爀甀瀀琀椀漀渀⸀㰀戀爀㸀ഀഀ
Chapter 2 will demonstrate a few examples of a soft and hard corruption of data blocks.<br>਍䌀栀愀瀀琀攀爀 ㌀ 愀挀琀甀愀氀氀礀 椀猀 琀栀攀 ∀戀漀搀礀∀ 漀昀 琀栀椀猀 渀漀琀攀⸀ 䤀琀 眀椀氀氀 猀栀漀眀 礀漀甀 栀漀眀 琀漀 爀攀瀀愀椀爀 愀渀 漀戀樀攀挀琀 眀椀琀栀 挀漀爀爀甀瀀琀 戀氀漀挀欀猀⸀㰀戀爀㸀ഀഀ
਍㰀戀爀㸀ഀഀ
<br>਍㰀䈀㸀䌀漀渀琀攀渀琀猀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
<br>਍㰀䈀㸀䌀栀愀瀀琀攀爀 ㄀⸀ 䌀爀攀愀琀椀渀最 琀栀攀 攀砀愀洀瀀氀攀 搀愀琀愀戀愀猀攀Ⰰ 愀渀搀 最攀琀琀椀渀最 愀 栀攀砀 攀搀椀琀漀爀⸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
<br>਍㰀䈀㸀䌀栀愀瀀琀攀爀 ㈀⸀ 䔀砀愀洀瀀氀攀猀 漀昀 猀漀昀琀 愀渀搀 栀愀爀搀 戀氀漀挀欀 挀漀爀爀甀瀀琀椀漀渀 ⠀氀攀琀✀猀 搀漀 椀琀 漀甀爀猀攀氀瘀攀猀⤀⸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
<br>਍ⴀ ㈀⸀㄀⸀ 匀漀昀琀 戀氀漀挀欀 挀漀爀爀甀瀀琀椀漀渀⸀㰀戀爀㸀ഀഀ
- 2.2. Soft corruption of a Database block, and the DB_BLOCK_CHECKING init.ora parameter.<br>਍ⴀ ㈀⸀㌀⸀ 䄀渀愀氀礀稀椀渀最 吀愀戀氀攀猀 愀渀搀 䘀椀氀攀猀 昀漀爀 挀漀爀爀甀瀀琀椀漀渀 ⠀甀猀椀渀最 ∀搀戀瘀∀ 愀渀搀 ∀䄀一䄀䰀夀娀䔀 吀䄀䈀䰀䔀∀⤀⸀㰀戀爀㸀ഀഀ
- 2.4. Hard corruption of a Database block (writing all zero's).<br>਍㰀戀爀㸀ഀഀ
<B>Chapter 3. Repairing objects with corrupt blocks.</B><br>਍㰀戀爀㸀ഀഀ
- 3.1. The DBMS_REPAIR.SKIP_CORRUPT_BLOCKS() procedure (quick fix)<br>਍ⴀ ㌀⸀㈀⸀ 吀栀攀 䐀䈀䴀匀开刀䔀倀䄀䤀刀⸀䘀䤀堀开䌀伀刀刀唀倀吀开䈀䰀伀䌀䬀匀⠀⤀ 瀀爀漀挀攀搀甀爀攀⸀㰀戀爀㸀ഀഀ
- 3.3. The ORA-1578 and ORA-8103 errors.<br>਍ⴀ ㌀⸀㌀⸀ 唀猀椀渀最 匀䔀吀 䔀嘀䔀一吀匀 瀀愀爀愀洀攀琀攀爀猀⸀㰀戀爀㸀ഀഀ
- 3.5. Querying "around" bad blocks.<br>਍ⴀ ㌀⸀㘀⸀ 匀漀洀攀 爀攀洀愀爀欀猀 漀渀 琀栀攀 猀琀爀甀挀琀甀爀攀 漀昀 愀 戀氀漀挀欀⸀㰀戀爀㸀ഀഀ
- 3.7. Using tools and statements to "dump" blocks.<br>਍㰀戀爀㸀ഀഀ
<br>਍㰀戀爀㸀ഀഀ
<h2>Chapter 1. Creating the example database, and getting a hex editor.</h2><br>਍ഀഀ
<h3>1.1 Creating a Database and some Objects:</h3><br>਍ഀഀ
If you have installed Oracle 10g/11g (or 12c), you might already have an Instance (and database) running.<br>਍䤀昀 渀漀琀Ⰰ 䤀 眀漀甀氀搀 氀椀欀攀 礀漀甀 琀漀 挀爀攀愀琀攀 愀 搀愀琀愀戀愀猀攀Ⰰ 瀀爀攀昀攀爀爀愀戀氀礀 眀椀琀栀 琀栀攀 渀愀洀攀 ∀猀愀氀攀猀∀⸀㰀戀爀㸀ഀഀ
The database name is really not so important, but if you need to create a database anyway (in order to practice<br>਍琀栀椀猀 渀漀琀攀⤀Ⰰ 礀漀甀 洀愀礀 愀猀 眀攀氀氀 挀愀氀氀 椀琀 ∀猀愀氀攀猀∀⸀ 䘀甀爀琀栀攀爀洀漀爀攀Ⰰ 椀昀 瀀漀猀猀椀戀氀攀Ⰰ 瀀氀愀挀攀 琀栀攀 搀愀琀愀戀愀猀攀 昀椀氀攀猀 椀渀 ∀挀㨀尀漀爀愀搀愀琀愀尀猀愀氀攀猀∀⸀㰀戀爀㸀ഀഀ
Here too, the exact location of the files is not really important. If you have placed the database files elsewhere,<br>਍樀甀猀琀 爀攀洀攀洀戀攀爀 琀漀 ∀琀爀愀渀猀氀愀琀攀∀ 洀礀 氀漀挀愀琀椀漀渀 ⠀眀栀椀挀栀 椀猀 ∀挀㨀尀漀爀愀搀愀琀愀尀猀愀氀攀猀∀⤀ 琀漀 眀栀愀琀攀瘀攀爀 礀漀甀 甀猀攀⸀㰀戀爀㸀ഀഀ
<br>਍䈀甀琀Ⰰ 椀渀 挀愀猀攀 礀漀甀 猀琀椀氀氀 渀攀攀搀 琀漀 挀爀攀愀琀攀 愀渀 䤀渀猀琀愀渀挀攀⼀搀愀琀愀戀愀猀攀Ⰰ 樀甀猀琀 猀琀愀爀琀 琀栀攀 ∀䐀愀琀愀戀愀猀攀 䌀漀渀昀椀最甀爀愀琀椀漀渀 圀椀稀愀爀搀∀Ⰰ㰀戀爀㸀ഀഀ
which you can find in your Windows Start menu, or just open a cmd prompt and enter "dbca".<br>਍甀猀椀渀最 琀栀攀 ∀䐀愀琀愀戀愀猀攀 䌀漀渀昀椀最甀爀愀琀椀漀渀 圀椀稀愀爀搀∀Ⰰ 挀爀攀愀琀攀 愀 搀愀琀愀戀愀猀攀Ⰰ 愀渀搀 礀漀甀 挀愀渀 甀猀攀 瀀爀攀琀琀礀 洀甀挀栀 甀猀攀 琀栀攀 搀攀昀愀甀氀琀猀㰀戀爀㸀ഀഀ
as suggested by the Wizard.<br>਍䄀最愀椀渀Ⰰ ⠀椀昀 瀀漀猀猀椀戀氀攀⤀ 攀砀挀攀瀀琀 昀漀爀 琀栀攀 搀椀猀欀氀漀挀愀琀椀漀渀 漀昀 琀栀攀 搀愀琀愀戀愀猀攀 昀椀氀攀猀Ⰰ 愀渀搀Ⰰ ⠀椀昀 瀀漀猀猀椀戀氀攀⤀ 挀栀漀漀猀攀 愀 搀愀琀愀戀愀猀攀 戀氀漀挀欀猀椀稀攀 漀昀 㠀㄀㤀㈀ 戀礀琀攀猀⸀㰀戀爀㸀ഀഀ
The blocksize, in principle, could be 4K, 8K, 16K, 64K etc.., but depending on the details of your storage, the Wizard will<br>਍渀漀琀 愀氀眀愀礀猀 愀氀氀漀眀 礀漀甀 琀漀 瀀椀挀欀 愀渀漀琀栀攀爀 戀氀漀挀欀猀椀稀攀Ⰰ 漀琀栀攀爀 愀猀 眀栀愀琀 椀琀 眀椀氀氀 挀漀洀攀 甀瀀 眀椀琀栀⸀㰀戀爀㸀ഀഀ
<br>਍伀渀挀攀 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 挀爀攀愀琀攀搀Ⰰ 眀攀 眀椀氀氀 挀爀攀愀琀攀 愀 挀漀甀瀀氀攀 漀昀 吀愀戀氀攀猀瀀愀挀攀猀 愀渀搀 愀 昀攀眀 漀戀樀攀挀琀猀 漀渀 琀栀漀猀攀 琀愀戀氀攀猀瀀愀挀攀猀⸀㰀戀爀㸀ഀഀ
<br>਍㰀䤀㸀一漀琀攀㨀 椀昀 礀漀甀 愀爀攀 昀愀洀椀氀椀愀爀 眀椀琀栀 匀儀䰀 匀攀爀瘀攀爀㨀 䄀渀 伀爀愀挀氀攀 吀愀戀氀攀猀瀀愀挀攀 爀攀猀攀洀戀氀攀猀 琀栀攀 昀甀渀挀琀椀漀渀 漀昀 愀 匀儀䰀 匀攀爀瘀攀爀 ∀䘀椀氀攀最爀漀甀瀀∀⸀㰀⼀䤀㸀㰀戀爀㸀ഀഀ
<br>਍吀栀攀爀攀 愀爀攀 洀愀渀礀 眀愀礀猀 琀漀 挀爀攀愀琀攀 琀愀戀氀攀猀瀀愀挀攀猀 愀渀搀 漀戀樀攀挀琀猀 氀椀欀攀 琀愀戀氀攀猀⸀ 䤀渀 ㄀　最⼀㄀㄀最Ⰰ 愀 圀攀戀戀愀猀攀搀 最爀愀瀀栀椀挀愀氀 ∀䔀渀琀攀爀瀀爀椀猀攀 洀愀渀愀最攀爀∀㰀戀爀㸀ഀഀ
gives you almost complete control over the Instance and database. Here, we will just use a 'prompt-oriented" sqlplus utility:<br>਍㰀戀爀㸀ഀഀ
- Start a cmd prompt<br>਍ⴀ 琀礀瀀攀 ∀猀攀琀 伀刀䄀䌀䰀䔀开匀䤀䐀㴀匀䄀䰀䔀匀∀ ⠀眀椀琀栀漀甀琀 琀栀攀 ∀∀⤀⸀ 伀昀挀漀甀爀猀攀Ⰰ 椀昀 礀漀甀爀 搀愀琀愀戀愀猀攀 椀猀 渀愀洀攀搀 漀琀栀攀爀眀椀猀攀Ⰰ 琀栀攀渀 甀猀攀 琀栀愀琀 渀愀洀攀⸀㰀戀爀㸀ഀഀ
- Enter: sqlplus /nolog<br>਍ⴀ 䠀漀瀀攀昀甀氀氀礀Ⰰ 愀渀 ∀匀儀䰀㸀∀ 瀀爀漀洀瀀琀 愀瀀瀀攀愀爀猀⸀ 䤀昀 渀漀琀Ⰰ 琀栀攀 甀琀椀氀椀琀礀 椀猀 瀀爀漀戀愀戀氀礀 渀漀琀 椀渀 礀漀甀爀 ∀攀渀瘀椀爀漀渀洀攀渀琀∀ 漀爀 ∀瀀愀琀栀∀⸀㰀戀爀㸀ഀഀ
- Now, we need to "connect" (or logon) to the Instance. Try this:<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍匀儀䰀㸀 挀漀渀渀攀挀琀 猀礀猀⼀瀀愀猀猀眀漀爀搀䀀猀愀氀攀猀 愀猀 猀礀猀搀戀愀    㰀䤀㸀爀攀瀀氀愀挀攀 ∀瀀愀猀猀眀漀爀搀∀ 眀椀琀栀 琀栀攀 琀爀甀攀 瀀愀猀猀眀漀爀搀 漀昀 ∀猀礀猀∀㰀⼀䤀㸀㰀戀爀㸀ഀഀ
Connected.<br>਍匀儀䰀㸀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ
਍一漀琀攀㨀 䤀昀 琀栀攀 愀戀漀瘀攀 氀漀最漀渀 搀椀搀 渀漀琀 眀漀爀欀攀搀Ⰰ 琀爀礀㨀㰀戀爀㸀ഀഀ
SQL> connect / as sysdba<br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍一漀眀Ⰰ 樀甀猀琀 琀礀瀀攀 琀栀攀 昀漀氀氀漀眀椀渀最 挀漀洀洀愀渀搀猀 ⠀漀爀 甀猀攀 挀漀瀀礀⼀瀀愀猀琀攀⤀㨀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀匀倀䄀䌀䔀 匀䄀䰀䔀匀开䐀䄀吀䄀开㄀ 䐀䄀吀䄀䘀䤀䰀䔀 ✀䌀㨀尀伀刀䄀䐀䄀吀䄀尀匀䄀䰀䔀匀尀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀⸀䐀䈀䘀✀ 匀䤀娀䔀 㔀　䴀㰀戀爀㸀ഀഀ
EXTENT MANAGEMENT LOCAL AUTOALLOCATE<br>਍匀䔀䜀䴀䔀一吀 匀倀䄀䌀䔀 䴀䄀一䄀䜀䔀䴀䔀一吀 䄀唀吀伀㬀㰀戀爀㸀ഀഀ
<br>਍䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀匀倀䄀䌀䔀 匀䄀䰀䔀匀开䐀䄀吀䄀开㈀ 䐀䄀吀䄀䘀䤀䰀䔀 ✀䌀㨀尀伀刀䄀䐀䄀吀䄀尀匀䄀䰀䔀匀尀匀䄀䰀䔀匀开䐀䄀吀䄀开　㈀⸀䐀䈀䘀✀ 匀䤀娀䔀 㔀　䴀㰀戀爀㸀ഀഀ
EXTENT MANAGEMENT LOCAL AUTOALLOCATE<br>਍匀䔀䜀䴀䔀一吀 匀倀䄀䌀䔀 䴀䄀一䄀䜀䔀䴀䔀一吀 䄀唀吀伀㬀㰀戀爀㸀ഀഀ
<br>਍䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀匀倀䄀䌀䔀 匀䄀䰀䔀匀开䤀一䐀䔀堀开㄀ 䐀䄀吀䄀䘀䤀䰀䔀 ✀䌀㨀尀伀刀䄀䐀䄀吀䄀尀匀䄀䰀䔀匀尀匀䄀䰀䔀匀开䤀一䐀䔀堀开　㄀⸀䐀䈀䘀✀ 匀䤀娀䔀 㔀　䴀㰀戀爀㸀ഀഀ
EXTENT MANAGEMENT LOCAL AUTOALLOCATE<br>਍匀䔀䜀䴀䔀一吀 匀倀䄀䌀䔀 䴀䄀一䄀䜀䔀䴀䔀一吀 䄀唀吀伀㬀㰀戀爀㸀ഀഀ
<br>਍䌀刀䔀䄀吀䔀 唀匀䔀刀 吀䔀匀吀唀匀䔀刀 䤀䐀䔀一吀䤀䘀䤀䔀䐀 䈀夀 吀䔀匀吀唀匀䔀刀㰀戀爀㸀ഀഀ
DEFAULT TABLESPACE SALES_DATA_1<br>਍吀䔀䴀倀伀刀䄀刀夀 吀䄀䈀䰀䔀匀倀䄀䌀䔀 吀䔀䴀倀㬀㰀戀爀㸀ഀഀ
<br>਍䜀刀䄀一吀 䌀伀一一䔀䌀吀 吀伀 吀䔀匀吀唀匀䔀刀㬀㰀戀爀㸀ഀഀ
GRANT DBA TO TESTUSER; <br>਍㰀戀爀㸀ഀഀ
/* we will not bother about a surplus of "rights" to the "testuser" in this test database */<br>਍⼀⨀ 一漀眀Ⰰ 氀攀琀猀 猀攀攀 漀昀 漀甀爀 琀攀猀琀甀猀攀爀 挀愀渀 挀漀渀渀攀挀琀 琀漀 琀栀攀 搀愀琀愀戀愀猀攀㨀 ⨀⼀㰀戀爀㸀ഀഀ
<br>਍匀儀䰀㸀 挀漀渀渀攀挀琀 琀攀猀琀甀猀攀爀⼀琀攀猀琀甀猀攀爀䀀猀愀氀攀猀㰀戀爀㸀ഀഀ
Connected.<br>਍㰀戀爀㸀ഀഀ
SQL> select name from v$database;<br>਍ഀഀ
NAME<br>਍ⴀⴀⴀⴀⴀⴀⴀⴀⴀ㰀戀爀㸀ഀഀ
SALES<br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍匀漀Ⰰ 眀攀 栀愀瘀攀 愀 挀漀甀瀀氀攀 漀昀 琀愀戀氀攀猀瀀愀挀攀猀 愀渀搀 愀 甀猀攀爀 眀栀椀挀栀 挀愀渀 挀漀渀渀攀挀琀Ⰰ 愀渀搀 挀愀渀 眀栀漀 挀爀攀愀琀攀 漀戀樀攀挀琀猀 氀椀欀攀 琀愀戀氀攀猀 愀渀搀 椀渀搀攀砀攀猀⸀㰀戀爀㸀ഀഀ
<br>਍䰀攀琀✀猀 挀漀渀渀攀挀琀 愀猀 琀栀攀 琀攀猀琀甀猀攀爀 愀最愀椀渀Ⰰ 愀渀搀 挀爀攀愀琀攀 愀 昀攀眀 琀愀戀氀攀猀⸀ 一漀琀攀 琀栀愀琀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀㄀ 愀渀搀 䔀䴀倀䰀伀夀䔀䔀㈀ 琀愀戀氀攀猀Ⰰ㰀戀爀㸀ഀഀ
are created on different tablespaces.<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
CREATE TABLE EMPLOYEE1<br>਍⠀㰀戀爀㸀ഀഀ
EMP_ID     NUMBER(6)    NOT NULL,<br>਍䔀䴀倀开一䄀䴀䔀   嘀䄀刀䌀䠀䄀刀㈀⠀㈀　⤀ 一伀吀 一唀䰀䰀Ⰰ㰀戀爀㸀ഀഀ
SALARY     NUMBER(7,2)<br>਍⤀㰀戀爀㸀ഀഀ
TABLESPACE SALES_DATA_1;<br>਍㰀戀爀㸀ഀഀ
਍䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀 䔀䴀倀䰀伀夀䔀䔀㈀㰀戀爀㸀ഀഀ
(<br>਍䔀䴀倀开䤀䐀     一唀䴀䈀䔀刀⠀㘀⤀    一伀吀 一唀䰀䰀Ⰰ㰀戀爀㸀ഀഀ
EMP_NAME   VARCHAR2(20) NOT NULL,<br>਍匀䄀䰀䄀刀夀     一唀䴀䈀䔀刀⠀㜀Ⰰ㈀⤀㰀戀爀㸀ഀഀ
)<br>਍吀䄀䈀䰀䔀匀倀䄀䌀䔀 匀䄀䰀䔀匀开䐀䄀吀䄀开㈀㬀㰀戀爀㸀ഀഀ
<br>਍㰀戀爀㸀ഀഀ
/* Now, our testuser will insert 9999 records into the EMPLOYEE1 table:<br>਍㰀戀爀㸀ഀഀ
declare<br>਍  椀 渀甀洀戀攀爀 㨀㴀 ㄀㬀㰀戀爀㸀ഀഀ
begin<br>਍  眀栀椀氀攀 椀㰀㄀　　　　 氀漀漀瀀㰀戀爀㸀ഀഀ
       insert into EMPLOYEE1<br>਍        瘀愀氀甀攀猀 ⠀椀Ⰰ✀栀愀爀爀礀✀Ⰰ㈀㔀　　⤀㬀㰀戀爀㸀ഀഀ
<br>਍      椀 㨀㴀 椀 ⬀ ㄀㬀㰀戀爀㸀       ഀഀ
  end loop;<br>਍  挀漀洀洀椀琀㬀㰀戀爀㸀ഀഀ
end;<br>਍⼀㰀戀爀㸀ഀഀ
<br>਍⼀⨀ 一攀砀琀Ⰰ 挀栀攀挀欀 椀昀 眀攀 栀愀瘀攀 琀栀漀猀攀 爀攀挀漀爀搀猀 椀渀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀㄀ 琀愀戀氀攀㨀㰀戀爀㸀ഀഀ
<br>਍猀攀琀 氀椀渀攀猀椀稀攀 ㄀　　　㰀戀爀㸀ഀഀ
set pagesize 1000<br>਍㰀戀爀㸀ഀഀ
SELECT * FROM EMPLOYEE1<br>਍㰀戀爀㸀ഀഀ
/* This should return 9999 records. */<br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍ഀഀ
We are almost ready with setting up our SALES database. The following statement will very likely NOT WORK<br>਍戀攀挀愀甀猀攀 礀漀甀爀 搀愀琀愀戀愀猀攀 栀愀猀 愀 ∀氀漀挀愀氀氀礀 洀愀渀愀最攀搀∀ 猀礀猀琀攀洀 琀愀戀氀攀猀瀀愀挀攀⸀㰀戀爀㸀ഀഀ
You might try the next statement, but on 10g/11g it will almost certainly not work, because per default the SYSTEM tablespace is locally managed.<br>਍䤀昀 礀漀甀 最攀琀 㰀䤀㸀∀伀刀䄀ⴀ㄀㈀㤀㄀㌀㨀 䌀愀渀渀漀琀 挀爀攀愀琀攀 搀椀挀琀椀漀渀愀爀礀 洀愀渀愀最攀搀 琀愀戀氀攀猀瀀愀挀攀∀㰀⼀䤀㸀Ⰰ 琀栀攀爀攀 椀猀 渀漀琀栀椀渀最 琀漀 眀漀爀爀礀 愀戀漀甀琀⸀㰀戀爀㸀ഀഀ
Only in a few cases, I will refer to a dictionary managed tablespace.<br>਍䄀渀礀眀愀礀Ⰰ 䤀 眀椀氀氀 挀爀攀愀琀攀 愀 ✀琀栀椀爀搀✀ 搀愀琀愀 琀愀戀氀攀猀瀀愀挀攀Ⰰ 眀栀椀挀栀 椀猀 ∀搀椀挀琀椀漀渀愀爀礀 洀愀渀愀最攀搀∀⸀㰀戀爀㸀ഀഀ
Later, we will explain the difference between a locally and dictionary managed tablespace.<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
CREATE TABLESPACE SALES_DATA_3 DATAFILE 'C:\ORADATA\SALES\SALES_DATA_03.DBF' SIZE 50M<br>਍䔀堀吀䔀一吀 䴀䄀一䄀䜀䔀䴀䔀一吀 䐀䤀䌀吀䤀伀一䄀刀夀㰀戀爀㸀ഀഀ
default storage (<br>਍                  椀渀椀琀椀愀氀 㔀㄀㈀䬀㰀戀爀㸀ഀഀ
                  next    512K<br>਍                  洀椀渀攀砀琀攀渀琀猀 ㄀㰀戀爀㸀ഀഀ
                  pctincrease 0 )<br>਍洀椀渀椀洀甀洀 攀砀琀攀渀琀 㔀㄀㈀䬀㰀戀爀㸀ഀഀ
logging<br>਍漀渀氀椀渀攀㰀戀爀㸀ഀഀ
permanent;<br>਍㰀戀爀㸀ഀഀ
/* The upper statement very likely fails in your situation. Don't worry about it. */<br>਍㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
<br>਍㰀栀㌀㸀㄀⸀㈀ 䜀攀琀琀椀渀最 愀 䠀攀砀 攀搀椀琀漀爀 昀漀爀 圀椀渀搀漀眀猀㨀㰀⼀栀㌀㸀ഀഀ
਍一攀砀琀Ⰰ 琀漀 攀洀甀氀愀琀攀 猀攀瘀攀爀愀氀 挀爀愀猀栀攀猀Ⰰ 眀攀 渀攀攀搀 愀 㰀䤀㸀栀攀砀 攀搀椀琀漀爀㰀⼀䤀㸀Ⰰ 戀礀 眀栀椀挀栀 眀攀 挀愀渀 漀瀀攀渀 愀 搀愀琀愀戀愀猀攀 昀椀氀攀Ⰰ㰀戀爀㸀ഀഀ
and "corrupt" the file manually.<br>਍吀栀攀爀攀 愀爀攀 猀攀瘀攀爀愀氀 挀栀漀椀挀攀猀 栀攀爀攀⸀ 夀漀甀 洀椀最栀琀 挀漀渀猀椀搀攀爀 琀栀攀 昀爀攀攀 甀琀椀氀椀琀礀 堀嘀䤀㌀㈀Ⰰ 眀栀椀挀栀 礀漀甀 挀愀渀 搀漀眀渀氀漀愀搀 昀爀漀洀㰀戀爀㸀ഀഀ
the site below.<br> ਍㰀戀爀㸀ഀഀ
http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm<br>਍㰀戀爀㸀ഀഀ
You only have to download the zip file, and extract it. Then you are ready to run it.<br> ਍吀栀攀爀攀 攀砀椀猀琀猀 洀愀渀礀 愀氀琀攀爀渀愀琀椀瘀攀猀 琀漀漀⸀ 䨀甀猀琀 ∀最漀漀最氀攀∀ 漀渀 ∀栀攀砀 攀搀椀琀漀爀∀⸀㰀戀爀㸀ഀഀ
But I really recommend xvi32, due to its simplicity and capabilities.<br>਍㰀戀爀㸀ഀഀ
<br>਍㰀栀㈀㸀䌀栀愀瀀琀攀爀 ㈀⸀ 䔀砀愀洀瀀氀攀猀 漀昀 猀漀昀琀 愀渀搀 栀愀爀搀 戀氀漀挀欀 挀漀爀爀甀瀀琀椀漀渀⸀㰀⼀栀㈀㸀ഀഀ
਍㰀栀㌀㸀㈀⸀㄀⸀ 匀漀昀琀 戀氀漀挀欀 挀漀爀爀甀瀀琀椀漀渀⸀㰀⼀栀㌀㸀ഀഀ
਍伀甀爀 ∀琀攀猀琀甀猀攀爀∀ 椀渀猀攀爀琀攀搀 㤀㤀㤀㤀 爀攀挀漀爀搀猀 椀渀琀漀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀㄀ 琀愀戀氀攀⸀ 吀栀椀猀 琀愀戀氀攀 椀猀 氀漀挀愀琀攀搀 椀渀 琀栀攀 ∀匀䄀䰀䔀匀开䐀䄀吀䄀开㄀∀ 琀愀戀氀攀猀瀀愀挀攀Ⰰ㰀戀爀㸀ഀഀ
which corresponds to the file "c:\oradata\sales\sales_data_01.dbf".<br>਍一漀眀Ⰰ 猀愀礀 琀栀愀琀 琀栀攀 昀椀爀猀琀 挀漀甀瀀氀攀 漀昀 戀氀漀挀欀猀 ⠀攀愀挀栀 㠀㄀㤀㈀ 戀礀琀攀猀⤀ 愀爀攀 爀攀猀攀爀瘀攀搀 昀漀爀 猀礀猀琀攀洀 瀀甀爀瀀漀猀攀Ⰰ 琀栀攀渀 椀琀✀猀 昀愀椀爀 琀漀 ∀最愀洀戀氀攀∀ 琀栀愀琀 戀氀漀挀欀渀漀 ㈀　㰀戀爀㸀ഀഀ
is allocated to the EMPLOYEE table. Since 20x8192=163840 in decimal, that would be 28000 in Hexidecimal.<br>਍㰀戀爀㸀ഀഀ
Note: just use Windows calculator, switch to "scientific mode", type 8x8192. When the result has appeared, click the "Hex" radio control.<br>਍㰀戀爀㸀ഀഀ
Let's "edit" that file, while the database is closed. Startup "sqlplus", logon as "sys" (as sysdba) and shutdown the instance:<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍匀儀䰀㸀 猀栀甀琀搀漀眀渀 椀洀洀攀搀椀愀琀攀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
After a short while, the database is closed, meaning that no Oracle processes is accessing any database file,<br>਍愀渀搀 愀氀氀 瀀爀漀挀攀猀猀攀猀 愀爀攀 琀攀爀洀椀渀愀琀攀搀 ⠀琀栀攀 搀愀琀愀戀愀猀攀 昀椀氀攀猀 愀爀攀 ∀挀漀氀搀∀⤀⸀㰀戀爀㸀ഀഀ
<br>਍ⴀ 匀琀愀爀琀甀瀀 砀瘀椀㌀㈀⸀㰀戀爀㸀ഀഀ
- Open the file "c:\oradata\sales\sales_data_01.dbf".<br>਍ⴀ 一愀瘀椀最愀琀攀 ⠀猀挀爀漀氀氀⤀ 琀漀 栀攀砀 愀搀搀爀攀猀猀 ㈀㠀　　　⸀㰀戀爀㸀ഀഀ
<br>਍䤀 渀漀琀椀挀攀搀 ⠀椀渀 洀礀 挀愀猀攀⤀ 琀栀愀琀 漀渀 琀栀攀 ㄀㤀琀栀 戀氀漀挀欀 ⠀樀甀猀琀 ∀戀攀昀漀爀攀∀ ㈀㠀　　　⤀Ⰰ 琀栀攀爀攀 椀猀 愀 ∀栀愀爀爀礀∀ 氀椀猀琀攀搀⸀㰀戀爀㸀ഀഀ
Actually, this is the "last" harry in that block. Now, I navigated to that string,<br>਍愀渀搀 爀攀瀀氀愀挀攀搀 ∀栀愀爀爀礀∀ 眀椀琀栀 ∀䠀䔀一刀夀∀⸀㰀戀爀㸀ഀഀ
This is an example of a "soft" corruption. Physically, the block is sound, but the checksum of the block has changed.<br>਍㰀戀爀㸀ഀഀ
<img src="oraclecorr1.jpg" align="center"/>਍㰀戀爀㸀ഀഀ
Save the file, and close xvi32.<br>਍㰀戀爀㸀ഀഀ
Let's start the instance, and open the SALES database. Start sqlplus (note that you probably should set the "ORACLE_SID=sales" variable first).<br>਍愀渀搀 猀琀愀爀琀甀瀀 琀栀攀 椀渀猀琀愀渀挀攀㨀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
C:\> set ORACLE_SID=sales<br>਍䌀㨀尀㸀 猀焀氀瀀氀甀猀 ⼀渀漀氀漀最㰀戀爀㸀ഀഀ
SQL> connect / as sysdba<br>਍挀漀渀渀攀挀琀攀搀㰀戀爀㸀ഀഀ
SQL> startup<br>਍⸀⸀㰀戀爀㸀ഀഀ
SQL> connect testuser/testuser<br>਍挀漀渀渀攀挀琀攀搀㰀戀爀㸀ഀഀ
SQL> SELECT * FROM EMPLOYEE1;<br>਍㰀戀爀㸀ഀഀ
ERROR:<br>਍伀刀䄀ⴀ　㄀㔀㜀㠀㨀 伀刀䄀䌀䰀䔀 搀愀琀愀 戀氀漀挀欀 挀漀爀爀甀瀀琀攀搀 ⠀昀椀氀攀 ⌀ 㔀Ⰰ 戀氀漀挀欀 ⌀ ㄀㤀⤀㰀戀爀㸀ഀഀ
ORA-01110: data file 5: 'C:\ORADATA\SALES\SALES_DATA_01.DBF'<br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍伀欀Ⰰ 琀栀攀 戀氀漀挀欀 渀甀洀戀攀爀 椀猀 樀甀猀琀 眀栀愀琀 眀攀 攀砀瀀攀挀琀攀搀 椀琀 琀漀 戀攀⸀ 伀爀愀挀氀攀 搀椀猀挀漀瘀攀爀攀搀 愀 挀栀攀挀欀猀甀洀 洀椀猀洀愀琀挀栀 漀渀 琀栀愀琀 戀氀漀挀欀⸀㰀戀爀㸀ഀഀ
Actually, the block is just physically sound, but the "administration" of that block is not right.<br>਍㰀戀爀㸀ഀഀ
Dit you note that Oracle didn't complained at all at startup, about the corrupt block?<br>਍吀栀椀猀 椀猀 猀漀 戀攀挀愀甀猀攀 椀琀✀猀 樀甀猀琀 㰀䈀㸀愀渀 漀爀搀椀渀愀爀礀 搀愀琀愀戀氀漀挀欀㰀⼀䈀㸀⸀ 圀攀 挀愀渀✀琀 攀砀瀀攀挀琀 伀爀愀挀氀攀 琀漀 挀栀攀挀欀 攀瘀攀爀礀 戀氀漀挀欀 愀琀 戀漀漀琀琀椀洀攀⸀㰀戀爀㸀ഀഀ
Only when a process tries to access the block, the error is generated.<br>਍一漀琀攀㨀 伀昀挀漀甀爀猀攀 洀愀渀礀 ∀猀瀀攀挀椀愀氀∀ 戀氀漀挀欀猀 㰀䤀㸀愀爀攀㰀⼀䤀㸀 挀栀攀挀欀攀搀Ⰰ 猀甀挀栀 愀猀 琀栀攀 栀攀愀搀攀爀猀 漀昀 琀栀攀 搀愀琀愀戀愀猀攀 昀椀氀攀猀⸀㰀戀爀㸀ഀഀ
<br>਍㰀栀㌀㸀㈀⸀㈀⸀ 匀漀昀琀 挀漀爀爀甀瀀琀椀漀渀 漀昀 愀 䐀愀琀愀戀愀猀攀 戀氀漀挀欀Ⰰ 愀渀搀 琀栀攀 䐀䈀开䈀䰀伀䌀䬀开䌀䠀䔀䌀䬀䤀一䜀 椀渀椀琀⸀漀爀愀 瀀愀爀愀洀攀琀攀爀⸀㰀⼀栀㌀㸀ഀഀ
਍䤀昀 椀琀 最攀琀✀猀 挀氀攀愀爀 琀栀愀琀 愀 琀愀戀氀攀 栀愀猀 愀 挀漀爀爀甀瀀琀 戀氀漀挀欀Ⰰ 礀漀甀 洀椀最栀琀 琀栀椀渀欀 漀昀 愀 猀瀀攀挀椀昀椀挀 椀渀椀琀⸀漀爀愀 瀀愀爀愀洀攀琀攀爀㨀 䐀䈀开䈀䰀伀䌀䬀开䌀䠀䔀䌀䬀䤀一䜀⸀㰀戀爀㸀ഀഀ
As we will soon find out, it will not help us SELECTING a table with a soft corrupt block.<br>਍䄀渀礀眀愀礀Ⰰ 氀攀琀✀猀 琀攀猀琀 椀琀⸀㰀戀爀㸀ഀഀ
<br>਍吀栀攀 瀀愀爀愀洀攀琀攀爀 挀愀渀 琀愀欀攀 琀栀攀 瘀愀氀甀攀猀㨀㰀戀爀㸀ഀഀ
<br>਍䐀䈀开䈀䰀伀䌀䬀开䌀䠀䔀䌀䬀䤀一䜀 㴀 笀 䘀䄀䰀匀䔀 簀 伀䘀䘀 簀 䰀伀圀 簀 䴀䔀䐀䤀唀䴀 簀 吀刀唀䔀 簀 䘀唀䰀䰀 紀㰀戀爀㸀ഀഀ
<br>਍䄀猀 礀漀甀 挀愀渀 猀攀攀Ⰰ 礀漀甀 挀愀渀 瀀甀琀 琀栀攀 瀀愀爀愀洀攀琀攀爀 琀漀 䘀䄀䰀匀䔀⸀ 吀栀攀渀 一漀 戀氀漀挀欀 挀栀攀挀欀椀渀最 椀猀 瀀攀爀昀漀爀洀攀搀 昀漀爀 戀氀漀挀欀猀 椀渀 甀猀攀爀 琀愀戀氀攀猀瀀愀挀攀猀⸀㰀戀爀㸀ഀഀ
Although it is recommended to place it to "TRUE" for production systems, let's see what happens if we place it explicitly<br>਍琀漀 ∀䘀䄀䰀匀䔀∀ 昀漀爀 琀栀攀 匀䄀䰀䔀匀 搀愀琀愀戀愀猀攀⸀㰀戀爀㸀ഀഀ
I assume that you know that the spfile.ora (or init.ora) contains a number of "settings", that the instance will read<br>਍愀琀 猀琀愀爀琀甀瀀⸀ 夀漀甀 挀愀渀 攀搀椀琀 琀栀攀 ∀椀渀椀琀⸀漀爀愀∀ 昀椀氀攀 愀挀挀漀爀搀椀渀最氀礀Ⰰ 戀甀琀 洀漀猀琀 ㄀　最Ⰰ ㄀㄀最 猀礀猀琀攀洀猀 甀猀攀 琀栀攀 戀椀渀愀爀礀 ∀猀瀀昀椀氀攀⸀漀爀愀∀ 瘀愀爀椀愀渀琀Ⰰ㰀戀爀㸀ഀഀ
which you can alter using "ALTER SYSTEM SET <configparameter> = VALUE SCOPE=BOTH|MEMORY|SPFILE"<br>਍㰀戀爀㸀ഀഀ
So here we go:<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍匀儀䰀㸀 愀氀琀攀爀 猀礀猀琀攀洀 猀攀琀 䐀䈀开䈀䰀伀䌀䬀开䌀䠀䔀䌀䬀䤀一䜀㴀䘀䄀䰀匀䔀 匀䌀伀倀䔀㴀䈀伀吀䠀㬀㰀戀爀㸀ഀഀ
<br>਍匀礀猀琀攀洀 愀氀琀攀爀攀搀㰀戀爀㸀ഀഀ
<br>਍匀儀䰀㸀 猀栀甀琀搀漀眀渀 椀洀洀攀搀椀愀琀攀   ⼀⨀ 氀攀琀猀 搀漀 愀 爀攀猀琀愀爀琀 漀昀 琀栀攀 椀渀猀琀愀渀挀攀 愀渀礀眀愀礀 ⨀⼀ 㰀戀爀㸀ഀഀ
..<br>਍匀儀䰀㸀 猀琀愀爀琀甀瀀㰀戀爀㸀ഀഀ
..<br>਍匀儀䰀㸀 猀栀漀眀 瀀愀爀愀洀攀琀攀爀 䐀䈀开䈀䰀伀䌀䬀开䌀䠀䔀䌀䬀䤀一䜀㬀 ⼀⨀ 氀攀琀猀 挀栀攀挀欀 琀栀攀 挀甀爀爀攀渀琀 瘀愀氀甀攀 ⨀⼀ 㰀戀爀㸀ഀഀ
<br>਍一䄀䴀䔀                                 吀夀倀䔀        嘀䄀䰀唀䔀㰀戀爀㸀ഀഀ
------------------------------------ ----------- --------------------<br>਍搀戀开戀氀漀挀欀开挀栀攀挀欀椀渀最                    猀琀爀椀渀最      䘀䄀䰀匀䔀㰀戀爀㸀ഀഀ
SQL><br>਍⸀⸀㰀戀爀㸀ഀഀ
SQL> SELECT * FROM TESTUSER.EMPLOYEE1;<br>਍㰀戀爀㸀ഀഀ
ERROR:<br>਍伀刀䄀ⴀ　㄀㔀㜀㠀㨀 伀刀䄀䌀䰀䔀 搀愀琀愀 戀氀漀挀欀 挀漀爀爀甀瀀琀攀搀 ⠀昀椀氀攀 ⌀ 㔀Ⰰ 戀氀漀挀欀 ⌀ ㄀㤀⤀㰀戀爀㸀ഀഀ
ORA-01110: data file 5: 'C:\ORADATA\SALES\SALES_DATA_01.DBF'<br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍匀漀Ⰰ 瀀氀愀挀椀渀最 琀栀椀猀 瀀愀爀愀洀攀琀攀爀 琀漀 䘀䄀䰀匀䔀Ⰰ 搀漀攀猀 渀漀琀 栀攀氀瀀 椀渀 猀攀氀攀挀琀椀渀最 愀氀氀 爀漀眀猀 昀爀漀洀 愀 吀愀戀氀攀Ⰰ 椀昀 愀 戀氀漀挀欀 椀猀 氀漀最椀挀愀氀氀礀 挀漀爀爀甀瀀琀⸀㰀戀爀㸀ഀഀ
Actually DB_BLOCK_CHECKING main purpose is checking that the block in memory will be checked after it is written to disk,<br>਍眀栀椀挀栀 椀猀 愀 最漀漀搀 琀栀椀渀最⸀㰀戀爀㸀ഀഀ
But it's not an "easy escape" to be able to fully select a table while a block is corrupt.<br>਍㰀戀爀㸀ഀഀ
<br>਍㰀栀㌀㸀㈀⸀㌀ 䄀渀愀氀礀稀椀渀最 吀愀戀氀攀猀 愀渀搀 䘀椀氀攀猀 昀漀爀 挀漀爀爀甀瀀琀椀漀渀⸀㰀⼀栀㌀㸀ഀഀ
਍䈀攀昀漀爀攀 眀攀 爀攀瀀愀椀爀 愀渀礀琀栀椀渀最Ⰰ 氀攀琀✀猀 攀砀瀀氀漀爀攀 愀 昀攀眀 ∀眀攀氀氀 欀渀漀眀渀∀ 伀爀愀挀氀攀 猀愀渀椀琀礀 挀栀攀挀欀猀⸀㰀戀爀㸀ഀഀ
<br>਍㰀䈀㸀㄀⸀ 吀栀攀 ∀䄀一䄀䰀夀娀䔀 吀䄀䈀䰀䔀开一䄀䴀䔀 嘀䄀䰀䤀䐀䄀吀䔀 匀吀刀唀䌀吀唀刀䔀∀ 猀琀愀琀攀洀攀渀琀⸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
<br>਍䌀漀渀渀攀挀琀 愀猀 猀礀猀 ⠀猀礀猀搀戀愀⤀ 愀渀搀 琀爀礀 琀栀攀 愀渀愀氀礀稀攀 挀漀洀洀愀渀搀 漀渀 琀栀攀 吀䔀匀吀唀匀䔀刀⸀䔀䴀倀䰀伀夀䔀䔀㄀ 琀愀戀氀攀⸀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍匀儀䰀㸀 挀漀渀渀攀挀琀 ⼀ 愀猀 猀礀猀搀戀愀㰀戀爀㸀ഀഀ
Connected.<br>਍㰀戀爀㸀ഀഀ
SQL> ANALYZE TABLE TESTUSER.EMPLOYEE1 VALIDATE STRUCTURE;<br>਍愀渀愀氀礀稀攀 琀愀戀氀攀 吀䔀匀吀唀匀䔀刀⸀䔀䴀倀䰀伀夀䔀䔀㄀ 嘀䄀䰀䤀䐀䄀吀䔀 匀吀刀唀䌀吀唀刀䔀㰀戀爀㸀ഀഀ
*<br>਍䔀刀刀伀刀 愀琀 氀椀渀攀 ㄀㨀㰀戀爀㸀ഀഀ
ORA-01578: ORACLE data block corrupted (file # 5, block # 19)<br>਍伀刀䄀ⴀ　㄀㄀㄀　㨀 搀愀琀愀 昀椀氀攀 㔀㨀 ✀䌀㨀尀伀刀䄀䐀䄀吀䄀尀匀䄀䰀䔀匀尀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀⸀䐀䈀䘀✀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
Here, the "analyze" command comes up with exactly the same errormessage. You see? It could be a very usable tool<br>਍琀漀 挀栀攀挀欀 礀漀甀爀 琀愀戀氀攀猀 漀渀挀攀 椀渀 愀 眀栀椀氀攀⸀㰀戀爀㸀ഀഀ
<br>਍㰀䈀㸀㈀⸀ 吀栀攀 ∀搀戀瘀∀ 伀匀 瀀爀漀洀瀀琀 甀琀椀氀椀琀礀 ⸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
<br>਍吀栀攀 䄀一䄀䰀夀娀䔀 吀䄀䈀䰀䔀 猀琀愀琀攀洀攀渀琀 礀漀甀 甀猀攀 眀栀椀氀攀 礀漀甀 愀爀攀 ∀椀渀∀ 伀爀愀挀氀攀Ⰰ 琀栀愀琀 椀猀Ⰰ 礀漀甀 栀愀瘀攀 愀 猀焀氀瀀氀甀猀 猀攀猀猀椀漀渀 琀漀 琀栀攀 䐀愀琀愀戀愀猀攀Ⰰ㰀戀爀㸀ഀഀ
and you use that statement just as you would use any other SQL statement.<br>਍㰀戀爀㸀ഀഀ
A check from the Operating System is possible too. For this, Oracle has the "dbv" utility available.<br>਍䤀琀✀猀 瘀攀爀礀 攀愀猀礀 琀漀 甀猀攀⸀ 吀愀欀攀 愀 氀漀漀欀 愀琀 琀栀攀 昀漀氀氀漀眀椀渀最 攀砀愀洀瀀氀攀㨀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
C:\oradata\sales>dbv file=SALES_DATA_01.DBF blocksize=8192<br>਍㰀戀爀㸀ഀഀ
DBVERIFY - Verification starting : FILE = SALES_DATA_01.DBF<br>਍倀愀最攀 ㄀㤀 椀猀 洀愀爀欀攀搀 挀漀爀爀甀瀀琀㰀戀爀㸀ഀഀ
Corrupt block relative dba: 0x01400013 (file 5, block 19)<br>਍䈀愀搀 挀栀攀挀欀 瘀愀氀甀攀 昀漀甀渀搀 搀甀爀椀渀最 搀戀瘀㨀㰀戀爀㸀ഀഀ
Data in bad block:<br>਍ 琀礀瀀攀㨀 㘀 昀漀爀洀愀琀㨀 ㈀ 爀搀戀愀㨀 　砀　㄀㐀　　　㄀㌀㰀戀爀㸀ഀഀ
 last change scn: 0x0000.000c9749 seq: 0x1 flg: 0x06<br>਍ 猀瀀愀爀攀㄀㨀 　砀　 猀瀀愀爀攀㈀㨀 　砀　 猀瀀愀爀攀㌀㨀 　砀　㰀戀爀㸀ഀഀ
 consistency value in tail: 0x97490601<br>਍ 挀栀攀挀欀 瘀愀氀甀攀 椀渀 戀氀漀挀欀 栀攀愀搀攀爀㨀 　砀攀㌀戀㘀㰀戀爀㸀ഀഀ
 computed block checksum: 0x43c<br>਍㰀戀爀㸀ഀഀ
DBVERIFY - Verification complete<br>਍㰀戀爀㸀ഀഀ
Total Pages Examined         : 6400<br>਍吀漀琀愀氀 倀愀最攀猀 倀爀漀挀攀猀猀攀搀 ⠀䐀愀琀愀⤀ 㨀 ㈀㜀㰀戀爀㸀ഀഀ
Total Pages Failing   (Data) : 0<br>਍吀漀琀愀氀 倀愀最攀猀 倀爀漀挀攀猀猀攀搀 ⠀䤀渀搀攀砀⤀㨀 　㰀戀爀㸀ഀഀ
Total Pages Failing   (Index): 0<br>਍吀漀琀愀氀 倀愀最攀猀 倀爀漀挀攀猀猀攀搀 ⠀伀琀栀攀爀⤀㨀 ㄀㈀㰀戀爀㸀ഀഀ
Total Pages Processed (Seg)  : 0<br>਍吀漀琀愀氀 倀愀最攀猀 䘀愀椀氀椀渀最   ⠀匀攀最⤀  㨀 　㰀戀爀㸀ഀഀ
Total Pages Empty            : 6360<br>਍吀漀琀愀氀 倀愀最攀猀 䴀愀爀欀攀搀 䌀漀爀爀甀瀀琀   㨀 ㄀㰀戀爀㸀ഀഀ
Total Pages Influx           : 0<br>਍䠀椀最栀攀猀琀 戀氀漀挀欀 匀䌀一            㨀 㠀㈀㔀㄀㘀㄀ ⠀　⸀㠀㈀㔀㄀㘀㄀⤀㰀戀爀㸀ഀഀ
<br>਍ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍㰀䈀㸀㌀⸀ 匀漀洀攀 瘀␀ 瘀椀攀眀猀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
<br>਍吀栀攀 愀戀漀瘀攀 挀漀洀洀愀渀搀猀 ⠀䄀一䄀䰀夀娀䔀 吀䄀䈀䰀䔀 愀渀搀 琀栀攀 搀戀瘀 甀琀椀氀椀琀礀⤀ 愀爀攀 琀漀漀氀猀 戀礀 眀栀椀挀栀 礀漀甀 挀愀渀 挀栀攀挀欀 漀戀樀攀挀琀猀 㰀䤀㸀愀挀琀椀瘀攀氀礀㰀⼀䤀㸀⸀ ഀഀ
You issue the Statement or command, and the check is done right now.<br>਍㰀戀爀㸀ഀഀ
Some "v$" views will register corruption as well, like for example "V$DATABASE_BLOCK_CORRUPTION".<br>਍吀栀攀 漀渀氀礀 瀀爀漀戀氀攀洀 漀昀琀攀渀 猀攀攀渀 栀攀爀攀Ⰰ 椀猀 琀栀愀琀 琀栀漀猀攀 瘀椀攀眀猀 眀椀氀氀 漀渀氀礀 爀攀挀攀椀瘀攀 椀渀瀀甀琀 㰀䤀㸀椀昀 猀漀洀攀 伀爀愀挀氀攀 挀漀洀瀀漀渀攀渀琀㰀⼀䤀㸀㰀戀爀㸀ഀഀ
have found the corruption, and not by you (or a userprocess) issueing some statement.<br>਍䘀漀爀 攀砀愀洀瀀氀攀Ⰰ 椀昀 礀漀甀 眀漀甀氀搀 洀愀欀攀 刀䴀䄀一 戀愀挀欀甀瀀猀Ⰰ 愀渀搀 刀䴀䄀一 攀渀挀漀甀渀琀攀爀猀 愀 挀漀爀爀甀瀀琀 戀氀漀挀欀Ⰰ 琀栀攀渀 椀琀 眀椀氀氀 戀攀 ∀爀攀最椀猀琀攀爀攀搀∀Ⰰ㰀戀爀㸀ഀഀ
and from then on only, it will be visible in some of those v$ views.<br>਍㰀戀爀㸀ഀഀ
<br>਍䤀渀 琀栀椀猀 猀攀挀琀椀漀渀Ⰰ 眀攀 栀愀瘀攀 猀攀攀渀 栀漀眀 琀漀 猀漀昀琀 挀漀爀爀甀瀀琀 愀 搀愀琀愀戀愀猀攀 戀氀漀挀欀Ⰰ 愀渀搀 眀攀 栀愀瘀攀 猀攀攀渀 琀栀攀 椀渀昀愀洀漀甀猀攀 伀爀愀挀氀攀 攀爀爀漀爀㰀戀爀㸀ഀഀ
message "ORA-01578: ORACLE data block corrupted (file # n, block # m)"<br>਍䄀氀猀漀 眀攀 栀愀瘀攀 猀攀攀渀 琀眀漀 伀爀愀挀氀攀 洀攀琀栀漀搀猀 琀栀愀琀 攀渀愀戀氀攀猀 甀猀 琀漀 挀栀攀挀欀 琀愀戀氀攀猀 愀渀搀 昀椀氀攀猀 漀渀 挀漀爀爀甀瀀琀椀漀渀⸀㰀戀爀㸀ഀഀ
਍㰀戀爀㸀ഀഀ
<h3>2.4 Hard corruption of a Database block.</h3>਍ഀഀ
This time, we will truly destroy a few blocks, that is, fill a region with "all zero's".<br>਍圀攀 眀椀氀氀 猀琀愀爀琀 猀漀洀攀眀栀攀爀攀 椀渀 琀栀攀 洀椀搀搀氀攀 漀昀 愀 戀氀漀挀欀Ⰰ 琀栀攀渀 挀爀漀猀猀 愀 戀氀漀挀欀 戀漀甀渀搀愀爀礀Ⰰ 愀渀搀 挀漀渀琀椀渀甀攀㰀戀爀㸀ഀഀ
to write zero's all up to somewhere in the middle of that next block.<br>਍㰀戀爀㸀ഀഀ
First, we will start with a "fresh" situation. That is, we will drop the testuser (and all of his objects),<br>਍愀渀搀 琀栀攀渀 眀攀 眀椀氀氀 搀爀漀瀀 琀栀攀 琀愀戀氀攀猀瀀愀挀攀猀 ∀匀䄀䰀䔀匀开䐀䄀吀䄀开㄀∀ 愀渀搀 ∀匀䄀䰀䔀匀开䐀䄀吀䄀开㄀∀⸀㰀戀爀㸀ഀഀ
Then we will create those tablespaces again, create the testuser again, create the tables again, and we will<br>਍甀猀攀 琀栀攀 猀挀爀椀瀀琀 琀栀愀琀 椀渀猀攀爀琀猀 㤀㤀㤀㤀 椀渀琀漀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀㄀ 琀愀戀氀攀⸀㰀戀爀㸀ഀഀ
It's not much work, since we have all the neccessary statements in Chapter 1.<br>਍㰀戀爀㸀ഀഀ
Now you might argue, that dropping the user with "DROP USER TESTUSER CASCADE" would be enough.<br>਍䤀渀搀攀攀搀Ⰰ 䐀刀伀倀 唀匀䔀刀⸀⸀⸀ 䌀䄀匀䌀䄀䐀䔀 猀栀漀甀氀搀 搀爀漀瀀 琀栀攀 甀猀攀爀Ⰰ 眀椀琀栀 愀氀氀 椀琀✀猀 漀戀樀攀挀琀猀 椀渀挀氀甀搀攀搀⸀㰀戀爀㸀ഀഀ
Well, not exactly. This will be explained later.<br>਍匀漀Ⰰ 瀀氀攀愀猀攀 搀漀 琀栀攀 昀漀氀氀漀眀椀渀最㨀㰀戀爀㸀ഀഀ
<br>਍ⴀ 昀爀漀洀 琀栀攀 ∀䌀㨀尀㸀∀ 瀀爀漀洀瀀琀Ⰰ 猀攀琀 琀栀攀 伀刀䄀䌀䰀䔀开匀䤀䐀㴀猀愀氀攀猀  ⠀漀爀 眀栀愀琀攀瘀攀爀 渀愀洀攀 礀漀甀爀 搀愀琀愀戀愀猀攀 甀猀攀猀⤀㰀戀爀㸀ഀഀ
- Start sqlplus /nolog<br>਍ⴀ 挀漀渀渀攀挀琀 ⼀ 愀猀 猀礀猀搀戀愀 ⠀漀爀 甀猀攀 ∀挀漀渀渀攀挀琀 猀礀猀⼀瀀愀猀猀眀漀爀搀䀀猀愀氀攀猀 愀猀 猀礀猀搀戀愀∀⤀㰀戀爀㸀ഀഀ
then use statements as show below:<br>਍㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍匀儀䰀㸀 挀漀渀渀攀挀琀 ⼀ 愀猀 猀礀猀搀戀愀㰀戀爀㸀ഀഀ
Connected.<br>਍匀儀䰀㸀 䐀刀伀倀 唀匀䔀刀 吀䔀匀吀唀匀䔀刀 䌀䄀匀䌀䄀䐀䔀㬀㰀戀爀㸀ഀഀ
<br>਍唀猀攀爀 搀爀漀瀀瀀攀搀⸀㰀戀爀㸀ഀഀ
<br>਍匀儀䰀㸀 䐀刀伀倀 吀䄀䈀䰀䔀匀倀䄀䌀䔀 匀䄀䰀䔀匀开䐀䄀吀䄀开㄀ 䤀一䌀䰀唀䐀䤀一䜀 䌀伀一吀䔀一吀匀㬀㰀戀爀㸀ഀഀ
<br>਍吀愀戀氀攀猀瀀愀挀攀 搀爀漀瀀瀀攀搀⸀㰀戀爀㸀ഀഀ
<br>਍匀儀䰀㸀 䐀刀伀倀 吀䄀䈀䰀䔀匀倀䄀䌀䔀 匀䄀䰀䔀匀开䐀䄀吀䄀开㈀ 䤀一䌀䰀唀䐀䤀一䜀 䌀伀一吀䔀一吀匀㬀㰀戀爀㸀ഀഀ
<br>਍吀愀戀氀攀猀瀀愀挀攀 搀爀漀瀀瀀攀搀⸀㰀戀爀㸀ഀഀ
<br>਍一攀砀琀Ⰰ 搀攀氀攀琀攀 琀栀攀 瀀栀礀猀椀挀愀氀 昀椀氀攀猀 昀爀漀洀 琀栀攀 猀礀猀琀攀洀⸀ 䘀漀爀 琀栀愀琀Ⰰ 甀猀攀 攀砀瀀氀漀爀攀爀 漀爀 甀猀攀 琀栀攀 挀洀搀 瀀爀漀洀瀀琀㨀㰀戀爀㸀ഀഀ
<br>਍䌀㨀尀漀爀愀搀愀琀愀尀猀愀氀攀猀㸀 搀攀氀 猀愀氀攀猀开搀愀琀愀开⨀ 㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍一漀眀Ⰰ 甀猀椀渀最 琀栀攀 猀琀愀琀攀洀攀渀琀猀 漀昀 挀栀愀瀀琀攀爀 ㄀㨀㰀戀爀㸀 ഀഀ
- create those tablespaces (SALES_DATA_1 and SALES_DATA_2) again,<br> ਍ⴀ 挀爀攀愀琀攀 琀栀攀 琀攀猀琀甀猀攀爀 愀最愀椀渀Ⰰ㰀戀爀㸀ഀഀ
- create the tables again (as user TESTUSER), and insert 9999 records into the EMPLOYEE1 again.<br>਍㰀戀爀㸀ഀഀ
This is a fresh start !<br>਍㰀戀爀㸀ഀഀ
If you executed the above, again it's (oviously) "fair" to "assume" that blockno 20<br>਍椀猀 愀氀氀漀挀愀琀攀搀 琀漀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀㄀ 琀愀戀氀攀⸀㰀戀爀㸀ഀഀ
Again, since 20x8192=163840 in decimal, that would be 28000 in Hexidecimal.<br>਍㰀戀爀㸀ഀഀ
Now, let's shutdown the database, start xvi32, and open the "C:\ORADATA\SALES\SALES_DATA_01.DBF" databasefile.<br>਍㰀戀爀㸀ഀഀ
Scroll down to hex address 27EEE (the 19th block), start entering "zero's", all the way to for example<br>਍栀攀砀 愀搀搀爀攀猀猀 ㈀㠀㄀㜀㘀⸀ 䠀攀爀攀Ⰰ 眀攀 栀愀瘀攀 搀攀猀琀爀漀礀攀搀 琀栀攀 攀渀搀 漀昀 琀栀攀 ㄀㤀琀栀 戀氀漀挀欀Ⰰ 挀爀漀猀猀椀渀最 琀栀攀 戀漀甀渀搀愀爀礀Ⰰ愀渀搀 搀攀猀琀爀漀礀攀搀㰀戀爀㸀ഀഀ
the header of the 20th block. This is hard corruption.<br>਍㰀戀爀㸀ഀഀ
Save the file, and close xvi32.<br>਍㰀戀爀㸀ഀഀ
Let's start the instance, and open the SALES database. Start sqlplus and startup the instance:<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
C:\> set ORACLE_SID=sales<br>਍䌀㨀尀㸀 猀焀氀瀀氀甀猀 ⼀渀漀氀漀最㰀戀爀㸀ഀഀ
SQL> connect / as sysdba<br>਍挀漀渀渀攀挀琀攀搀㰀戀爀㸀ഀഀ
SQL> startup<br>਍⸀⸀㰀戀爀㸀ഀഀ
SQL> connect testuser/testuser<br>਍挀漀渀渀攀挀琀攀搀㰀戀爀㸀ഀഀ
SQL> SELECT * FROM EMPLOYEE1;<br>਍㰀戀爀㸀ഀഀ
ERROR:<br>਍伀刀䄀ⴀ　㄀㔀㜀㠀㨀 伀刀䄀䌀䰀䔀 搀愀琀愀 戀氀漀挀欀 挀漀爀爀甀瀀琀攀搀 ⠀昀椀氀攀 ⌀ 㔀Ⰰ 戀氀漀挀欀 ⌀ ㄀㤀⤀㰀戀爀㸀ഀഀ
ORA-01110: data file 5: 'C:\ORADATA\SALES\SALES_DATA_01.DBF'<br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍ഀഀ
Just as before, at the moment Oracle accesses the first corrupt block, it pulls out, throwing the same<br>਍昀愀洀椀氀椀愀爀 洀攀猀猀愀最攀 愀猀 椀渀 猀攀挀琀椀漀渀 ㈀⸀㄀⸀㰀戀爀㸀ഀഀ
Note that from the message alone, you cannot determine whether a block is soft- or hardcorrupted.<br>਍㰀戀爀㸀ഀഀ
Ofcourse, we can use the "ANALYZE TABLE" SQL Statement again, and we can try the "dbv" utility.<br>਍䰀攀琀✀猀 琀爀礀 琀栀攀 氀愀琀琀攀爀 漀渀攀⸀ 䔀砀椀琀 猀焀氀瀀氀甀猀 愀渀搀 漀瀀攀渀 愀 挀漀洀洀愀渀搀 挀洀搀 眀椀渀搀漀眀⸀㰀戀爀㸀ഀഀ
Navigate to the C:\ORADATA\SALES directory. Enter the familiar dbv command:<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
C:\oradata\sales> dbv file=SALES_DATA_01.DBF blocksize=8192<br>਍㰀戀爀㸀ഀഀ
DBVERIFY - Verification starting : FILE = SALES_DATA_01.DBF<br>਍㰀䈀㸀倀愀最攀 ㄀㤀 椀猀 椀渀昀氀甀砀 ⴀ 洀漀猀琀 氀椀欀攀氀礀 洀攀搀椀愀 挀漀爀爀甀瀀琀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
Corrupt block relative dba: 0x01400013 (file 5, block 19)<br>਍䘀爀愀挀琀甀爀攀搀 戀氀漀挀欀 昀漀甀渀搀 搀甀爀椀渀最 搀戀瘀㨀㰀戀爀㸀ഀഀ
Data in bad block:<br>਍ 琀礀瀀攀㨀 㘀 昀漀爀洀愀琀㨀 ㈀ 爀搀戀愀㨀 　砀　㄀㐀　　　㄀㌀㰀戀爀㸀ഀഀ
 last change scn: 0x0000.000cb030 seq: 0x1 flg: 0x06<br>਍ 猀瀀愀爀攀㄀㨀 　砀　 猀瀀愀爀攀㈀㨀 　砀　 猀瀀愀爀攀㌀㨀 　砀　㰀戀爀㸀ഀഀ
 consistency value in tail: 0x00000000<br>਍ 挀栀攀挀欀 瘀愀氀甀攀 椀渀 戀氀漀挀欀 栀攀愀搀攀爀㨀 　砀攀昀愀㔀㰀戀爀㸀ഀഀ
 computed block checksum: 0x9678<br>਍㰀戀爀㸀ഀഀ
<B>Page 20 is influx - most likely media corrupt</B><br>਍䌀漀爀爀甀瀀琀 戀氀漀挀欀 爀攀氀愀琀椀瘀攀 搀戀愀㨀 　砀　㄀㐀　　　㄀㐀 ⠀昀椀氀攀 㔀Ⰰ 戀氀漀挀欀 ㈀　⤀㰀戀爀㸀ഀഀ
Fractured block found during dbv:<br>਍䐀愀琀愀 椀渀 戀愀搀 戀氀漀挀欀㨀㰀戀爀㸀ഀഀ
 type: 0 format: 0 rdba: 0x00000000<br>਍ 氀愀猀琀 挀栀愀渀最攀 猀挀渀㨀 　砀　　　　⸀　　　　　　　　 猀攀焀㨀 　砀　 昀氀最㨀 　砀　　㰀戀爀㸀ഀഀ
 spare1: 0x0 spare2: 0x0 spare3: 0x0<br>਍ 挀漀渀猀椀猀琀攀渀挀礀 瘀愀氀甀攀 椀渀 琀愀椀氀㨀 　砀戀　㌀　　㘀　㄀㰀戀爀㸀ഀഀ
 check value in block header: 0x0<br>਍ 戀氀漀挀欀 挀栀攀挀欀猀甀洀 搀椀猀愀戀氀攀搀㰀戀爀㸀ഀഀ
<br>਍䐀䈀嘀䔀刀䤀䘀夀 ⴀ 嘀攀爀椀昀椀挀愀琀椀漀渀 挀漀洀瀀氀攀琀攀㰀戀爀㸀ഀഀ
<br>਍吀漀琀愀氀 倀愀最攀猀 䔀砀愀洀椀渀攀搀         㨀 㘀㐀　　㰀戀爀㸀ഀഀ
.. (some records omitted)<br>਍吀漀琀愀氀 倀愀最攀猀 䴀愀爀欀攀搀 䌀漀爀爀甀瀀琀   㨀 ㈀㰀戀爀㸀ഀഀ
Total Pages Influx           : 2<br>਍䠀椀最栀攀猀琀 戀氀漀挀欀 匀䌀一            㨀 㠀㌀㄀㔀㌀㘀 ⠀　⸀㠀㌀㄀㔀㌀㘀⤀㰀戀爀㸀ഀഀ
਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍㰀戀爀㸀ഀഀ
This time, the <B>dbv</B> utility informs you about the "hard" corruption. Note the "<B>Page 19 is influx - most likely media corrupt</B>" message.<br>਍䄀 猀椀洀椀氀愀爀 洀攀猀猀愀最攀 椀猀 猀栀漀眀渀 昀漀爀 戀氀漀挀欀 ㈀　⸀㰀戀爀㸀ഀഀ
This is very different from the dbv output as shown in section 2.1 (the soft corrupted block).<br>਍㰀戀爀㸀ഀഀ
<br>਍ഀഀ
<h2>Chapter 3. Repairing objects with corrupt blocks.</h2>਍ഀഀ
<h3>3.1. Repairing tables with the DBMS_REPAIR.SKIP_CORRUPT_BLOCKS() procedure.</h2>਍ഀഀ
This method, using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(), can be regarded as "the quick fix".<br>਍㰀戀爀㸀ഀഀ
The DBMS_REPAIR package, contains a number of subprograms that enable you to <B>detect</B>, or <B>skip</B>, or <B>repair</B> corrupt blocks<br>਍椀渀 琀愀戀氀攀猀 愀渀搀 椀渀搀攀砀攀猀⸀㰀戀爀㸀ഀഀ
At many occasions, you probably use the "skip" functionality, after you have seen that repair is not the "repair"<br>਍礀漀甀 瀀爀漀戀愀戀氀礀 栀漀瀀攀搀 椀琀 琀漀 戀攀⸀㰀戀爀㸀ഀഀ
<br>਍圀栀礀 眀栀漀甀氀搀 愀 爀攀瀀愀椀爀 渀漀琀 眀漀爀欀㼀 㰀戀爀㸀ഀഀ
<br>਍ⴀ 䤀渀 猀攀挀琀椀漀渀 ㈀⸀㄀Ⰰ 眀攀 栀愀瘀攀 猀攀攀渀 愀渀 攀砀愀洀瀀氀攀 漀昀 愀 ∀猀漀昀琀∀ 挀漀爀爀甀瀀琀椀漀渀⸀ 䠀攀爀攀Ⰰ 眀攀 爀攀瀀氀愀挀攀搀 愀 挀栀愀爀愀挀琀攀爀猀琀爀椀渀最 ⠀栀愀爀爀礀⤀㰀戀爀㸀ഀഀ
with another characterstring (HENRY) in the same place in that block. It only lead to a different<br>਍挀栀攀挀欀猀甀洀 漀昀 琀栀愀琀 戀氀漀挀欀⸀ 匀甀挀栀 猀漀昀琀 挀漀爀爀甀瀀琀椀漀渀猀 愀爀攀 椀渀搀攀攀搀 ⠀椀渀 瀀爀椀渀挀椀瀀氀攀⤀ 爀攀瀀愀椀爀愀戀氀攀Ⰰ 愀渀搀 琀栀甀猀 琀栀攀 戀氀漀挀欀⠀猀⤀ 洀椀最栀琀 戀攀 爀攀挀漀瘀攀爀攀搀⸀㰀戀爀㸀ഀഀ
<br>਍ⴀ 䤀渀 猀攀挀琀椀漀渀 ㈀⸀㐀Ⰰ 䤀 栀愀爀搀 挀漀爀爀甀瀀琀攀搀 愀 昀攀眀 戀氀漀挀欀猀Ⰰ 戀礀 瀀氀愀挀椀渀最 愀氀氀 稀攀爀漀✀猀 椀渀 琀栀攀 昀漀漀琀攀爀 漀昀 漀渀攀 戀氀漀挀欀Ⰰ 挀爀漀猀猀椀渀最 漀瘀攀爀㰀戀爀㸀ഀഀ
to the next block, and destroying that block's header and most of it's data.<br>਍吀栀愀琀 椀猀 渀漀琀 爀攀瀀愀椀爀愀戀氀攀Ⰰ 戀攀挀愀甀猀攀 琀栀攀 椀渀昀漀爀洀愀琀椀漀渀 椀猀 琀爀甀氀礀 氀漀猀琀⸀㰀戀爀㸀ഀഀ
Only an RMAN block restore could help us here, or possibly one of Oracle's fabulous FLASHBACK options.<br>਍㰀戀爀㸀ഀഀ
We will study two procedures from the package:<br>਍㰀戀爀㸀ഀഀ
(1): DBMS_REPAIR.SKIP_CORRUPT_BLOCKS() <br>਍⠀㈀⤀㨀 䐀䈀䴀匀开刀䔀倀䄀䤀刀⸀䘀䤀堀开䌀伀刀刀唀倀吀开䈀䰀伀䌀䬀匀⠀⤀ 㰀戀爀㸀ഀഀ
<br>਍倀攀爀猀漀渀愀氀氀礀Ⰰ 䤀 昀愀瘀漀甀爀 琀栀攀 ∀匀䬀䤀倀∀ 瀀爀漀挀攀搀甀爀攀 漀瘀攀爀 琀栀攀 ∀䘀䤀堀∀ 瀀爀漀挀攀搀甀爀攀⸀㰀戀爀㸀ഀഀ
਍䤀渀 挀愀猀攀 漀昀 ∀栀愀爀搀∀ 挀漀爀爀甀瀀琀椀漀渀 漀爀 ∀猀漀昀琀∀ 挀漀爀爀甀瀀琀椀漀渀Ⰰ 眀栀攀爀攀 漀渀攀 戀氀漀挀欀Ⰰ 漀爀 愀 昀攀眀 戀氀漀挀欀猀Ⰰ 愀爀攀 愀昀昀攀挀琀攀搀Ⰰ 礀漀甀 挀愀渀 攀渀猀甀爀攀㰀戀爀㸀ഀഀ
that Oracle <B>just skips them (!)</B>, so that the table can be properly accessed, and that will allow you to copy the data<br>਍漀瘀攀爀 琀漀 愀 渀攀眀 挀漀瀀礀 漀昀 琀栀愀琀 琀愀戀氀攀⸀㰀戀爀㸀ഀഀ
In "real life" circumstances, actually this is a very nice solution, that <B>indeed</B> will work.<br>਍㰀戀爀㸀ഀഀ
Remember, as you have seen in Chapter 2, if you query a table with a corrupt block, <B>it get's interrupted</B> when the<br>਍挀漀爀爀甀瀀琀 戀氀漀挀欀 椀猀 愀挀挀攀猀猀攀搀⸀ 匀漀Ⰰ 椀琀✀猀 搀椀昀昀椀挀甀氀琀 琀漀 洀愀欀攀 愀 昀甀氀氀 挀漀瀀礀 漀昀 琀栀攀 搀愀琀愀 漀昀 愀氀氀 ∀最漀漀搀∀ 戀氀漀挀欀猀⸀㰀戀爀㸀ഀഀ
<br>਍䤀昀 漀渀氀礀 漀渀攀 漀爀 琀眀漀 戀氀漀挀欀猀 愀爀攀 椀渀愀挀挀攀猀猀椀戀氀攀Ⰰ 甀猀甀愀氀氀礀 琀栀椀猀 爀攀瀀爀攀猀攀渀琀猀 樀甀猀琀 愀 琀椀渀礀 戀椀琀 漀昀 搀愀琀愀Ⰰ 挀漀洀瀀愀爀攀搀 琀漀 㰀䈀㸀愀氀氀㰀⼀䈀㸀 搀愀琀愀㰀戀爀㸀ഀഀ
that's stored in that table.<br>਍吀栀愀琀✀猀 眀栀礀 琀栀攀 ∀猀欀椀瀀 挀漀爀爀甀瀀琀攀搀∀ 瀀爀漀挀攀搀甀爀攀 椀猀 猀漀 瘀愀氀甀愀戀氀攀Ⰰ 椀渀 挀愀猀攀 漀昀 挀漀爀爀甀瀀琀攀搀 戀氀漀挀欀猀⸀ 吀栀椀猀 漀渀攀 眀漀爀欀猀 㰀䤀㸀爀攀愀氀氀礀㰀⼀䤀㸀 眀漀爀欀猀 ∀焀甀椀挀欀氀礀∀⸀㰀戀爀㸀ഀഀ
<br>਍䰀攀琀✀猀 琀爀礀 椀琀⸀㰀戀爀㸀ഀഀ
<br>਍ഀഀ
This procedure enables or disables the skipping of corrupt blocks during index and table scans of the specified object.<br>਍䤀渀 洀漀猀琀 漀挀挀愀猀椀漀渀猀Ⰰ 礀漀甀 眀愀渀琀 伀爀愀挀氀攀 ∀琀漀 猀欀椀瀀∀ 挀漀爀爀甀瀀琀 戀氀漀挀欀猀Ⰰ 猀漀 琀栀愀琀 焀甀攀爀椀攀猀 挀愀渀 瀀爀漀挀攀攀搀 眀椀琀栀漀甀琀 椀渀琀攀爀爀甀瀀琀椀漀渀猀⸀㰀戀爀㸀ഀഀ
In this section, we are going to "enable" the skipping of bad blocks.਍㰀戀爀㸀ഀഀ
Thus, what we are going to do here, is telling Oracle to skip over corrupt block, which will enable you to create<br>਍愀 ∀最漀漀搀∀ 琀愀戀氀攀Ⰰ 爀攀愀搀椀渀最 愀氀氀 最漀漀搀 戀氀漀挀欀猀Ⰰ 眀栀椀氀攀 伀一䰀夀 猀欀椀瀀瀀椀渀最 琀栀攀 戀愀搀 戀氀漀挀欀⠀猀⤀⸀㰀戀爀㸀ഀഀ
<br>਍吀栀攀 昀甀氀氀 猀礀渀琀愀砀 漀昀 琀栀攀 昀甀渀挀琀椀漀渀 椀猀 猀栀漀眀渀 戀攀氀漀眀⸀ 䤀琀 琀愀欀攀猀 㐀 椀渀瀀甀琀 瘀愀爀猀Ⰰ 漀昀 眀栀椀挀栀 琀栀攀 氀愀猀琀 ㈀ 挀愀渀 戀攀 氀攀昀琀 愀猀 琀栀攀 搀攀昀愀甀氀琀⸀㰀戀爀㸀ഀഀ
<br>਍攀砀攀挀 搀戀洀猀开爀攀瀀愀椀爀⸀猀欀椀瀀开挀漀爀爀甀瀀琀开戀氀漀挀欀猀⠀匀䌀䠀䔀䴀䄀开一䄀䴀䔀Ⰰ 伀䈀䨀䔀䌀吀开一䄀䴀䔀Ⰰ 伀䈀䨀䔀䌀吀开吀夀倀䔀Ⰰ 匀䬀䤀倀开䘀䰀䄀䜀⤀㬀㰀戀爀㸀ഀഀ
<br>਍䠀攀爀攀Ⰰ 琀栀攀 匀䌀䠀䔀䴀䄀开一䄀䴀䔀 挀愀渀 戀攀 爀攀愀搀 愀猀 琀栀攀 ∀漀眀渀攀爀∀ 漀昀 琀栀攀 漀戀樀攀挀琀 ⠀氀椀欀攀 琀攀猀琀甀猀攀爀Ⰰ 栀愀爀爀礀 攀琀挀⸀⸀⤀㰀戀爀㸀ഀഀ
Formally, a SCHEMA is not equal to what is understood to be an "owner", but it usually is.<br>਍吀栀攀 伀䈀䨀䔀䌀吀开一䄀䴀䔀Ⰰ 洀漀猀琀 漀昀琀攀渀 椀猀 琀栀攀 渀愀洀攀 漀昀 愀 琀愀戀氀攀Ⰰ 漀爀 愀渀 椀渀搀攀砀⸀㰀戀爀㸀ഀഀ
The OBJECT_TYPE, most often is "table" or "index". The default is "table".<br>਍吀栀攀 匀䬀䤀倀开䘀䰀䄀䜀 琀攀氀氀猀 伀爀愀挀氀攀 琀漀 猀欀椀瀀 漀爀 渀漀琀 琀漀 猀欀椀瀀Ⰰ 挀漀爀爀甀瀀琀 琀愀戀氀攀猀⸀ 吀栀攀 搀攀昀愀甀氀琀 椀猀 ∀猀欀椀瀀∀⸀㰀戀爀㸀ഀഀ
<br>਍刀攀洀攀洀戀攀爀 眀栀愀琀 栀愀瀀瀀攀渀攀搀 椀渀 猀攀挀琀椀漀渀 ㈀⸀㈀Ⰰ 眀栀攀爀攀 眀攀 栀愀瘀攀 琀栀攀 猀椀琀甀愀琀椀漀渀 琀栀愀琀 琀眀漀 戀氀漀挀欀猀 椀猀 琀栀攀 吀䔀匀吀唀匀䔀刀⸀䔀䴀倀䰀伀夀䔀䔀㄀ 琀愀戀氀攀Ⰰ 愀爀攀 栀愀爀搀 挀漀爀爀甀瀀琀攀搀㼀㰀戀爀㸀ഀഀ
When one would query that table, this happened:<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍䌀㨀尀㸀 猀攀琀 伀刀䄀䌀䰀䔀开匀䤀䐀㴀猀愀氀攀猀㰀戀爀㸀ഀഀ
C:\> sqlplus /nolog<br>਍匀儀䰀㸀 挀漀渀渀攀挀琀 ⼀ 愀猀 猀礀猀搀戀愀㰀戀爀㸀ഀഀ
connected<br>਍匀儀䰀㸀 猀琀愀爀琀甀瀀㰀戀爀㸀ഀഀ
..<br>਍匀儀䰀㸀 匀䔀䰀䔀䌀吀 ⨀ 䘀刀伀䴀 吀䔀匀吀唀匀䔀刀⸀䔀䴀倀䰀伀夀䔀䔀㄀㬀㰀戀爀㸀ഀഀ
<br>਍䔀刀刀伀刀㨀㰀戀爀㸀ഀഀ
ORA-01578: ORACLE data block corrupted (file # 5, block # 19)<br>਍伀刀䄀ⴀ　㄀㄀㄀　㨀 搀愀琀愀 昀椀氀攀 㔀㨀 ✀䌀㨀尀伀刀䄀䐀䄀吀䄀尀匀䄀䰀䔀匀尀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀⸀䐀䈀䘀✀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
Let's "fix" the table, using "dbms_repair.skip_corrupt_blocks":<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
SQL> connect / as sysdba<br>਍挀漀渀渀攀挀琀攀搀㰀戀爀㸀ഀഀ
<br>਍匀儀䰀㸀 攀砀攀挀 搀戀洀猀开爀攀瀀愀椀爀⸀猀欀椀瀀开挀漀爀爀甀瀀琀开戀氀漀挀欀猀⠀✀吀䔀匀吀唀匀䔀刀✀Ⰰ ✀䔀䴀倀䰀伀夀䔀䔀㄀✀⤀㬀㰀戀爀㸀ഀഀ
<br>਍倀䰀⼀匀儀䰀 瀀爀漀挀攀搀甀爀攀 猀甀挀挀攀猀猀昀甀氀氀礀 挀漀洀瀀氀攀琀攀搀⸀㰀戀爀㸀ഀഀ
<br>਍匀儀䰀㸀 匀䔀䰀䔀䌀吀 ⨀ 䘀刀伀䴀 吀䔀匀吀唀匀䔀刀⸀䔀䴀倀䰀伀夀䔀䔀㄀㬀㰀戀爀㸀ഀഀ
<br>਍㰀䤀㸀䄀氀氀 爀漀眀猀 昀爀漀洀 愀氀氀 最漀漀搀 戀氀漀挀欀猀 愀爀攀 猀栀漀眀渀⸀㰀⼀䤀㸀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
Don't forget the quotes around the SCHEMA_NAME and OBJECT_NAME (thus: 'TESTUSER', 'EMPLOYEE1')਍㰀戀爀㸀ഀഀ
The above action has made the EMPLOYEE1 table accesible again !<br>਍㰀戀爀㸀ഀഀ
Ofcourse, it is suggested that you now create a copy table with all good data (which usually will be "the bulk" of the data).<br>਍匀漀Ⰰ 椀渀 最攀渀攀爀愀氀Ⰰ 琀栀愀琀 眀漀甀氀搀 戀攀 甀猀椀渀最 愀 匀琀愀琀攀洀攀渀琀 氀椀欀攀㨀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀 猀愀氀瘀愀最攀开琀愀戀氀攀㰀戀爀㸀ഀഀ
AS SELECT * FROM corrupt_table;<br>਍㰀戀爀㸀 ഀഀ
<font face="arial" size=2 color="black">਍一漀眀 礀漀甀 栀愀瘀攀 猀愀瘀攀搀 琀栀攀 ∀戀甀氀欀∀ 漀昀 琀栀攀 琀愀戀氀攀爀漀眀猀Ⰰ 戀攀挀愀甀猀攀 甀猀甀愀氀氀礀 琀栀攀爀攀 椀猀 漀渀氀礀 漀渀攀 ⠀漀爀 愀琀 洀漀猀琀 愀 挀漀甀瀀氀攀⤀ 戀愀搀 戀氀漀挀欀猀⸀㰀戀爀㸀ഀഀ
So, making the table "alive" again, and copying the good rows to another table, is quite a reasonable solution.<br>਍伀昀挀漀甀爀猀攀Ⰰ 礀漀甀 眀椀氀氀 洀椀猀猀 愀 挀漀甀瀀氀攀 漀昀 爀漀眀猀 ⠀琀栀漀猀攀 琀栀愀琀 爀攀猀椀搀攀搀 漀渀 琀栀攀 戀愀搀 戀氀漀挀欀猀⤀⸀㰀戀爀㸀ഀഀ
<br>਍㰀䈀㸀䌀伀一䌀䰀唀匀䤀伀一㨀 㰀戀爀㸀ഀഀ
<br>਍䐀䈀䴀匀开刀䔀倀䄀䤀刀⸀匀䬀䤀倀开䌀伀刀刀唀倀吀开䈀䰀伀䌀䬀匀⠀匀䌀䠀䔀䴀䄀开一䄀䴀䔀Ⰰ吀䄀䈀䰀䔀开一䄀䴀䔀⤀㰀戀爀㸀ഀഀ
<br>਍椀猀 愀 瘀攀爀礀 爀攀愀猀漀渀愀戀氀攀 洀攀琀栀漀搀 琀漀 愀挀挀攀猀猀 愀 琀愀戀氀攀 眀椀琀栀 挀漀爀爀甀瀀琀 戀氀漀挀欀猀Ⰰ 愀渀搀 挀漀瀀礀 琀栀攀 挀漀渀琀攀渀琀 琀漀 愀 渀攀眀 琀愀戀氀攀⸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
<br>਍㰀氀椀㸀䈀甀琀 爀攀洀攀洀戀攀爀Ⰰ 琀栀椀猀 瀀爀漀挀攀搀甀爀攀 椀猀 㰀䈀㸀戀攀猀琀 琀漀 戀攀 甀猀攀搀㰀⼀䈀㸀 椀昀 礀漀甀 眀愀渀琀 琀漀 挀漀瀀礀 愀氀氀 ∀最漀漀搀∀ 爀漀眀猀 昀爀漀洀 琀栀攀 ∀戀愀搀∀ 琀愀戀氀攀Ⰰ 琀漀 愀 㰀䈀㸀渀攀眀㰀⼀䈀㸀 琀愀戀氀攀⸀㰀⼀氀椀㸀㰀戀爀㸀ഀഀ
<li>If you want <B>to keep using the current table</B>, it's better to use the method of section 3.2.</li><br>਍㰀戀爀㸀ഀഀ
<I>Note: If Oracle is skipping corrupt block(s), you are also able to export (using 'exp' or 'expdp') the table to a file.</I><br>਍㰀戀爀㸀ഀഀ
<br>਍㰀栀㌀㸀 ㌀⸀㈀ 吀栀攀 䐀䈀䴀匀开刀䔀倀䄀䤀刀⸀䘀䤀堀开䌀伀刀刀唀倀吀开䈀䰀伀䌀䬀匀⠀⤀ 瀀爀漀挀攀搀甀爀攀㨀㰀⼀栀㌀㸀ഀഀ
਍䤀昀 礀漀甀 愀爀攀 愀氀爀攀愀搀礀 挀漀渀琀攀渀琀 眀椀琀栀 琀栀攀 洀攀琀栀漀搀 漀昀 猀攀挀琀椀漀渀 ㌀⸀㄀Ⰰ 戀攀挀愀甀猀攀 椀琀✀猀 愀 洀攀琀栀漀搀 琀漀 猀愀瘀攀 琀栀攀 最漀漀搀 爀漀眀猀 漀昀 愀 琀愀戀氀攀Ⰰ㰀戀爀㸀ഀഀ
you may skip this section, and go on to section 3.3.<br>਍㰀戀爀㸀ഀഀ
In case of block corruption, we might consider using the DBMS_REPAIR.FIX_CORRUPT_BLOCK() procedure. Note the expression "FIX" in<br>਍琀栀攀 瀀爀漀挀攀搀甀爀攀 渀愀洀攀⸀㰀戀爀㸀ഀഀ
I am afraid that it will be a bit of a dissapointment, because you might have thought that it could repair soft corruption.<br>਍䤀渀 洀愀渀礀 挀椀爀挀甀洀猀琀愀渀挀攀猀Ⰰ 琀栀攀 爀攀猀甀氀琀 椀猀 琀栀愀琀 琀栀攀 瀀爀漀挀攀搀甀爀攀 眀椀氀氀 ∀洀愀爀欀∀ 漀爀 ∀昀氀愀最∀ 猀漀昀琀 漀爀 栀愀爀搀 挀漀爀爀甀瀀琀攀搀 戀氀漀挀欀⠀猀⤀Ⰰ㰀戀爀㸀 ഀഀ
and then consider it to be "repaired".<br>਍㰀戀爀㸀ഀഀ
In case of a hard corrupted block (as in section 2.2), data is truly gone (like we did in writing "all zero's").<br>਍匀漀Ⰰ 椀渀 愀氀氀 栀漀猀琀攀琀礀Ⰰ 眀攀 挀愀渀✀琀 攀砀瀀攀挀琀 伀爀愀挀氀攀 琀漀 爀攀瀀愀椀爀 琀栀愀琀 ⠀琀栀愀琀 椀猀Ⰰ 最攀琀 琀栀攀 爀漀眀猀 戀愀挀欀 琀栀愀琀 眀攀爀攀 漀渀 琀栀愀琀 戀氀漀挀欀⤀⸀㰀戀爀㸀ഀഀ
<br>਍䤀渀 挀愀猀攀 漀昀 愀 猀漀昀琀 挀漀爀爀甀瀀琀攀搀 戀氀漀挀欀 ⠀愀猀 椀渀 猀攀挀琀椀漀渀 ㈀⸀㄀⤀Ⰰ 椀渀 猀漀洀攀 挀椀爀挀甀洀猀琀愀渀挀攀猀Ⰰ 椀琀 眀漀甀氀搀 戀攀 㰀䈀㸀爀攀愀猀漀渀愀戀氀攀㰀⼀䈀㸀 琀漀 攀砀瀀攀挀琀Ⰰ 琀栀愀琀 琀栀攀 戀氀漀挀欀㰀戀爀㸀ഀഀ
can be repaired. For example, if only a checksum on a block is not right, it should be repairable.<br>਍唀猀椀渀最 琀栀攀 伀爀愀挀氀攀 䐀䈀䴀匀开刀䔀倀䄀䤀刀 瀀爀漀挀攀搀甀爀攀猀 愀氀氀漀渀攀Ⰰ 礀漀甀 眀漀渀✀琀 㰀䤀㸀爀攀愀氀氀礀㰀⼀䤀㸀 爀攀瀀愀椀爀 琀栀攀 戀氀漀挀欀⸀㰀戀爀㸀ഀഀ
But, in conjunction with the DUMP_ORPHAN_KEYS() and ADMIN_TABLES() procedures, you might get usefull info<br>਍愀戀漀甀琀 椀渀搀攀砀攀猀 琀栀愀琀 愀爀攀 愀昀昀攀挀琀攀搀Ⰰ 愀渀搀 昀爀攀攀氀椀猀琀猀 眀栀椀挀栀 猀栀漀甀氀搀 戀攀 爀攀戀甀椀氀搀⸀ 吀栀椀猀 洀椀最栀琀 栀攀氀瀀 椀渀 琀栀攀 漀瘀攀爀愀氀氀 瀀爀漀挀攀猀猀⸀㰀戀爀㸀ഀഀ
The idea then is, that you want to <I>continue</I> using that object, and thus additional procedures can be used<br>਍眀栀椀挀栀 栀攀氀瀀猀 礀漀甀 椀渀 爀攀愀氀椀稀椀渀最 琀栀愀琀 ⠀瀀爀漀瘀椀搀椀渀最 礀漀甀 椀渀昀漀 愀戀漀甀琀 椀渀搀攀砀攀猀Ⰰ 昀爀攀攀氀椀猀琀 攀琀挀⸀⸀⤀㰀戀爀㸀ഀഀ
<br>਍䈀甀琀 愀最愀椀渀Ⰰ 眀椀琀栀 爀攀猀瀀攀挀琀 琀漀 瀀甀爀攀氀礀 猀愀氀瘀愀最椀渀最 琀愀戀氀攀 搀愀琀愀Ⰰ 礀漀甀 眀漀渀✀琀 戀攀 戀攀琀琀攀爀 漀昀昀 挀漀洀瀀愀爀攀搀 琀漀 琀栀攀 洀攀琀栀漀搀 漀昀 猀攀挀琀椀漀渀 ㌀⸀㄀⸀㰀戀爀㸀ഀഀ
<br>਍吀栀攀 戀愀猀椀挀 椀搀攀愀 椀猀 琀栀椀猀㨀㰀戀爀㸀ഀഀ
<br>਍吀栀攀 瀀爀漀挀攀猀猀 爀攀焀甀椀爀攀猀 琀眀漀 愀搀洀椀渀椀猀琀爀愀琀椀瘀攀 琀愀戀氀攀猀 琀漀 栀漀氀搀 愀 氀椀猀琀 漀昀 㰀䤀㸀挀漀爀爀甀瀀琀 戀氀漀挀欀⠀猀⤀㰀⼀䤀㸀 愀渀搀 㰀䤀㸀椀渀搀攀砀 欀攀礀猀㰀⼀䤀㸀 瀀漀椀渀琀椀渀最㰀戀爀㸀ഀഀ
to those blocks. These are created as follows, using the DBMS_REPAIR.ADMIN_TABLES() sub procedure :<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
BEGIN<br>਍  㰀䈀㸀䐀戀洀猀开刀攀瀀愀椀爀⸀䄀搀洀椀渀开吀愀戀氀攀猀㰀⼀䈀㸀 ⠀㰀戀爀㸀ഀഀ
    table_name => 'REPAIR_TABLE',<br>਍    琀愀戀氀攀开琀礀瀀攀 㴀㸀 䐀戀洀猀开刀攀瀀愀椀爀⸀刀攀瀀愀椀爀开吀愀戀氀攀Ⰰ㰀戀爀㸀ഀഀ
    action => Dbms_Repair.Create_Action,<br>਍    琀愀戀氀攀猀瀀愀挀攀 㴀㸀 ✀匀䄀䰀䔀匀开䐀䄀吀䄀开㄀✀⤀㬀 㰀戀爀㸀ഀഀ
<br>਍  㰀䈀㸀䐀戀洀猀开刀攀瀀愀椀爀⸀䄀搀洀椀渀开吀愀戀氀攀猀㰀⼀䈀㸀 ⠀㰀戀爀㸀ഀഀ
    table_name => 'ORPHAN_KEY_TABLE',<br>਍    琀愀戀氀攀开琀礀瀀攀 㴀㸀 䐀戀洀猀开刀攀瀀愀椀爀⸀伀爀瀀栀愀渀开吀愀戀氀攀Ⰰ㰀戀爀㸀ഀഀ
    action => Dbms_Repair.Create_Action,<br>਍    琀愀戀氀攀猀瀀愀挀攀 㴀㸀 ✀匀䄀䰀䔀匀开䐀䄀吀䄀开㄀✀⤀㬀㰀戀爀㸀ഀഀ
END;<br>਍⼀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
The above statements only builds the admin tables.<br>਍㰀戀爀㸀ഀഀ
Now that the administration tables are built, we are able to check the table using the <B>DBMS_REPAIR.CHECK_OBJECT()</B> procedure.<br>਍吀栀椀猀 瀀爀漀挀攀搀甀爀攀 戀攀栀愀瘀攀猀 焀甀椀琀攀 氀椀欀攀 琀栀攀 ∀䄀一䄀䰀夀娀䔀 吀䄀䈀䰀䔀∀ 挀漀洀洀愀渀搀Ⰰ 琀漀 瀀愀爀猀攀 琀栀爀漀甀最栀 琀栀攀 眀栀漀氀攀 琀愀戀氀攀Ⰰ 挀栀攀挀欀椀渀最 攀愀挀栀 戀氀漀挀欀⸀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍匀䔀吀 匀䔀刀嘀䔀刀伀唀吀倀唀吀 伀一  ⼀⨀ 渀攀攀搀攀搀 昀漀爀 洀漀爀攀 漀甀琀瀀甀琀 漀渀 礀漀甀爀 猀挀爀攀攀渀 ⨀⼀ 㰀戀爀㸀ഀഀ
<br>਍䐀䔀䌀䰀䄀刀䔀 㰀戀爀㸀ഀഀ
  no_corrupt INT;<br>਍䈀䔀䜀䤀一㰀戀爀㸀ഀഀ
  no_corrupt := 0;<br>਍  㰀䈀㸀䐀戀洀猀开刀攀瀀愀椀爀⸀䌀栀攀挀欀开伀戀樀攀挀琀㰀⼀䈀㸀 ⠀㰀戀爀㸀ഀഀ
    schema_name => 'TESTUSER',<br>਍    漀戀樀攀挀琀开渀愀洀攀 㴀㸀 ✀䔀䴀倀䰀伀夀䔀䔀㄀✀Ⰰ㰀戀爀㸀ഀഀ
    repair_table_name => 'REPAIR_TABLE',<br>਍    挀漀爀爀甀瀀琀开挀漀甀渀琀 㴀㸀 渀漀开挀漀爀爀甀瀀琀⤀㬀㰀戀爀㸀ഀഀ
  Dbms_Output.Put_Line('number blocks corrupt: ' || TO_CHAR (no_corrupt));<br>਍䔀一䐀㬀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
<br>਍䄀昀琀攀爀 爀甀渀渀椀渀最 琀栀攀 愀戀漀瘀攀 猀琀愀琀攀洀攀渀琀Ⰰ 琀栀攀 挀甀爀爀甀瀀琀 戀氀漀挀欀⠀猀⤀ 栀愀瘀攀 戀攀攀渀 搀攀琀攀挀琀攀搀Ⰰ 戀甀琀 愀爀攀 渀漀琀 洀愀爀欀攀搀 愀猀 挀漀爀爀甀瀀琀 礀攀琀⸀㰀戀爀㸀 ഀഀ
Next, the <B>DBMS_REPAIR.FIX_CORRUPT_BLOCKS()</B> procedure can be used to mark the blocks as corrupt, allowing them<br> ਍琀漀 戀攀 猀欀椀瀀瀀攀搀 眀栀攀渀 焀甀攀爀椀攀猀 愀爀攀 爀甀渀⸀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍匀䔀吀 匀䔀刀嘀䔀刀伀唀吀倀唀吀 伀一㰀戀爀㸀ഀഀ
DECLARE<br>਍  瘀开昀椀砀攀搀 䤀一吀㬀㰀戀爀㸀ഀഀ
BEGIN <br>਍  瘀开昀椀砀攀搀 㨀㴀 　㬀㰀戀爀㸀ഀഀ
  <B>Dbms_Repair.Fix_Corrupt_Blocks</B> (<br>਍    猀挀栀攀洀愀开渀愀洀攀 㴀㸀 ✀吀䔀匀吀唀匀䔀刀✀Ⰰ㰀戀爀㸀ഀഀ
    object_name=> 'EMPLOYEE1',<br>਍    漀戀樀攀挀琀开琀礀瀀攀 㴀㸀 䐀戀洀猀开刀攀瀀愀椀爀⸀吀愀戀氀攀开伀戀樀攀挀琀Ⰰ㰀戀爀㸀ഀഀ
    repair_table_name => 'REPAIR_TABLE',<br>਍    昀椀砀开挀漀甀渀琀㴀㸀 瘀开昀椀砀攀搀⤀㬀㰀戀爀㸀ഀഀ
  Dbms_Output.Put_Line('number of blocks fixed: ' || to_char(v_fixed));<br>਍䔀一䐀㬀㰀戀爀㸀ഀഀ
/<br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍ഀഀ
਍䄀琀 琀栀椀猀 瀀栀愀猀攀Ⰰ 挀漀爀爀甀瀀琀 琀愀戀氀攀 戀氀漀挀欀猀 栀愀瘀攀 戀攀攀渀 氀漀挀愀琀攀搀Ⰰ 愀渀搀 愀氀氀 愀爀攀 ∀洀愀爀欀攀搀∀⸀ 一漀眀Ⰰ 椀昀 礀漀甀 眀漀甀氀搀 欀渀漀眀 琀栀愀琀㰀戀爀㸀ഀഀ
the table has indexes, you can check all index entries, searching for "orphaned keys", meaning <I>those</I> index entries<br>਍琀栀愀琀 眀攀爀攀 瀀漀椀渀琀椀渀最 琀漀 琀栀攀 氀漀猀琀 爀漀眀猀 漀渀 琀栀攀 戀愀搀 琀愀戀氀攀 戀氀漀挀欀猀⸀㰀戀爀㸀ഀഀ
So, this can be done using the <B>DBMS_REPAIR.DUMP_ORPHAN_KEYS()</B> procedure.<br>਍一漀眀Ⰰ 猀甀瀀瀀漀猀攀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀㄀ 琀愀戀氀攀 栀愀猀 愀渀 椀渀搀攀砀 挀愀氀氀攀搀 ∀䤀一䐀䔀堀开䔀䴀倀开一䄀䴀䔀∀Ⰰ 礀漀甀 挀漀甀氀搀 爀甀渀 愀 猀琀愀琀攀洀攀渀琀 愀猀 昀漀氀氀漀眀猀㨀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍匀䔀吀 匀䔀刀嘀䔀刀伀唀吀倀唀吀 伀一㰀戀爀㸀ഀഀ
DECLARE<br>਍  瘀开渀甀洀戀攀爀开漀爀瀀栀愀渀猀 䤀一吀㬀㰀戀爀㸀ഀഀ
BEGIN<br>਍  瘀开渀甀洀戀攀爀开漀爀瀀栀愀渀猀 㨀㴀 　㬀㰀戀爀㸀ഀഀ
  <B>Dbms_Repair.Dump_Orphan_Keys</B> (<br>਍    猀挀栀攀洀愀开渀愀洀攀 㴀㸀 ✀吀䔀匀吀唀匀䔀刀✀Ⰰ㰀戀爀㸀ഀഀ
    object_name => 'INDEX_EMP_NAME',<br>਍    漀戀樀攀挀琀开琀礀瀀攀 㴀㸀 䐀戀洀猀开刀攀瀀愀椀爀⸀䤀渀搀攀砀开伀戀樀攀挀琀Ⰰ㰀戀爀㸀ഀഀ
    repair_table_name => 'REPAIR_TABLE',<br>਍    漀爀瀀栀愀渀开琀愀戀氀攀开渀愀洀攀㴀㸀 ✀伀刀倀䠀䄀一开䬀䔀夀开吀䄀䈀䰀䔀✀Ⰰ㰀戀爀㸀ഀഀ
    key_count => v_number_orphans);<br>਍  䐀戀洀猀开伀甀琀瀀甀琀⸀倀甀琀开䰀椀渀攀⠀✀漀爀瀀栀愀渀 欀攀礀 挀漀甀渀琀㨀 ✀ 簀簀 琀漀开挀栀愀爀⠀瘀开渀甀洀戀攀爀开漀爀瀀栀愀渀猀⤀⤀㬀㰀戀爀㸀ഀഀ
END;<br>਍⼀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍伀昀挀漀甀爀猀攀Ⰰ 猀椀渀挀攀 眀攀 栀愀瘀攀 琀栀攀 䄀搀洀椀渀 琀愀戀氀攀猀 ∀刀䔀倀䄀䤀刀开吀䄀䈀䰀䔀∀ 愀渀搀 ∀伀刀倀䠀䄀一开䬀䔀夀开吀䄀䈀䰀䔀∀Ⰰ 椀琀 椀猀 瘀攀爀礀 椀渀猀琀爀甀挀琀椀瘀攀㰀戀爀㸀ഀഀ
to view their contents.<br>਍㰀栀㌀㸀䔀砀挀攀爀挀椀猀攀㨀㰀⼀栀㌀㸀ഀഀ
਍一漀眀Ⰰ 栀攀爀攀 椀猀 愀渀 攀砀攀爀挀椀猀攀㨀 礀漀甀 洀椀最栀琀 琀爀礀 琀栀攀 愀戀漀瘀攀 搀攀猀挀爀椀戀攀搀 瀀爀漀挀攀搀甀爀攀 礀漀甀爀猀攀氀昀 ℀㰀戀爀㸀ഀഀ
<br>਍䄀最愀椀渀Ⰰ 礀漀甀 猀栀漀甀氀搀 猀琀愀爀琀 眀椀琀栀 愀 ∀昀爀攀猀栀∀ 猀椀琀甀琀愀琀椀漀渀⸀ 吀栀攀渀 礀漀甀 眀椀氀氀 挀爀攀愀琀攀 ⠀愀最愀椀渀⤀ 愀 猀漀昀琀 挀漀爀爀甀瀀琀椀漀渀 椀渀 琀栀攀 ㄀㤀琀栀 戀氀漀挀欀㰀戀爀㸀ഀഀ
in the EMPLOYEE1 table, exactly as is described in section 2.1.<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
SQL> connect / as sysdba<br>਍䌀漀渀渀攀挀琀攀搀⸀㰀戀爀㸀ഀഀ
SQL> DROP USER TESTUSER CASCADE;<br>਍㰀戀爀㸀ഀഀ
User dropped.<br>਍㰀戀爀㸀ഀഀ
SQL> DROP TABLESPACE SALES_DATA_1 INCLUDING CONTENTS;<br>਍㰀戀爀㸀ഀഀ
Tablespace dropped.<br>਍㰀戀爀㸀ഀഀ
SQL> DROP TABLESPACE SALES_DATA_2 INCLUDING CONTENTS;<br>਍㰀戀爀㸀ഀഀ
Tablespace dropped.<br>਍㰀戀爀㸀ഀഀ
Next, delete the physical files from the system. For that, use explorer or use the cmd prompt:<br>਍㰀戀爀㸀ഀഀ
C:\oradata\sales> del sales_data_* <br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍ഀഀ
Now, using the statements of chapter 1:<br> ਍ⴀ 挀爀攀愀琀攀 琀栀漀猀攀 琀愀戀氀攀猀瀀愀挀攀猀 ⠀匀䄀䰀䔀匀开䐀䄀吀䄀开㄀ 愀渀搀 匀䄀䰀䔀匀开䐀䄀吀䄀开㈀⤀ 愀最愀椀渀Ⰰ㰀戀爀㸀 ഀഀ
- create the testuser again,<br>਍ⴀ 挀爀攀愀琀攀 琀栀攀 琀愀戀氀攀猀 愀最愀椀渀 ⠀愀猀 甀猀攀爀 吀䔀匀吀唀匀䔀刀⤀Ⰰ 愀渀搀 椀渀猀攀爀琀 㤀㤀㤀㤀 爀攀挀漀爀搀猀 椀渀琀漀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀㄀ 愀最愀椀渀⸀㰀戀爀㸀ഀഀ
- Also create an index on the EMPLOYEE1 table called "INDEX_EMP_NAME", using:<br>਍ 䌀刀䔀䄀吀䔀 䤀一䐀䔀堀 䤀一䐀䔀堀开䔀䴀倀开一䄀䴀䔀 伀一 䔀䴀倀䰀伀夀䔀䔀㄀⠀䔀䴀倀开一䄀䴀䔀⤀ 吀䄀䈀䰀䔀匀倀䄀䌀䔀 匀䄀䰀䔀匀开䤀一䐀䔀堀开㄀⸀㰀戀爀㸀ഀഀ
<br>਍吀栀攀渀Ⰰ 挀漀渀渀攀挀琀 愀猀 猀礀猀 ⠀猀礀猀搀戀愀⤀ 愀渀搀 猀栀甀琀搀漀眀渀 琀栀攀 搀愀琀愀戀愀猀攀⸀ 一漀眀Ⰰ 愀猀 搀攀猀挀爀椀戀攀搀 椀渀 猀攀挀琀椀漀渀 ㈀⸀㄀Ⰰ 猀琀愀爀琀 砀瘀椀㌀㈀Ⰰ㰀戀爀㸀ഀഀ
open the "c:\oradata\sales\sales_data_01.dbf" file, navigate to the 19th block (just "before" hex 28000),<br>਍愀渀搀 挀栀愀渀最攀 漀渀攀 漀昀 琀栀攀 ∀氀愀猀琀∀ 栀愀爀爀礀✀猀 椀渀琀漀 䠀䔀一刀夀⸀ 吀栀攀渀 猀愀瘀攀 琀栀攀 昀椀氀攀 愀渀搀 挀氀漀猀攀 砀瘀椀㌀㈀⸀㰀戀爀㸀ഀഀ
Next, startup the database.<br>਍㰀戀爀㸀ഀഀ
Now, if you want, execute the various DBMS_REPAIR subprocedures, as described above, yourself <br>਍㰀戀爀㸀ഀഀ
<br>਍㰀栀㌀㸀㌀⸀㌀ 吀栀攀 伀刀䄀ⴀ　㄀㔀㜀㠀 愀渀搀 伀刀䄀ⴀ　㠀㄀　㌀ 攀爀爀漀爀猀⸀㰀⼀栀㈀㸀ഀഀ
਍㰀䈀㸀㰀唀㸀伀刀䄀ⴀ　㄀㔀㜀㠀㨀㰀⼀䈀㸀㰀⼀唀㸀㰀戀爀㸀ഀഀ
<br>਍䈀氀漀挀欀 挀漀爀爀甀瀀琀椀漀渀 爀攀愀氀氀礀 眀椀氀氀 渀漀琀 漀挀挀甀爀 ∀漀昀琀攀渀∀⸀ 䈀甀琀 眀栀攀渀 椀琀 㰀䤀㸀搀漀攀猀㰀⼀䤀㸀 栀愀瀀瀀攀渀Ⰰ 琀栀攀渀 礀漀甀 眀椀氀氀 猀攀攀㰀戀爀㸀ഀഀ
the well-known ORA-01578 (or ORA-1578) error message.<br>਍䤀渀 愀氀氀 琀栀攀 攀砀愀洀瀀氀攀猀 愀戀漀瘀攀Ⰰ 眀攀 椀渀搀攀攀搀 栀愀瘀攀 猀攀攀渀 琀栀椀猀 瀀愀爀琀椀挀甀氀愀爀 洀攀猀猀愀最攀⸀㰀戀爀㸀ഀഀ
<br>਍㰀䈀㸀㰀唀㸀伀刀䄀ⴀ　㠀㄀　㌀㨀㰀⼀䈀㸀㰀⼀唀㸀㰀戀爀㸀ഀഀ
<br>਍唀猀甀愀氀氀礀Ⰰ 琀栀攀 洀攀猀猀愀最攀 椀猀 渀漀琀 猀漀 猀栀漀挀欀椀渀最Ⰰ 戀攀挀愀甀猀攀 椀琀 洀攀爀攀氀礀 洀攀愀渀猀 琀栀椀猀㨀 㰀䤀㸀伀刀䄀ⴀ　㠀㄀　㌀㨀 漀戀樀攀挀琀 渀漀 氀漀渀最攀爀 攀砀椀猀琀猀⸀㰀⼀䤀㸀㰀戀爀㸀ഀഀ
<br>਍䤀渀 最攀渀攀爀愀氀Ⰰ 眀攀 挀愀渀 搀椀猀琀椀渀最甀椀猀栀 琀栀爀攀攀 挀愀猀攀猀㨀㰀戀爀㸀ഀഀ
<br>਍⠀㄀⤀㨀㰀戀爀㸀ഀഀ
An object was indeed missing. Such an event could for example happen if some process tries to access the object,<br>਍眀栀椀氀攀 愀渀漀琀栀攀爀 瀀爀漀挀攀猀猀 昀漀爀 攀砀愀洀瀀氀攀 搀爀漀瀀瀀攀搀 漀爀 洀漀瘀攀搀 椀琀⸀㰀戀爀㸀ഀഀ
It does not have to be interactive processes. It could be batch processes where one process want to perform<br>਍愀 搀爀漀瀀 愀渀搀 爀攀ⴀ挀爀攀愀琀攀 ⠀漀昀 愀 琀愀戀氀攀Ⰰ 漀爀 椀渀搀攀砀 攀琀挀⸀⸀⤀Ⰰ 眀栀椀氀攀 愀渀漀琀栀攀爀 瀀爀漀挀攀猀猀 椀猀 渀漀琀 ✀愀眀愀爀攀✀ 漀昀 琀栀愀琀 昀愀挀琀⸀㰀戀爀㸀ഀഀ
So, in this case, the message is just stating reality: the object does not exist (for some process, at some time),<br>਍愀渀搀 琀栀甀猀 椀猀 渀漀琀 愀氀愀爀洀椀渀最 ⠀攀砀挀攀瀀琀 昀漀爀 琀栀攀 昀愀挀琀 琀栀愀琀 琀栀攀 瀀爀漀最爀愀洀洀椀渀最 氀漀最椀挀 椀猀 渀漀琀 爀椀最栀琀⤀⸀㰀戀爀㸀ഀഀ
<br>਍⠀㈀⤀㨀㰀戀爀㸀ഀഀ
Over several Oracle versions, a few bugs raised this message too, effectively generating a "false" alarm.<br>਍䘀漀爀 攀砀愀洀瀀氀攀㨀㰀戀爀㸀ഀഀ
- PQ may signal a false ORA-8103 message under a high load.<br> ਍ⴀ 伀刀䄀ⴀ㠀㄀　㌀⼀伀刀䄀ⴀ㄀㐀㄀　 昀爀漀洀 挀漀渀挀甀爀爀攀渀琀 䤀一匀䔀刀吀 ⼀ 攀砀瀀漀爀琀 漀渀 䄀匀匀䴀 琀愀戀氀攀猀㰀戀爀㸀ഀഀ
਍㰀戀爀㸀ഀഀ
(3):<br>਍一漀琀 眀椀琀栀猀琀愀渀搀椀渀最 琀栀攀 愀戀漀瘀攀 昀愀挀琀猀Ⰰ 眀攀 猀栀漀甀氀搀 愀氀眀愀礀猀 琀愀欀攀 琀栀椀猀 洀攀猀猀愀最攀 瘀攀爀礀 猀攀爀椀漀甀猀氀礀⸀㰀戀爀㸀ഀഀ
If you see it (for example, a user reports it, or you find it in a log), and you know for sure that the object exists,<br>਍琀栀攀渀 礀漀甀 洀椀最栀琀 栀愀瘀攀 爀甀渀 椀渀琀漀 愀 ∀戀甀最∀⸀ 伀爀Ⰰ 椀渀搀攀攀搀 猀漀洀攀 昀漀爀洀 漀昀 氀漀最椀挀愀氀 挀漀爀爀甀瀀琀椀漀渀 椀猀 戀漀琀栀攀爀椀渀最 礀漀甀爀 猀礀猀琀攀洀⸀㰀戀爀㸀ഀഀ
<br>਍䤀昀 礀漀甀 猀攀攀 琀栀攀 洀攀猀猀愀最攀 椀渀 瀀爀漀搀甀挀琀椀漀渀Ⰰ 愀渀搀 渀漀 漀戀瘀椀漀甀猀 爀攀愀猀漀渀 挀愀渀 戀攀 昀漀甀渀搀 ⠀氀椀欀攀 琀栀愀琀 猀漀洀攀 戀愀琀挀栀 椀渀搀攀攀搀 爀攀ⴀ挀爀攀愀琀攀猀 琀栀攀 漀戀樀攀挀琀Ⰰ㰀戀爀㸀ഀഀ
at the time that another process tries to access it), and you cannot find any filed bug report, I think it needs immediate attention.<br>਍㰀戀爀㸀ഀഀ
I consider this message more serious than the ordinary "block-corrupt" errormessage (which is ORA-01578).<br>਍吀栀愀琀 椀猀Ⰰ 㰀䤀㸀椀昀 礀漀甀 挀愀渀渀漀琀 攀砀瀀氀愀椀渀 椀琀⸀㰀⼀䤀㸀㰀戀爀㸀ഀഀ
For production systems, I think you should immediately contact Oracle Support.<br>਍㰀戀爀㸀ഀഀ
As for other options:<br>਍夀漀甀 挀漀甀氀搀 琀爀礀 琀漀 爀攀ⴀ挀爀攀愀琀攀 琀栀攀 漀戀樀攀挀琀⸀ 䤀 栀愀瘀攀 猀攀攀渀 洀礀猀攀氀昀 琀栀愀琀 愀 ∀ 匀䔀䰀䔀䌀吀 ⨀ ∀ 猀漀洀攀琀椀洀攀猀 猀琀椀氀氀 眀漀爀欀猀Ⰰ 眀栀椀氀攀 漀琀栀攀爀 漀瀀攀爀愀琀椀漀渀猀 昀愀椀氀⸀㰀戀爀㸀ഀഀ
<br>਍㰀戀爀㸀ഀഀ
<h3>3.4 Using the SET EVENTs parameter.</h2>਍ഀഀ
Again, we return to the usual corrupt block message, which is ORA-01578.<br>਍吀栀攀爀攀 椀猀 愀渀漀琀栀攀爀 ∀焀甀椀挀欀 昀椀砀∀Ⰰ 眀栀椀挀栀 洀椀最栀琀 戀攀 椀洀瀀漀爀琀愀渀琀 椀渀 猀漀洀攀 挀愀猀攀猀⸀㰀戀爀㸀ഀഀ
<br>਍伀爀愀挀氀攀 瀀爀漀瘀椀搀攀猀 洀愀渀礀 搀椀愀最渀漀猀琀椀挀 琀漀漀氀猀 昀漀爀 搀攀戀甀最最椀渀最 琀栀攀 刀䐀䈀䴀匀⸀ 伀渀攀 ∀琀爀愀搀椀琀椀漀渀愀氀∀ 洀攀琀栀漀搀猀 椀猀Ⰰ 琀栀愀琀 挀攀爀琀愀椀渀 琀爀愀挀椀渀最 漀瀀琀椀漀渀猀Ⰰ㰀戀爀㸀ഀഀ
or a certain type of "behaviour" of the Instance, can be turned on using the SET EVENTS statement. <br>਍䤀渀 洀愀渀礀 挀愀猀攀猀 琀栀椀猀 眀椀氀氀 爀攀猀甀氀琀 琀栀愀琀 ∀搀椀愀最渀漀猀琀椀挀 椀渀昀漀爀洀愀琀椀漀渀∀ ⠀昀漀挀甀猀攀搀 漀渀 愀 挀攀爀琀愀椀渀 攀瘀攀渀琀⤀ 最攀琀猀 搀甀洀瀀攀搀 琀漀 琀爀愀挀攀 昀椀氀攀猀⸀㰀戀爀㸀ഀഀ
Or, some other "SET EVENTS" commands, will just change the behaviour of the RDBMS in a certain way.<br>਍㰀戀爀㸀ഀഀ
There are several ways to put an "event" active, for example:<br>਍㰀戀爀㸀ഀഀ
<ol>਍㰀氀椀㸀唀猀椀渀最 琀栀攀 䄀䰀吀䔀刀 匀䔀匀匀䤀伀一 匀䔀吀 䔀嘀䔀一吀匀 ✀攀瘀攀渀琀猀渀甀洀戀攀爀✀ 猀琀愀琀攀洀攀渀琀⸀ 吀栀椀猀 眀椀氀氀 攀昀昀攀挀琀 礀漀甀爀 挀甀爀爀攀渀琀 猀攀猀猀椀漀渀 漀渀氀礀⸀㰀⼀氀椀㸀㰀戀爀㸀ഀഀ
<li>Using the ALTER SYSTEM SET EVENTS 'eventsnumber' statement. This is a change "Instance" wide .</li><br>਍㰀氀椀㸀䔀搀椀琀椀渀最 琀栀攀 椀渀椀琀⸀漀爀愀 猀琀愀爀琀甀瀀 昀椀氀攀 ⠀椀昀 礀漀甀 搀漀 渀漀琀 甀猀攀 愀 猀瀀昀椀氀攀⸀漀爀愀⤀⸀ 吀栀椀猀 椀猀 愀 挀栀愀渀最攀 ∀䤀渀猀琀愀渀挀攀∀ 眀椀搀攀 ⸀㰀⼀氀椀㸀㰀戀爀㸀ഀഀ
</ol>਍㰀戀爀㸀ഀഀ
Now, the "behaviour" we are searching for, is that Oracle will skip "corrupt" blocks, and that it <br>਍愀氀氀漀眀猀 礀漀甀 猀攀氀攀挀琀 琀栀攀 眀栀漀氀攀 琀愀戀氀攀 愀最愀椀渀 ⠀攀砀挀攀瀀琀 昀漀爀 琀栀攀 挀漀爀爀甀瀀琀 戀氀漀挀欀⤀⸀㰀戀爀㸀ഀഀ
<br>਍䤀渀 愀 眀愀礀Ⰰ 琀栀椀猀 洀攀琀栀漀搀 椀猀 渀漀琀 洀甀挀栀 搀椀昀昀攀爀攀渀琀 昀爀漀洀 眀栀愀琀 眀攀 栀愀瘀攀 猀攀攀渀 椀渀 猀攀挀琀椀漀渀 ㌀⸀㄀Ⰰ㰀戀爀㸀ഀഀ
using the DBMS_REPAIR.SKIP_CORRUPT_BLOCKS procedure.<br>਍㰀戀爀㸀ഀഀ
If it works, you again have a method to SELECT all of the table again, and just skipping corrupt blocks.<br>਍吀栀椀猀 洀攀愀渀猀 琀栀愀琀 礀漀甀 挀愀渀 挀爀攀愀琀攀 愀 渀攀眀 琀愀戀氀攀 昀爀漀洀 琀栀攀 搀愀洀愀最攀搀 漀渀攀Ⰰ 樀甀猀琀 愀猀 眀愀猀 猀栀漀眀渀 椀渀 猀攀挀琀椀漀渀 ㌀⸀㄀⸀㰀戀爀㸀ഀഀ
<br>਍䰀攀琀✀猀 琀爀礀 椀琀⸀㰀戀爀㸀ഀഀ
<br>਍䘀椀爀猀琀Ⰰ 氀攀琀✀猀 猀琀愀爀琀 愀最愀椀渀 眀椀琀栀 愀 ∀昀爀攀猀栀∀ 猀椀琀甀愀琀椀漀渀⸀ 吀栀愀琀 椀猀Ⰰ 搀爀漀瀀 琀栀攀 琀攀猀琀甀猀攀爀 ⠀愀渀搀 愀氀氀 漀昀 栀椀猀 漀戀樀攀挀琀猀⤀Ⰰ㰀戀爀㸀ഀഀ
and then drop the tablespaces "SALES_DATA_1" and "SALES_DATA_1".<br>਍吀栀攀渀 挀爀攀愀琀攀 琀栀漀猀攀 琀愀戀氀攀猀瀀愀挀攀猀 愀最愀椀渀Ⰰ 挀爀攀愀琀攀 琀栀攀 琀攀猀琀甀猀攀爀 愀最愀椀渀Ⰰ 挀爀攀愀琀攀 琀栀攀 琀愀戀氀攀猀 愀最愀椀渀Ⰰ 愀渀搀 甀猀攀 琀栀攀 猀挀爀椀瀀琀㰀戀爀㸀ഀഀ
that inserts 9999 into the EMPLOYEE1 table.<br>਍䤀琀✀猀 渀漀琀 洀甀挀栀 眀漀爀欀Ⰰ 猀椀渀挀攀 眀攀 栀愀瘀攀 愀氀氀 琀栀攀 渀攀挀挀攀猀猀愀爀礀 猀琀愀琀攀洀攀渀琀猀 椀渀 䌀栀愀瀀琀攀爀 ㄀⸀㰀戀爀㸀ഀഀ
Then use XVI32 to soft corrupt the 19th block (change a "harry" into "HENRY"), exactly as described in section 2.1<br>਍㰀戀爀㸀ഀഀ
If you have done all of the above, logon as "testuser", and try the following:<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
SQL> SELECT * FROM EMPLOYEE1;<br>਍㰀戀爀㸀ഀഀ
(many records skipped...)<br>਍㰀戀爀㸀ഀഀ
..<br>਍      ㈀㐀㄀㔀 栀愀爀爀礀                      ㈀㔀　　㰀戀爀㸀ഀഀ
      2416 harry                      2500<br>਍      ㈀㐀㄀㜀 栀愀爀爀礀                      ㈀㔀　　㰀戀爀㸀ഀഀ
<br>਍䔀刀刀伀刀㨀㰀戀爀㸀ഀഀ
ORA-01578: ORACLE data block corrupted (file # 5, block # 19)<br>਍伀刀䄀ⴀ　㄀㄀㄀　㨀 搀愀琀愀 昀椀氀攀 㔀㨀 ✀䌀㨀尀伀刀䄀䐀䄀吀䄀尀匀䄀䰀䔀匀尀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀⸀䐀䈀䘀✀㰀戀爀㸀ഀഀ
<br>਍㈀㠀㈀　 爀漀眀猀 猀攀氀攀挀琀攀搀⸀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
Ofcourse, we face the dreaded ORA-01578 error again.<br>਍一攀砀琀Ⰰ 琀爀礀 琀栀攀 昀漀氀氀漀眀椀渀最 䄀䰀吀䔀刀 匀䔀匀匀䤀伀一 匀䔀吀 䔀嘀䔀一吀匀 挀漀洀洀愀渀搀㨀㰀戀爀㸀ഀഀ
<br>਍ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
SQL> ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';<br>਍㰀戀爀㸀ഀഀ
SQL> SELECT * FROM EMPLOYEE1;<br>਍㰀戀爀㸀ഀഀ
(many records skipped...)<br>਍      㤀㤀㤀㜀 栀愀爀爀礀                      ㈀㔀　　㰀戀爀㸀ഀഀ
      9998 harry                      2500<br>਍      㤀㤀㤀㤀 栀愀爀爀礀                      ㈀㔀　　㰀戀爀㸀ഀഀ
<br>਍㤀㔀㤀㘀 爀漀眀猀 猀攀氀攀挀琀攀搀⸀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
This time, <B>no error is produced</B>, and your query just continues and is not halted.<br>਍夀漀甀 愀爀攀 渀漀眀 愀戀氀攀 琀漀 猀攀氀攀挀琀 琀栀攀 眀栀漀氀攀 琀愀戀氀攀 愀最愀椀渀Ⰰ 愀氀氀漀眀椀渀最 礀漀甀 琀漀 猀愀氀瘀愀最攀 愀氀氀 ✀最漀漀搀✀ 爀漀眀猀 琀漀 愀 渀攀眀 琀愀戀氀攀⸀㰀戀爀㸀ഀഀ
<br>਍一漀琀攀㨀 䤀昀 伀爀愀挀氀攀 椀猀 漀渀攀 漀昀 礀漀甀爀 洀愀椀渀 昀漀挀甀猀 瀀漀椀渀琀猀Ⰰ 愀渀搀 猀甀瀀瀀漀猀攀 礀漀甀 愀爀攀 渀漀琀 琀漀漀 昀愀洀椀氀椀愀爀 眀椀琀栀 琀栀攀 匀䔀吀 䔀嘀䔀一吀匀 猀琀愀琀攀洀攀渀琀Ⰰ㰀戀爀㸀ഀഀ
you really should spend one of these rainy day's on digging further into this very usefull diagnostic tool.<br>਍㰀戀爀㸀ഀഀ
<br>਍㰀栀㌀㸀㌀⸀㔀 儀甀攀爀礀椀渀最 ∀愀爀漀甀渀搀∀ 戀愀搀 戀氀漀挀欀猀⸀㰀⼀栀㈀㸀ഀഀ
਍䤀渀 琀栀椀猀 洀攀琀栀漀搀Ⰰ 礀漀甀 樀甀猀琀 甀猀攀 匀儀䰀 焀甀攀爀椀攀猀 琀漀 漀戀琀愀椀渀 愀氀氀 最漀漀搀 爀漀眀猀 昀爀漀洀 琀栀攀 漀戀樀攀挀琀Ⰰ 愀渀搀 眀栀攀爀攀 礀漀甀 愀爀攀 漀渀氀礀 猀欀椀瀀瀀椀渀最㰀戀爀㸀ഀഀ
the bad block(s).<br>਍䤀琀✀猀 渀漀琀 最愀爀愀渀琀攀攀搀 琀栀愀琀 椀琀 眀椀氀氀 眀漀爀欀 椀渀 㰀䤀㸀愀氀氀㰀⼀䤀㸀 挀愀猀攀猀Ⰰ 戀甀琀 椀渀 洀愀渀礀 椀琀 眀椀氀氀⸀㰀戀爀㸀ഀഀ
The following excellent article can be viewed as a true "classic" on the subject, and it will explain you<br>਍愀氀氀 礀漀甀 渀攀攀搀 琀漀 欀渀漀眀⸀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ
਍栀琀琀瀀㨀⼀⼀眀攀戀⸀椀渀琀攀爀⸀渀氀⸀渀攀琀⼀甀猀攀爀猀⼀吀⸀䬀漀瀀瀀攀氀愀愀爀猀⼀漀爀愀　㄀㔀㜀㠀⸀爀琀昀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
<br>਍吀栀攀 琀攀挀栀渀椀焀甀攀 搀攀猀挀爀椀戀攀搀 椀渀 琀栀攀 愀爀琀椀挀氀攀Ⰰ 爀攀氀礀 漀渀 爀漀眀椀搀✀猀 愀渀搀 猀漀洀攀 漀琀栀攀爀 猀漀瀀栀椀猀琀椀挀愀琀攀搀 椀渀猀椀最栀琀猀⸀㰀戀爀㸀ഀഀ
<br>਍䤀 栀愀瘀攀 渀漀琀栀椀渀最 琀漀 愀搀搀 琀漀 挀漀渀琀攀渀琀 漀昀 琀栀攀 愀戀漀瘀攀 愀爀琀椀挀氀攀⸀ 䴀愀礀戀攀 䤀 挀愀渀 猀瀀攀渀搀 樀甀猀琀 愀 昀攀眀 眀漀爀搀猀 漀渀 愀 猀瀀攀挀椀愀氀 挀愀猀攀⸀㰀戀爀㸀ഀഀ
This special case is illustrated by the following example. Here we try <I>to avoid to access the table at all,</I><br>਍愀渀搀 眀攀 眀愀渀琀 伀爀愀挀氀攀 琀漀 伀一䰀夀 甀猀攀 琀栀攀 㰀䤀㸀∀挀漀瘀攀爀椀渀最 椀渀搀攀砀∀㰀⼀䤀㸀Ⰰ 椀渀 愀 瘀攀爀礀 猀椀洀瀀氀攀 挀漀渀琀攀砀琀⸀㰀戀爀㸀ഀഀ
<br>਍夀漀甀 欀渀漀眀 琀栀愀琀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀㄀ 琀愀戀氀攀 栀愀猀 琀栀攀 昀漀氀氀漀眀椀渀最 䐀䐀䰀㨀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀 䔀䴀倀䰀伀夀䔀䔀㄀㰀戀爀㸀ഀഀ
(<br>਍䔀䴀倀开䤀䐀     一唀䴀䈀䔀刀⠀㘀⤀    一伀吀 一唀䰀䰀Ⰰ㰀戀爀㸀ഀഀ
EMP_NAME   VARCHAR2(20) NOT NULL,<br>਍匀䄀䰀䄀刀夀     一唀䴀䈀䔀刀⠀㜀Ⰰ㈀⤀㰀戀爀㸀ഀഀ
)<br>਍吀䄀䈀䰀䔀匀倀䄀䌀䔀 匀䄀䰀䔀匀开䐀䄀吀䄀开㄀㬀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
Now, suppose we have an index on that table like this one:<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
CREATE INDEX INDEX_EMP ON EMPLOYEE1(EMP_NAME, SALARY) TABLESPACE SALES_INDEX_1.<br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍ഀഀ
In a sense, you may view an index as a sort of "mini" table of the real table object.<br>਍吀栀攀 挀栀漀猀攀渀 欀攀礀⠀猀⤀ 漀昀 琀栀攀 椀渀搀攀砀Ⰰ 挀漀爀爀攀猀瀀漀渀搀猀 琀漀 琀栀攀 猀愀洀攀 挀漀氀甀洀渀猀 漀昀 琀栀攀 琀愀戀氀攀⸀㰀戀爀㸀ഀഀ
I agree that this example is a <I>very opportunistic</I> example, because the index here has almost all tablecolumns<br>਍攀砀挀攀瀀琀 昀漀爀 琀栀攀 ∀䔀䴀倀开䤀䐀∀ 挀漀氀甀洀渀⸀㰀戀爀㸀ഀഀ
But I only want to convey the basic idea here.<br>਍㰀戀爀㸀ഀഀ
Now, its just a fact, that if you <B>only</B> query on a selection of the table columns, which are in the index as well,<br>਍琀栀攀 挀栀愀渀挀攀猀 愀爀攀 瀀爀攀琀琀礀 最漀漀搀 琀栀愀琀 伀爀愀挀氀攀 眀椀氀氀 漀渀氀礀 愀挀挀攀猀猀 琀栀攀 椀渀搀攀砀Ⰰ 愀渀搀 渀漀琀 愀挀挀攀猀猀椀渀最 琀栀攀 琀愀戀氀攀 㰀䈀㸀愀琀 愀氀氀㰀⼀䈀㸀⸀㰀戀爀㸀ഀഀ
<br>਍匀漀Ⰰ 琀栀愀琀 洀攀愀渀猀 琀栀愀琀 礀漀甀 挀愀渀 爀攀琀爀椀攀瘀攀 愀氀氀 搀愀琀愀 昀爀漀洀 琀栀攀 琀愀戀氀攀Ⰰ 眀栀椀挀栀 椀猀 挀漀瘀攀爀攀搀 戀礀 琀栀攀 椀渀搀攀砀⸀㰀戀爀㸀ഀഀ
This includes that data (from the index), which corresponds to the data (from the table) that is on the corrupt block(s).<br>਍䄀最愀椀渀Ⰰ 愀渀搀 椀琀✀猀 瘀攀爀礀 椀洀瀀漀爀琀愀渀琀 琀漀 猀琀爀攀猀猀 琀栀愀琀 昀愀挀琀Ⰰ 椀琀✀猀 漀渀氀礀 琀栀愀琀 搀愀琀愀 ⠀漀渀氀礀 琀栀漀猀攀 挀漀氀甀洀渀猀⤀ 眀栀椀挀栀㰀戀爀㸀ഀഀ
is included in the index.<br>਍㰀戀爀㸀ഀഀ
The theory is somewhat illustrated by the figure below:<br>਍㰀戀爀㸀ഀഀ
<img src="oraclecorr3.jpg" align="center"/>਍㰀戀爀㸀ഀഀ
And you know? You can even ask Oracle "nicely" to <B>use</B> the index, by using a <B>"Query Hint".</B><br>਍䈀甀琀 攀瘀攀渀 眀椀琀栀漀甀琀 琀栀攀 ∀栀椀渀琀∀Ⰰ 椀昀 礀漀甀 漀渀氀礀 焀甀攀爀礀 漀渀 挀漀氀甀洀猀 眀栀椀挀栀 愀爀攀 愀氀氀 挀漀瘀攀爀攀搀 戀礀 琀栀攀 椀渀搀攀砀Ⰰ 伀爀愀挀氀攀 眀椀氀氀㰀戀爀㸀ഀഀ
almost for sure use that index without accessing the table at all.<br>਍㰀戀爀㸀ഀഀ
I agree that the example above certainly has it's flaws. But I hope the general idea is clear.<br>਍㰀戀爀㸀ഀഀ
Excercise: try to make the above scenario work, and see if you indeed are able to retreive <U>all</U> EMP_NAME and SALARY<br>਍瘀愀氀甀攀猀Ⰰ 甀猀椀渀最 琀栀攀 椀渀搀攀砀 愀氀漀渀攀⸀㰀戀爀㸀ഀഀ
<br>਍㰀戀爀㸀ഀഀ
<h3>3.6 Some remarks on the structure of a block.</h2>਍ഀഀ
You probably know that "segments", like tables and indexes, are build from database "blocks" (or "pages').<br>਍唀猀甀愀氀氀礀Ⰰ 愀 䐀䈀䄀 猀攀氀攀挀琀猀 愀 猀甀椀琀愀戀氀攀 戀氀漀挀欀猀椀稀攀 瘀漀漀爀 琀栀攀 攀渀琀椀爀攀 搀愀琀愀戀愀猀攀Ⰰ 漀爀 昀漀爀 椀渀搀椀瘀椀搀甀愀氀 琀愀戀氀攀猀瀀愀挀攀猀⸀㰀戀爀㸀ഀഀ
Typical blocksizes are 4K, 8K, 32K etc..<br>਍㰀戀爀㸀ഀഀ
Oracle's block structure is quite complex. In such block, there are many specific "fields",<br>਍攀愀挀栀 眀椀琀栀 愀 瘀攀爀礀 猀瀀攀挀椀昀椀挀 瀀甀爀瀀漀猀攀 琀漀 琀栀攀 搀愀琀愀戀愀猀攀 攀渀最椀渀攀⸀㰀戀爀㸀ഀഀ
Below is a very schematic, and very simplified, representation of a Data Block:<br>਍㰀戀爀㸀ഀഀ
<br>਍㰀椀洀最 猀爀挀㴀∀漀爀愀挀氀攀挀漀爀爀㔀⸀樀瀀最∀ 愀氀椀最渀㴀∀挀攀渀琀攀爀∀⼀㸀ഀഀ
<br>਍㰀戀爀㸀ഀഀ
We can see a "Header" part, with a number of fixed fields, like the "DBA" (database block address), or the "blocktype".<br>਍吀栀攀 戀氀漀挀欀琀礀瀀攀 椀搀攀渀琀椀昀椀攀猀 眀栀愀琀 猀漀爀琀 漀昀 戀氀漀挀欀 眀攀 愀爀攀 搀攀愀氀椀渀最 眀椀琀栀⸀㰀戀爀㸀ഀഀ
Ofcourse, most blocks in a database file are "data blocks" (type 06), for tables and indexes. But many "administrative" blocks<br>਍攀砀椀猀琀猀 愀猀 眀攀氀氀Ⰰ 氀椀欀攀 琀栀攀 ∀猀攀最洀攀渀琀 栀攀愀搀攀爀∀Ⰰ 眀栀椀挀栀 椀猀 愀 瘀攀爀礀 猀瀀攀挀椀愀氀 戀氀漀挀欀 昀漀爀 攀愀挀栀 琀愀戀氀攀 漀爀 椀渀搀攀砀⸀㰀戀爀㸀ഀഀ
<br>਍䄀渀漀琀栀攀爀 椀渀琀攀爀爀攀猀琀椀渀最 昀椀攀氀搀 椀猀 琀栀攀 刀䐀䈀䄀Ⰰ 漀爀 琀栀攀 ∀爀攀氀愀琀椀瘀攀 搀愀琀愀戀愀猀攀 戀氀漀挀欀 愀搀搀爀攀猀猀∀⸀ 吀栀椀猀 椀猀 愀渀 㐀 戀礀琀攀 愀搀搀爀攀猀猀 眀栀椀挀栀 洀漀猀琀 琀漀漀氀猀Ⰰ㰀戀爀㸀ഀഀ
or dump commands, will show in hexadecimal format. Fortunately, some "tools" or dump commands will also use a more understandable<br>਍渀漀琀愀琀椀漀渀Ⰰ 眀栀椀挀栀 椀猀 氀椀欀攀 猀漀㨀 ⠀昀椀氀攀开椀搀Ⰰ 戀氀漀挀欀渀甀洀戀攀爀⤀⸀ 匀漀Ⰰ 昀漀爀 攀砀愀洀瀀氀攀 ⠀㜀Ⰰ㄀㌀㜀⤀ 眀漀甀氀搀 琀栀攀渀 洀攀愀渀 戀氀漀挀欀 渀甀洀戀攀爀 ㄀㌀㜀 椀渀 昀椀氀攀 渀甀洀戀攀爀 㜀⸀㰀戀爀㸀ഀഀ
<br>਍一攀砀琀 琀漀 琀栀攀 栀攀愀搀攀爀Ⰰ 愀 ∀琀爀愀渀猀愀挀琀椀漀渀愀氀 氀愀礀攀爀∀ 椀猀 瀀爀攀猀攀渀琀⸀ 匀椀渀挀攀 洀甀氀琀椀瀀氀攀 瀀爀漀挀攀猀猀攀猀 挀愀渀 愀挀挀攀猀猀 愀 戀氀漀挀欀Ⰰ 椀琀 椀猀 昀漀爀 攀砀愀洀瀀氀攀㰀戀爀㸀ഀഀ
needed to store transaction id's. Next, the "Table Directory" contains information about the table object,<br>਍眀栀椀挀栀 栀愀猀 爀漀眀猀 猀琀漀爀攀搀 椀渀 琀栀椀猀 瀀愀爀琀椀挀甀氀愀爀 戀氀漀挀欀⸀ 一攀砀琀Ⰰ 琀栀攀 ∀刀漀眀 䐀椀爀攀挀琀漀爀礀∀ 挀漀渀琀愀椀渀猀 椀渀昀漀爀洀愀琀椀漀渀 愀戀漀甀琀 愀挀琀甀愀氀 爀漀眀猀 椀渀 琀栀攀 戀氀漀挀欀Ⰰ㰀戀爀㸀ഀഀ
including addresses for each row piece. Following that, is a certain amount of "free space" which can be used for new rows.<br>਍吀栀攀渀 愀琀 氀愀猀琀Ⰰ 眀攀 栀愀瘀攀 猀瀀愀挀攀 昀漀爀 琀栀攀 愀挀琀甀愀氀 搀愀琀愀Ⰰ 琀栀愀琀 椀猀Ⰰ 琀栀攀 爀漀眀猀 漀昀 琀栀攀 琀愀戀氀攀 漀爀 椀渀搀攀砀 漀戀樀攀挀琀⸀㰀戀爀㸀ഀഀ
<br>਍匀椀渀挀攀 眀攀 栀愀瘀攀 ∀砀瘀椀㌀㈀∀ 愀猀 漀甀爀 䠀攀砀 攀搀椀琀漀爀Ⰰ 氀攀琀✀猀 琀愀欀攀 愀 氀漀漀欀 愀琀 栀漀眀 䈀氀漀挀欀 ㈀　 椀渀 ∀挀㨀尀漀爀愀搀愀琀愀尀猀愀氀攀猀尀猀愀氀攀猀开搀愀琀愀开　㄀⸀搀戀昀∀㰀戀爀㸀ഀഀ
looks like. Later we will see more tools and options to view contents of blocks, but let's stick to xvi32 for now.<br>਍㰀戀爀㸀ഀഀ
Make sure the instance is closed. Next, start xvi32 and open "c:\oradata\sales\sales_data_01.dbf". <br>਍一漀眀Ⰰ 渀愀瘀椀最愀琀攀 琀漀 栀攀砀 愀搀搀爀攀猀猀 ㈀㠀　　　⸀ 䠀攀爀攀 眀攀 眀椀氀氀 猀攀攀 琀栀攀 ∀猀琀愀爀琀∀ 漀昀 戀氀漀挀欀 ㈀　⸀㰀戀爀㸀ഀഀ
<br>਍ഀഀ
<img src="oraclecorr7.jpg" align="center"/>਍ ഀഀ
<br>਍㰀戀爀㸀ഀഀ
Do you notice that the block starts with "06", meaning that this is just a data block. Next, the format seems to be "A2".<br>਍琀栀攀渀 眀攀 猀攀攀 琀栀攀 ∀　　 　　∀ 漀昀 琀栀攀 甀渀甀猀攀搀 ∀猀瀀愀爀攀∀ 昀椀攀氀搀猀⸀ 䄀昀琀攀爀 琀栀愀琀Ⰰ 琀栀攀 戀氀漀挀欀✀猀 䐀䈀䄀 昀漀氀氀漀眀猀 攀琀挀⸀⸀⸀ 攀琀挀⸀⸀⸀㰀戀爀㸀ഀഀ
So, in my database, all data blocks start with "06 A2 00 00"<br>਍㰀戀爀㸀ഀഀ
This way, you could walk through the header, or even the entire block. This not what we will do right now, since we need<br>਍琀漀 猀攀攀 昀椀爀猀琀Ⰰ 眀栀愀琀 漀琀栀攀爀 琀漀漀氀猀 攀砀椀猀琀 琀漀 瘀椀攀眀 琀栀攀 挀漀渀琀攀渀琀猀 漀昀 搀愀琀愀戀愀猀攀 戀氀漀挀欀猀⸀㰀戀爀㸀ഀഀ
<br>਍㰀戀爀㸀ഀഀ
਍㰀栀㌀㸀㌀⸀㜀 唀猀椀渀最 琀漀漀氀猀 愀渀搀 挀漀洀洀愀渀搀猀 琀漀 搀甀洀瀀 愀渀搀 瘀椀攀眀 戀氀漀挀欀猀⸀㰀⼀栀㈀㸀ഀഀ
਍ഀഀ
਍㰀䈀㸀㌀⸀㜀⸀㄀ 䄀 昀攀眀 眀漀爀搀猀 漀渀 琀栀攀 ∀䄀䰀吀䔀刀 匀夀匀吀䔀䴀 䐀唀䴀倀 䐀䄀吀䄀䘀䤀䰀䔀 昀椀氀攀渀甀洀戀攀爀 䈀䰀伀䌀䬀 戀氀漀挀欀渀甀洀戀攀爀∀ 猀琀愀琀攀洀攀渀琀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
<br>਍䘀爀漀洀 愀 猀焀氀瀀氀甀猀 猀攀猀猀椀漀渀Ⰰ 礀漀甀 挀愀渀 甀猀攀 琀栀攀 昀漀氀氀漀眀椀渀最 ∀䄀䰀吀䔀刀 匀夀匀吀䔀䴀⸀⸀∀ 猀琀愀琀攀洀攀渀琀 琀漀 搀甀洀瀀 琀栀攀 挀漀渀琀攀渀琀猀 漀昀 愀 戀氀漀挀欀 琀漀 愀 琀爀愀挀攀昀椀氀攀⸀㰀戀爀㸀ഀഀ
This tracefile will be created in the USER_DUMP_DIRECTORY.<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
ALTER SYSTEM DUMP DATAFILE filenumber BLOCK blocknumber;<br>਍㰀戀爀㸀ഀഀ
or:<br>਍㰀戀爀㸀ഀഀ
ALTER SYSTEM DUMP DATAFILE filenumber BLOCK min first block number BLOCK max last block number;<br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍匀漀Ⰰ 甀猀椀渀最 愀 挀漀渀渀攀挀琀椀漀渀 琀漀 漀甀爀 匀䄀䰀䔀匀 搀愀琀愀戀愀猀攀Ⰰ 眀攀 挀漀甀氀搀 昀漀爀 攀砀愀洀瀀氀攀 琀爀礀 琀栀椀猀 挀漀洀洀愀渀搀㨀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍匀儀䰀㸀 䄀䰀吀䔀刀 匀夀匀吀䔀䴀 䐀唀䴀倀 䐀䄀吀䄀䘀䤀䰀䔀 㔀 䈀䰀伀䌀䬀 ㄀㜀㬀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
The choice of numbers shown above, is due to the fact that we know that the TESTUSER.EMPLOYEE table lives in the<br>਍昀椀爀猀琀 昀攀眀 搀漀稀攀渀猀 漀昀 戀氀漀挀欀猀 椀渀 琀栀攀 ∀挀㨀尀漀爀愀搀愀琀愀尀猀愀氀攀猀尀猀愀氀攀猀开搀愀琀愀开　㄀⸀搀戀昀∀ 昀椀氀攀Ⰰ 眀栀椀挀栀 椀渀 洀礀 挀愀猀攀Ⰰ 椀猀 搀愀琀愀昀椀氀攀 渀甀洀戀攀爀 ∀㔀∀⸀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ
<B>Note 1: how to lookup the file id's?</B><br>਍㰀戀爀㸀ഀഀ
You probably know how to lookup the file numbers of the datafiles. Anyway, I will list one methode here:<br>਍䤀渀 洀礀 匀䄀䰀䔀匀 搀愀琀愀戀愀猀攀Ⰰ 眀攀 挀愀渀 猀攀攀 琀栀椀猀㨀㰀戀爀㸀ഀഀ
<br>਍匀儀䰀㸀 猀攀琀 氀椀渀攀猀椀稀攀 ㄀　　　㰀戀爀㸀ഀഀ
SQL> set pagesize 1000<br>਍㰀戀爀㸀ഀഀ
SQL> SELECT FILE#, NAME FROM v$datafile;<br>਍㰀戀爀㸀ഀഀ
FILE# NAME<br>਍㄀ 䌀㨀尀伀刀䄀䐀䄀吀䄀尀吀䔀匀吀㄀　䜀尀匀夀匀吀䔀䴀　㄀⸀䐀䈀䘀㰀戀爀㸀ഀഀ
2 C:\ORADATA\TEST10G\UNDOTBS01.DBF<br>਍㌀ 䌀㨀尀伀刀䄀䐀䄀吀䄀尀吀䔀匀吀㄀　䜀尀匀夀匀䄀唀堀　㄀⸀䐀䈀䘀㰀戀爀㸀ഀഀ
4 C:\ORADATA\TEST10G\USERS01.DBF<br>਍㔀 䌀㨀尀伀刀䄀䐀䄀吀䄀尀匀䄀䰀䔀匀尀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀⸀䐀䈀䘀㰀戀爀㸀ഀഀ
6 C:\ORADATA\SALES\SALES_DATA_02.DBF<br>਍㜀 䌀㨀尀伀刀䄀䐀䄀吀䄀尀匀䄀䰀䔀匀尀匀䄀䰀䔀匀开䤀一䐀䔀堀开　㄀⸀䐀䈀䘀㰀戀爀㸀ഀഀ
<br>਍䈀礀 琀栀攀 眀愀礀Ⰰ 礀漀甀 洀愀礀 愀氀猀漀 甀猀攀 琀栀攀 昀甀氀氀 瀀愀琀栀 漀昀 琀栀攀 搀愀琀愀昀椀氀攀 椀渀 琀栀攀 ∀搀甀洀瀀∀ 挀漀洀洀愀渀搀Ⰰ 氀椀欀攀 昀漀爀 攀砀愀洀瀀氀攀㨀㰀戀爀㸀ഀഀ
<br>਍匀儀䰀㸀 䄀䰀吀䔀刀 匀夀匀吀䔀䴀 䐀唀䴀倀 䐀䄀吀䄀䘀䤀䰀䔀 ✀䌀㨀尀伀刀䄀䐀䄀吀䄀尀匀䄀䰀䔀匀尀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀⸀䐀䈀䘀✀ 䈀䰀伀䌀䬀 ㄀㜀㬀㰀戀爀㸀ഀഀ
<br>਍ഀഀ
<B>Note 2: Where are the trace files stored?</B><br>਍㰀戀爀㸀ഀഀ
"Traditionally" (in Oracle up to 10g included), the init.ora/spfile.ora parameter "USER_DUMP_DEST",<br>਍猀瀀攀挀椀昀椀攀猀 猀漀洀攀 氀漀挀愀琀椀漀渀 漀渀 漀甀爀 昀椀氀攀猀礀猀琀攀洀 眀栀攀爀攀 伀爀愀挀氀攀 眀椀氀氀 ∀搀甀洀瀀∀ 琀爀愀挀攀昀椀氀攀猀 昀爀漀洀 甀猀攀爀瀀爀漀挀攀猀猀攀猀⸀㰀戀爀㸀ഀഀ
You can find that location using:<br>਍㰀戀爀㸀ഀഀ
select value from v$parameter where name = 'user_dump_dest';<br>਍㰀戀爀㸀ഀഀ
This will show you the "USER_DUMP_DEST" directory. For example, on my 10g installation on Windows, it's:<br>਍㰀戀爀㸀ഀഀ
C:\oracle\product\10.2\admin\sales\udump<br>਍㰀戀爀㸀ഀഀ
Sometimes there can be a lot of ".trc" files in that place. Ofcourse, the timestamp of the files will tell<br>਍礀漀甀 眀栀愀琀 椀猀 礀漀甀爀 琀爀愀挀攀Ⰰ 戀甀琀 礀漀甀 愀氀猀漀 甀猀攀 琀栀椀猀 焀甀攀爀礀 琀漀 昀椀渀搀 琀栀攀 ∀瀀爀漀挀攀猀猀 椀搀∀ 眀栀椀挀栀 挀爀攀愀琀攀搀 礀漀甀爀 昀椀氀攀⸀㰀戀爀㸀ഀഀ
Then, just note the trace file which name carries that number, and your dump should be easy to identify.<br>਍㰀戀爀㸀ഀഀ
select spid from v$session s, v$process p<br>਍眀栀攀爀攀 瀀⸀愀搀搀爀 㴀 猀⸀瀀愀搀搀爀 愀渀搀 猀⸀愀甀搀猀椀搀 㴀 猀礀猀开挀漀渀琀攀砀琀⠀✀甀猀攀爀攀渀瘀✀Ⰰ✀猀攀猀猀椀漀渀椀搀✀⤀㰀戀爀㸀ഀഀ
<br>਍㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍䰀攀琀✀猀 爀攀琀甀爀渀 琀漀 搀甀洀瀀椀渀最 愀 戀氀漀挀欀 琀漀 愀 琀爀愀挀攀 昀椀氀攀⸀ 匀漀Ⰰ 眀栀愀琀 挀愀渀 眀攀 攀砀瀀攀挀琀 琀漀 猀攀攀㼀㰀戀爀㸀ഀഀ
Above, we already dumped block 17 in database file 5, to a trace file in the "USER_DUMP_DEST" directory.<br>਍匀甀挀栀 昀椀氀攀猀 愀爀攀 樀甀猀琀 愀猀挀椀椀 琀砀琀 昀椀氀攀猀 眀栀椀挀栀 礀漀甀 挀愀渀 瘀椀攀眀 甀猀椀渀最 礀漀甀爀 昀愀瘀漀爀椀琀攀 琀砀琀 攀搀椀琀漀爀⸀㰀戀爀㸀ഀഀ
Here is some partial output of my trace:<br>਍ഀഀ
<font face="courier" size=2 color="black">਍㰀戀爀㸀ഀഀ
73E6600 0000A206 01400011 000F6019 06010000  [......@..`......]<br>਍㜀㌀䔀㘀㘀㄀　 　　　　䐀䘀㄀　 　　　　　　　㄀ 　　　　䈀䈀䄀䔀 　　　䘀㔀䘀䘀䌀  嬀⸀⸀⸀⸀⸀⸀⸀⸀⸀⸀⸀⸀⸀开⸀⸀崀㰀戀爀㸀ഀഀ
73E6620 00000000 01320002 01400009 00190003  [......2...@.....]<br>਍㜀㌀䔀㘀㘀㌀　 　　　　　㄀䘀㄀ 　　㠀　　㄀䐀䔀 　　㘀㜀　　㘀䄀 　　　　㈀㄀㤀㌀  嬀⸀⸀⸀⸀⸀⸀⸀⸀樀⸀最⸀⸀℀⸀⸀崀㰀戀爀㸀ഀഀ
73E6640 000F6019 00000000 00000000 00000000  [.`..............]<br>਍㰀戀爀㸀ഀഀ
.. many rows skipped..<br>਍㰀戀爀㸀ഀഀ
73E6CD0 0303012C 052E35C2 72726168 1AC20279  [,....5..harry...]<br>਍㜀㌀䔀㘀䌀䔀　 　㌀　㌀　㄀㈀䌀 　㔀㈀䐀㌀㔀䌀㈀ 㜀㈀㜀㈀㘀㄀㘀㠀 ㄀䄀䌀㈀　㈀㜀㤀  嬀Ⰰ⸀⸀⸀⸀㔀ⴀ⸀栀愀爀爀礀⸀⸀⸀崀㰀戀爀㸀ഀഀ
73E6CF0 0303012C 052C35C2 72726168 1AC20279  [,....5,.harry...]<br>਍㜀㌀䔀㘀䐀　　 　㌀　㌀　㄀㈀䌀 　㔀㈀䈀㌀㔀䌀㈀ 㜀㈀㜀㈀㘀㄀㘀㠀 ㄀䄀䌀㈀　㈀㜀㤀  嬀Ⰰ⸀⸀⸀⸀㔀⬀⸀栀愀爀爀礀⸀⸀⸀崀㰀戀爀㸀ഀഀ
73E6D10 0303012C 052A35C2 72726168 1AC20279  [,....5*.harry...]<br>਍⸀⸀ 攀琀挀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
You are probably no so very much impressed by what you can "see" in this output.<br>਍吀栀攀爀攀 椀猀 漀昀挀漀甀爀猀攀 洀甀挀栀 洀漀爀攀 琀漀 猀愀礀 漀渀 栀漀眀 礀漀甀 挀愀渀 戀攀猀琀 椀渀琀攀爀瀀爀攀琀 琀栀漀猀攀 琀爀愀挀攀 昀椀氀攀猀⸀㰀戀爀㸀ഀഀ
<br>਍一漀眀Ⰰ 樀甀猀琀 琀漀 洀愀欀攀 礀漀甀 挀甀爀椀漀甀猀㨀㰀戀爀㸀ഀഀ
<br>਍㰀䈀㸀ⴀⴀ 伀琀栀攀爀 瘀愀爀椀愀渀琀猀 漀昀 琀栀攀 ∀䄀䰀吀䔀刀 匀夀匀吀䔀䴀 䐀唀䴀倀∀ 猀琀愀琀攀洀攀渀琀 琀栀愀琀 洀椀最栀琀 戀攀 漀昀 椀渀琀攀爀爀攀猀琀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
<br>਍䈀礀 琀栀攀 眀愀礀㨀 吀栀攀 ∀䄀䰀吀䔀刀 匀夀匀吀䔀䴀 䐀唀䴀倀⸀⸀∀ 猀琀愀琀攀洀攀渀琀 栀愀猀 洀漀爀攀 瘀愀爀椀愀渀琀猀⸀㰀戀爀㸀ഀഀ
For example, you can dump blocks from redologs as well. For example:<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
ALTER SYSTEM DUMP LOGFILE 'C:\ORADATA\SALES\redo05.dbf' DBA MIN 5 17221 DBA MAX 5 17250; <br>਍䄀䰀吀䔀刀 匀夀匀吀䔀䴀 䐀唀䴀倀 䰀伀䜀䘀䤀䰀䔀 ✀䌀㨀尀伀刀䄀䐀䄀吀䄀尀匀䄀䰀䔀匀尀爀攀搀漀　㔀⸀搀戀昀✀ 吀䤀䴀䔀 䴀䤀一 㔀㈀㠀㌀㄀㐀㘀㌀㌀㬀 㰀戀爀㸀ഀഀ
<br>਍⠀䐀䈀䄀㨀 搀愀琀愀戀愀猀攀 戀氀漀挀欀 愀搀搀爀攀猀猀 氀椀欀攀 眀攀 栀愀瘀攀 甀猀攀搀 戀攀昀漀爀攀Ⰰ 昀漀爀 攀砀愀洀瀀氀攀 ∀搀愀琀愀昀椀氀攀 㔀∀ 愀渀搀 ∀戀氀漀挀欀渀甀洀戀攀爀 ㄀㜀㈀㈀㄀∀⤀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍ഀഀ
<B>-- Using the SET EVENTS statement:</B><br>਍㰀戀爀㸀ഀഀ
Again, there is much to learn from the "SET EVENTS" statement. For example take a look at this:<br>਍㰀戀爀㸀ഀഀ
<font face="courier" size=2 color="black">਍ഀഀ
-- Dump control file contents:<br>਍愀氀琀攀爀 猀攀猀猀椀漀渀 猀攀琀 攀瘀攀渀琀猀 ✀椀洀洀攀搀椀愀琀攀 琀爀愀挀攀 渀愀洀攀 䌀伀一吀刀伀䰀䘀 氀攀瘀攀氀 ㄀　✀㰀戀爀㸀ഀഀ
<br>਍ⴀⴀ 䐀甀洀瀀 昀椀氀攀 栀攀愀搀攀爀猀㨀㰀戀爀㸀ഀഀ
alter session set events 'immediate trace name FILE_HDRS level 10'<br>਍㰀戀爀㸀ഀഀ
-- Dump redo log headers:<br>਍愀氀琀攀爀 猀攀猀猀椀漀渀 猀攀琀 攀瘀攀渀琀猀 ✀椀洀洀攀搀椀愀琀攀 琀爀愀挀攀 渀愀洀攀 刀䔀䐀伀䠀䐀刀 氀攀瘀攀氀 ㄀　✀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
I hope that you practice this note on a testsystem. If so, try the above SET EVENTS commands and take a look at the trace files.<br>਍㰀戀爀㸀ഀഀ
<br>਍㰀䈀㸀㌀⸀㜀⸀㈀ 䄀 昀攀眀 眀漀爀搀猀 漀渀 琀栀攀 伀爀愀挀氀攀 猀甀瀀瀀氀椀攀搀 ∀䈀䈀䔀䐀∀ 甀琀椀氀椀琀礀⸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
<br>਍吀栀攀 䈀䈀䔀䐀 ⠀䈀氀漀挀欀 䈀爀漀眀猀攀爀 愀渀搀 䔀䐀椀琀漀爀⤀ 甀琀椀氀椀琀礀Ⰰ 椀猀 愀 琀漀漀氀 昀爀漀洀 伀爀愀挀氀攀 椀琀猀攀氀昀⸀㰀戀爀㸀ഀഀ
However, It looks as if it <B>not present on all</B> Oracle versions (and platforms), which is somewhat "strange".<br>਍㰀戀爀㸀ഀഀ
It's a very powerfull tool, and just as it's name implies, you can <B>view</B> and <B>modify</B> database blocks.<br>਍㰀戀爀㸀ഀഀ
For the Windows platform, it's an executable, ready for use. On unix, you first need to link it, before you can use it.<br>਍伀渀 圀椀渀搀漀眀猀Ⰰ 䤀 昀漀甀渀搀 椀琀 椀渀 琀栀攀 伀爀愀挀氀攀 㠀椀 䔀䔀 刀攀氀攀愀猀攀 ㌀Ⰰ 瘀 㠀⸀㄀⸀㜀Ⰰ 愀渀搀 漀氀搀攀爀 瘀攀爀猀椀漀渀猀Ⰰ 戀甀琀Ⰰ 昀漀爀 攀砀愀洀瀀氀攀Ⰰ 䤀 挀漀甀氀搀 渀漀琀 昀椀渀搀 椀琀 椀渀 ㄀　最 漀爀 ㄀㄀最⸀㰀戀爀㸀ഀഀ
So, let's try that "old" one. But the utility is password protected, so let's find that first.<br>਍㰀戀爀㸀ഀഀ
<img src="oraclecorr9.jpg" align="center"/>਍㰀戀爀㸀ഀഀ
<br>਍唀猀椀渀最 砀瘀椀㌀㈀Ⰰ 漀瀀攀渀椀渀最 ∀戀戀攀搀⸀攀砀攀∀Ⰰ 愀渀搀 猀攀愀爀挀栀椀渀最 漀渀 琀栀攀 猀琀爀椀渀最 ∀瀀愀猀猀眀漀爀搀∀Ⰰ 琀栀攀 瀀愀猀猀眀漀爀搀 椀猀 焀甀椀挀氀礀 昀漀甀渀搀⸀㰀戀爀㸀ഀഀ
It's "blockedit". By the way, the password is "all over the Internet" as well.<br>਍匀漀 氀攀琀✀猀 猀琀愀爀琀 椀琀㨀㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍䌀㨀尀漀爀愀㠀椀尀戀椀渀㸀䈀䈀䔀䐀㰀戀爀㸀ഀഀ
Password:<br>਍㰀戀爀㸀ഀഀ
BBED: Release 2.0.0.0.0 - Limited Production on Thu Oct 28 12:03:36 2010<br>਍㰀戀爀㸀ഀഀ
(c) Copyright 2000 Oracle Corporation.  All rights reserved.<br>਍㰀戀爀㸀ഀഀ
************* !!! For Oracle Internal Use only !!! ***************<br>਍㰀戀爀㸀ഀഀ
BBED><br>਍㰀戀爀㸀ഀഀ
<font face="arial" size=2 color="black">਍吀栀攀 戀愀渀渀攀爀 琀栀愀琀 椀猀 猀栀漀眀渀Ⰰ 椀猀 猀漀洀攀眀栀愀琀 椀渀琀椀洀椀搀愀琀椀渀最Ⰰ 眀愀爀渀椀渀最 甀猀 琀栀愀琀 甀猀愀最攀 椀猀 昀漀爀 ∀伀爀愀挀氀攀 䤀渀琀攀爀渀愀氀 唀猀攀 漀渀氀礀∀⸀㰀戀爀㸀ഀഀ
This is not to take lightly. Ofcourse, on testsystems you can do what you like,<br>਍戀甀琀 昀漀爀 瀀爀漀搀甀挀琀椀漀渀Ⰰ 㰀䈀㸀渀攀瘀攀爀㰀⼀䈀㸀 甀猀攀 椀琀 眀椀琀栀漀甀琀 伀爀愀挀氀攀 匀甀瀀瀀漀爀琀 最甀椀搀愀渀挀攀⸀㰀戀爀㸀ഀഀ
<br>਍吀栀攀爀攀 攀砀椀猀琀猀 愀渀 攀砀挀攀氀氀攀渀琀 愀爀琀椀挀氀攀Ⰰ 搀攀猀挀爀椀戀椀渀最 琀栀攀 甀猀攀 漀昀 䈀䈀䔀䐀Ⰰ 椀渀挀氀甀搀椀渀最 猀漀洀攀 愀洀愀稀椀渀最 攀砀愀洀瀀氀攀猀⸀㰀戀爀㸀ഀഀ
You can find it using this url:<br>਍㰀戀爀㸀ഀഀ
http://orafaq.com/papers/dissassembling_the_data_block.pdf<br>਍㰀戀爀㸀ഀഀ
If you have browsed through that document, you probably agree that I don't need to spend any more words on bbed.<br>਍㰀戀爀㸀ഀഀ
<br>਍䘀漀爀 渀漀眀Ⰰ 䤀 挀漀渀挀氀甀搀攀 琀栀椀猀 猀椀洀瀀氀攀 渀漀琀攀⸀ 䠀漀瀀攀 礀漀甀 栀愀瘀攀 氀椀欀攀搀 椀琀℀㰀戀爀㸀ഀഀ
਍ഀഀ
਍ ഀഀ
਍㰀戀爀㸀ഀഀ
<br>਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ
਍㰀栀㌀㸀圀栀愀琀 愀戀漀甀琀 爀攀最甀氀愀爀 䈀愀挀欀甀瀀 愀渀搀 刀攀挀漀瘀攀爀礀 琀攀挀栀渀椀焀甀攀猀 㼀㰀⼀栀㌀㸀ഀഀ
਍䠀愀瘀椀渀最 愀 漀瀀琀椀洀愀氀 戀愀挀欀甀瀀⼀爀攀挀漀瘀攀爀礀 瀀漀氀椀挀礀 椀渀 瀀氀愀挀攀Ⰰ 椀猀 琀栀攀 戀攀猀琀 搀攀昀攀渀挀攀 愀最愀椀渀猀琀 㰀䈀㸀愀渀礀㰀⼀䈀㸀 欀椀渀搀 漀昀 挀漀爀爀甀瀀琀椀漀渀Ⰰ㰀戀爀㸀ഀഀ
disk errors, and all sorts of other faults (like human error etc..).<br>਍㰀戀爀㸀ഀഀ
Originally, I had planned a Chapter 4, dealing on Backup and Recovery techniques.<br>਍一漀眀Ⰰ 䤀 琀栀椀渀欀 椀琀✀猀 戀攀琀琀攀爀 琀漀 挀爀攀愀琀攀 愀 㰀䈀㸀猀攀瀀攀爀愀琀攀 搀漀挀甀洀攀渀琀㰀⼀䈀㸀 漀渀 琀栀愀琀 猀甀戀樀攀挀琀Ⰰ 愀渀搀 琀栀甀猀 渀漀琀 椀渀挀氀甀搀攀 椀琀 椀渀 愀 渀漀琀攀 愀戀漀甀琀 ∀戀氀漀挀欀 挀漀爀爀甀瀀琀椀漀渀∀⸀㰀戀爀㸀ഀഀ
<br>਍㰀戀爀㸀ഀഀ
<br>਍ഀഀ
<font face="arial" size=2 color="black">਍ഀഀ
 ਍㰀戀爀㸀       ഀഀ
<br>਍㰀戀爀㸀ഀഀ
</body>਍㰀⼀栀琀洀氀㸀�