RE: Extent allocation time

Steve Adams provided me with this answer a few years back.      

If you know which logfile to look in, the following command should do it.

        alter system dump logfile '/some/logfile' layer 14 opcode 4;

That will give you a trace file with 19 lines for each extent allocation recorded in that logfile. The timestamp is on the second line, and the RDBA of the extent added is on the second last line after the ADD tag. Here is an example ...

        REDO RECORD - Thread:1 RBA: 0x0000cf.00000011.002c LEN: 0x00dc VLD: 0x01
        SCN: 0x0000.007db024 SUBSCN:  1 03/17/2004 08:50:30
        CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800131 SCN:0x0000.007db024 SEQ:  4 OP:5.1
        ktudb redo: siz: 96 spc: 16234 flg: 0x0022 seq: 0x005a rec: 0x03
                    xid:  0x0008.02a.00000089
        ktubu redo: slt: 42 rci: 2 opc: 14.5 objn: 7 objd: 25488 tsn: 4
        Undo type:  Regular undo       Undo type:  Last buffer split:  No
        Tablespace Undo:  Yes
                     0x00000000
        kteopu undo - undo operation on extent map
               segdba: 0x1000029  class: 4  mapdba:0x1000029  offset: 2
         rbr extent - dba: 0x100002d  nbk: 0x4
        kteop redo - redo operation on extent map
           DELETE: entry:1
           shift back: dba:0x0 len:0
           SETSTAT: exts:1 blks:3 lastmap:0x0 mapcnt:0
        CHANGE #2 TYP:0 CLS: 4 AFN:4 DBA:0x01000029 SCN:0x0000.007db024 SEQ:  7 OP:14.4
        kteop redo - redo operation on extent map
           ADD: dba:0x100002d len:4 at offset:1
           SETSTAT: exts:2 blks:7 lastmap:0x0 mapcnt:0

There are other extent management operations that also use the 14.4 opcode, so don't be confused if you see a few bits of the other stuff as well.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Goulet, Dick Sent: Thursday, October 20, 2005 1:09 PM To: ora_forum_at_yahoo.com; oracle-l_at_freelists.org Subject: RE: Extent allocation time

None that I'm aware of.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ora_forum Sent: Thursday, October 20, 2005 1:05 PM To: oracle-l_at_freelists.org
Subject: Extent allocation time

Hi All!  

Any way I can get time when an extent was allocated for the table? One of your tbs runs out of space and I need to do some research.  

Thanks.
George.

Yahoo! <http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=36035/*http://music.yahoo.com/unlimited/> Music Unlimited - Access over 1 million songs. Try it free.

--
http://www.freelists.org/webpage/oracle-l