RE: check for (empty?) blocks which leads to ORA-8103 in a datafile?
Date: Tue, 8 Sep 2009 16:18:57 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F1FDD170D_at_AAPQMAILBX02V.proque.st>
How about:
Analyze table <table_name> validate structure cascade; ??
Cascade should automatically check all indexes on each table, in addition to the table itself.
-Mark
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Berger
Sent: Tuesday, September 08, 2009 3:03 PM
To: ORACLE-L
Subject: check for (empty?) blocks which leads to ORA-8103 in a datafile?
Hi List,
I am facing a problem in our DWH database (9.2.0.7):
a query on a partition gives a ORA-8103 (object no longer exists). But I checked last_ddl_time from dba_objects which is 8-Aug-2009.
During a SR (7694226.993) the explanation was 'This block looks like having been overwritten either in-memory or on-disk by a non-oracle block/page.
This does not look like an Oracle error but introduced externally.'
With the suggestion to restore the datafile.
This is a possibility, but I want to check, how many blocks are affected, before I do anything.
dbv did not show any errors
a block dump of one of the blocks is:
*** SESSION ID:(225.49695) 2009-09-07 14:15:04.220
Start dump data blocks tsn: 69 file#: 804 minblk 235529 maxblk 235529
buffer tsn: 69 rdba: 0x00039809 (0/235529)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0x990c type: 0x00=unknown
Hex dump of corrupt header 4 = CORRUPT
Dump of memory from 0x0000000103834A00 to 0x0000000103834A14
103834A00 00020000 00039809 00000000 00000105 [................]103834A10 990C0000 [....]
End dump data blocks tsn: 69 file#: 804 minblk 235529 maxblk 235529 .
So my idea is to loop through dba_extents (block_id + [1..blocks]) and create for every table or partition a 'select rownum from user.table where rownum=dbms_rowid.rowid_create()'
This will give me information about any table block. (I guess)
Does someone knows any method to ckeck all blocks of other objects (e.g. indexes) by accessing them via their rowid?
thnx,
Martin
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 08 2009 - 15:18:57 CDT