Re: phantom records?

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Thu, 11 Nov 2010 08:35:26 +0100
Message-ID: <AANLkTi=Tyg9nwBJvJPYQZ54LCO=sjLm_wsv=oqZZ=751_at_mail.gmail.com>



Resent due to overquoting ...

On Thu, Nov 11, 2010 at 8:32 AM, Stefan Knecht <knecht.stefan_at_gmail.com>wrote:

> Stephan
>
> This might help:
>
> SQL> create table t (x varchar2(20), y varchar2(20));
>
>
>
>
> Table created.
>
> SQL> insert into t values ('AA','FOO12345');
>
>
>
>
> 1 row created.
>
> SQL> insert into t values ('BB','BAR12345');
>
>
>
>
> 1 row created.
>
> SQL> commit;
>
>
>
>
> Commit complete.
>
> 1* select dbms_rowid.rowid_relative_fno(t.rowid) "FILE#",
> dbms_rowid.rowid_block_number(t.rowid) "BLOCK",
> dbms_rowid.rowid_row_number(t.rowid) "ROW#_IN_BLOCK", x,y from t
>
> FILE# BLOCK ROW#_IN_BLOCK X Y
> ---------- ---------- ------------- --------------------
> --------------------
> 1 84473 0 AA FOO12345
> 1 84473 1 BB BAR12345
>
> so here we're intereted in row number 0 in file 1, block 84473
>
> SQL> alter session set tracefile_identifier='FOO';
>
> Session altered.
>
> SQL> select value from v$diag_info where name='Default Trace File';
>
>
>
>
> VALUE
>
> --------------------------------------------------------------------------------
> /u00/app/oracle/diag/rdbms/m2/M2/trace/M2_ora_22396_FOO.trc
>
> SQL> !vi /u00/app/oracle/diag/rdbms/m2/M2/trace/M2_ora_22396_FOO.trc
>
>
> In there, search for "block_row_dump"
>
> And find the respective number for your case, here it was row 0:
>
>
> block_row_dump:
> tab 0, row 0, _at_0x1f91
> tl: 15 fb: --H-FL-- lb: 0x1 cc: 2
> col 0: [ 2] 41 41
> col 1: [ 8] 46 4f 4f 31 32 33 34 35
>
> ... all the columns will be listed here
>
> Use that information to look at the data...
>
> SQL> select utl_raw.cast_to_varchar2(replace('41 41',' ')) "COL1",
>
>
>
> 2 utl_raw.cast_to_varchar2(replace('46 4f 4f 31 32 33 34 35','
> ')) "COL2" from dual;
>
>
>
> COL1
>
> --------------------------------------------------------------------------------
> COL2
>
> --------------------------------------------------------------------------------
> AA
> FOO12345
>
>
> This can then give you the possibility to see if you can query the data
> using other column data, in case there's some problems with the column in
> question.
>
> Stefan
>
> =========================
>
> Stefan P Knecht
> CEO & Founder
> s_at_10046.ch
>
> 10046 Consulting GmbH
> Schwarzackerstrasse 29
> CH-8304 Wallisellen
> Switzerland
>
> Phone +41-(0)8400-10046
> Cell +41 (0) 79 571 36 27
> info_at_10046.ch
> http://www.10046.ch
>
> =========================
>
>
> On Wed, Nov 10, 2010 at 10:35 PM, Uzzell, Stephan <SUzzell_at_micros.com>wrote:
>
>> Hi Stefan,
>>
>>
>>
>> Glad Im not the only one that thinks this looks strange ;-)
>>
>>
>>
>> Ive dumped the 2 blocks to tracefiles, but not sure how to read that is
>> there a good guide to it somewhere?
>>
>>
>>
>> As for the select /*+ FULL (g) */ gds_host, I had to give the system
>> back to the users (who knew theyd want to do QA on a QA system?), and since
>> this application checks for things like disabled constraints, I had to
>> delete the record I inserted so I could re-enable the constraint.
>>
>>
>>
>> When I first started investigating this however, I did try a select /*+
>> FULL( gds_hosts ) */ gds_host from gds_hosts thinking this was an index
>> problem. However, I got the same 11 records that way that I got with a
>> straight select.
>>
>>
>>
>> Ive arranged for some more downtime tomorrow, so I can try anything that
>> requires disabling constraints then.
>>
>>
>>
>> *
>> _____________________________________________________________________________
>> *
>>
>> *Stephan Uzzell |** **MICROS Systems, Inc.** *
>>
>>
>>
>> Database Administrator - OPERA Global Technical Services
>>
>> 7031 Columbia Gateway Dr, Columbia, MD 21046 | ( 443.285.8000x2760 | 7443.285.6505
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 11 2010 - 01:35:26 CST

Original text of this message