Re: phantom records?
- From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- To: "Uzzell, Stephan" <SUzzell@xxxxxxxxxx>
- Date: Thu, 11 Nov 2010 08:35:26 +0100
Resent due to overquoting ...
On Thu, Nov 11, 2010 at 8:32 AM, Stefan Knecht <knecht.stefan@xxxxxxxxx>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, @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@xxxxxxxx
>
> 10046 Consulting GmbH
> Schwarzackerstrasse 29
> CH-8304 Wallisellen
> Switzerland
>
> Phone +41-(0)8400-10046
> Cell +41 (0) 79 571 36 27
> info@xxxxxxxx
> http://www.10046.ch
>
> =========================
>
>
> On Wed, Nov 10, 2010 at 10:35 PM, Uzzell, Stephan <SUzzell@xxxxxxxxxx>wrote:
>
>> Hi Stefan,
>>
>>
>>
>> Glad I’m not the only one that thinks this looks strange ;-)
>>
>>
>>
>> I’ve 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 they’d 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.
>>
>>
>>
>> I’ve 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
>>
>>
>>
>
Other related posts:
