| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance issue while loading large amount of data
Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA7032B699A_at_lnewton.leeds.lfs.co.uk>...
> Hi Richard,
>
> nice bedtime reading there - thanks, I need to look into this stuff in
> more detail. (If Niall wants to call me a geek, then so be it !)
>
> A quick question for you, I have a couple of rows in a table which are
> giving the old 'integer overflow' error from time to time. I suspect the
> problem is data but I'm not sure. I was wondering how I can get from a
> rowid to a file & block number ready for a dump. Can it be done ?
>
> I'm on 64bit 9iR2 on HPUX 11 64 bit with 8174 client on Win NT4.
>
Hi Norman,
Absolutely !!
I'll show you a quick demo of how to do it (cost will be having to put up with a few more Bowie references ;)
BTW, I'm posting this from google at work so I'm not too sure how it's going to be formatted.
SQL> create table bowie_stuff (album varchar2(30), year number, rating varchar2(30));
Table created.
SQL> insert into bowie_stuff values ('Man Who Sold The World', 1970, 'Bloody Good !!');
1 row created.
SQL> insert into bowie_stuff values ('Diamond Dogs', 1974 , 'Brilliant');
1 row created.
SQL> insert into bowie_stuff values ('Outside', 1995 , 'Underrated Masterpiece');
1 row created.
SQL> commit;
Commit complete.
Just quickly created a little table so we can easily find and recognise a referenced row.
SQL> select album, dbms_rowid.rowid_to_absolute_fno(rowid, 'BOWIE',
'BOWIE_STUFF'), dbms_rowid.rowid
_block_number(rowid), dbms_rowid.rowid_row_number(rowid) from
bowie_stuff where album like '%Dogs%';
ALBUM
3
125427
1
The dbms_rowid package has a whole heap of useful functions for formating the rowid for us.
The rowid_to_absolute_fno function will give us the datafile number we
need. Providing you have less than 1022
datafiles, the rowid_relative_number (which is what's actually stored
in the rowid) would suffice but the
absolute_fno is safest. Note it requires the rowid as well as the
schema and the object name.
The rowid_block_number function will give us the block number and
importantly the rowid_row_number
function will give us the row directory slot number so we can find the
row of interest within the block.
OK, we have all we need to dump the block of interest.
SQL> alter system dump datafile 3 block 125427;
System altered.
My comments prefixed with **
Start dump data blocks tsn: 2 file#: 3 minblk 125427 maxblk 125427 buffer tsn: 2 rdba: 0x00c1e9f3 (3/125427) scn: 0x0000.000985a0 seq: 0x01 flg: 0x02 tail: 0x85a00601 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x00c1e9f3
Object id on Block? Y
seg/obj: 0x6204 csc: 0x00.98581 itc: 1 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag LckScn/Fsc
data_block_dump
flag=----------- ntab=1 nrow=3 frre=-1 fsbo=0x18 fseo=0x1f47 avsp=0x1f2f tosp=0x1f2f 0xe:pti[0] nrow=3 offs=0 0x12:pri[0] offs=0x1f8b 0x14:pri[1] offs=0x1f6d 0x16:pri[2] offs=0x1f47
block_row_dump:
tab 0, row 0, @0x1f8b
tl: 45 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [22]
4d 61 6e 20 57 68 6f 20 53 6f 6c 64 20 54 68 65 20 57 6f 72 6c 64
col 1: [ 3] c2 14 47
col 2: [14] 42 6c 6f 6f 64 79 20 47 6f 6f 64 20 21 21
tab 0, row 1, @0x1f6d
tl: 30 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [12] 44 69 61 6d 6f 6e 64 20 44 6f 67 73 col 1: [ 3] c2 14 4b col 2: [ 9] 42 72 69 6c 6c 69 61 6e 74tab 0, row 2, @0x1f47
col 0: [ 7] 4f 75 74 73 69 64 65 col 1: [ 3] c2 14 60 col 2: [22]
Hope this is of use.
Cheers
Richard Received on Wed Jan 15 2003 - 21:09:05 CST
 |
 |