SQL> set linesize 300
SQL> col name format a60
SQL> select file#,name,bytes from v$datafile;

     FILE# NAME                                                              BYTES
---------- ------------------------------------------------------------ ----------
         1 /data/ora10g/RACDBSTD/system.259.727735501                    429916160
         2 /data/ora10g/RACDBSTD/undotbs1.260.727735547                  209715200
         3 /data/ora10g/RACDBSTD/sysaux.261.727735559                    167772160
         4 /data/ora10g/RACDBSTD/undotbs2.263.727735589                  209715200
         5 /data/ora10g/RACDBSTD/users.264.727735605                       5242880
         6 /data/ora10g/RACDBSTD/TEST01.DBF                               52428800

6 rows selected.

 
SQL> select b.tablespace_name,b.block_size,b.segment_space_management from dba_tablespaces b;

TABLESPACE_NAME                BLOCK_SIZE SEGMEN
------------------------------ ---------- ------
SYSTEM                               8192 MANUAL
UNDOTBS1                             8192 MANUAL
SYSAUX                               8192 AUTO
TEMP                                 8192 MANUAL
UNDOTBS2                             8192 MANUAL
USERS                                8192 AUTO
TEST                                 8192 AUTO

7 rows selected.

SQL> create table MILIATEST
  2  (
  3   ID     NUMBER(10),
  4   NAME   VARCHAR2(2000),
  5   ADRESS VARCHAR2(2000)
  6  ) tablespace test;

Table created.

SQL> create or replace procedure show_space
  2        ( p_segname in varchar2,
  3   p_owner  in varchar2 default user,
  4   p_type   in varchar2 default 'TABLE',
  5   p_partition in varchar2 default NULL )
  6  as
  7     l_total_blocks             number;
  8     l_total_bytes              number;
  9     l_unused_blocks            number;
 10     l_unused_bytes             number;
 11     l_LastUsedExtFileId        number;
 12     l_LastUsedExtBlockId       number;
 13     l_last_used_block          number;
 14     procedure p( p_label in varchar2, p_num in number )
 15     is
 16     begin
 17         dbms_output.put_line( rpad(p_label,40,'.') ||
 18                               p_num );
 19     end;
 20  begin
 21     dbms_space.unused_space
 22     ( segment_owner    => p_owner,
 23       segment_name     => p_segname,
 24       segment_type     => p_type,
 25       partition_name   => p_partition,
 26       total_blocks     => l_total_blocks,
 27       total_bytes      => l_total_bytes,
 28       unused_blocks    => l_unused_blocks,
 29       unused_bytes     => l_unused_bytes,
 30       last_used_extent_file_id => l_LastUsedExtFileId,
 31       last_used_extent_block_id => l_LastUsedExtBlockId,
 32       last_used_block => l_last_used_block );
 33     p( 'Total Blocks', l_total_blocks );
 34     p( 'Total Bytes', l_total_bytes );
 35     p( 'Unused Blocks', l_unused_blocks );
 36     p( 'Unused Bytes', l_unused_bytes );
 37     p( 'Last Used Ext FileId', l_LastUsedExtFileId );
 38     p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
 39     p( 'Last Used Block', l_last_used_block );
 40  end;
 41  /

Procedure created.

SQL> set serverout on;
SQL> exec show_space('MILIATEST');

Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................124
Unused Bytes............................1015808
Last Used Ext FileId....................6
Last Used Ext BlockId...................137
Last Used Block.........................4

PL/SQL procedure successfully completed.

SQL> SQL>
SQL> declare
  2  m_name varchar2(2000):='';
  3  begin
  4  m_name:= 'a2aaaaaaaa';
  5  for j in 1..3 loop
  6  insert into MILIATEST(ID,name) values(j,m_name);
  7  commit;
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> insert into MILIATEST(ID,name) values(4,'a4444aabaa');

1 row created.

SQL> commit;

Commit complete.

SQL> exec show_space('MILIATEST');
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................64
Unused Bytes............................524288
Last Used Ext FileId....................6
Last Used Ext BlockId...................137
Last Used Block.........................64

PL/SQL procedure successfully completed.

SQL> select f,b from (
  2    select dbms_rowid.rowid_relative_fno(rowid) f,
  3           dbms_rowid.rowid_block_number(rowid) b
  4   from MILIATEST) group by f,b order by b;

         F          B
---------- ----------
         6        156

SQL>   select dbms_rowid.rowid_relative_fno(rowid) f,
  2           dbms_rowid.rowid_block_number(rowid) b
  3   from MILIATEST;

         F          B
---------- ----------
         6        156
         6        156
         6        156
         6        156

SQL>
SQL> alter system dump datafile 6 block min 156 block 156 ;
alter system dump datafile 6 block min 156 block 156
                                                 *
ERROR at line 1:
ORA-25115: duplicate BLOCK option specification

SQL> alter system dump datafile 6 block min 156 block max 156 ;

System altered.

SQL> show parameter dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /data/ora10g/admin/RACDBSTD/bd
                                                 ump
core_dump_dest                       string      /data/ora10g/admin/RACDBSTD/cd
                                                 ump
max_dump_file_size                   string      UNLIMITED
shadow_core_dump                     string      partial
user_dump_dest                       string      /data/ora10g/admin/RACDBSTD/ud
                                                 ump
SQL> select dump('a4444aabaa',16) from dual;

DUMP('A4444AABAA',16)
--------------------------------------------
Typ=96 Len=10: 61,34,34,34,34,61,61,62,61,61

 

SQL> select id,name from MILIATEST;

        ID NAME
---------- --------------------
         1 a2aaaaaaaa
         2 a2aaaaaaaa
         3 a2aaaaaaaa
         4 a4444aabaa

SQL>  select dump(6,16) from dual;

DUMP(6,16)
-----------------
Typ=2 Len=2: c1,7

 

准备把 id=4 改为 6

 

BBED> show all
        FILE#           6
        BLOCK#          1
        OFFSET          0
        DBA             0x01800001 (25165825 6,1)
        FILENAME        /data/ora10g/RACDBSTD/TEST01.DBF
        BIFILE          bifile.bbd
        LISTFILE        filelist.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

BBED> set file 6 block 156
        FILE#           6
        BLOCK#          156

 

 

BBED> map /v
 File: /data/ora10g/RACDBSTD/TEST01.DBF (6)
 Block: 156                                   Dba:0x0180009c
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0      
    ub1 type_kcbh                           @0      
    ub1 frmt_kcbh                           @1      
    ub1 spare1_kcbh                         @2      
    ub1 spare2_kcbh                         @3      
    ub4 rdba_kcbh                           @4      
    ub4 bas_kcbh                            @8      
    ub2 wrp_kcbh                            @12     
    ub1 seq_kcbh                            @14     
    ub1 flg_kcbh                            @15     
    ub2 chkval_kcbh                         @16     
    ub2 spare3_kcbh                         @18     

 struct ktbbh, 72 bytes                     @20     
    ub1 ktbbhtyp                            @20     
    union ktbbhsid, 4 bytes                 @24     
    struct ktbbhcsc, 8 bytes                @28     
    b2 ktbbhict                             @36     
    ub1 ktbbhflg                            @38     
    ub1 ktbbhfsl                            @39     
    ub4 ktbbhfnx                            @40     
    struct ktbbhitl[2], 48 bytes            @44     

 struct kdbh, 14 bytes                      @100    
    ub1 kdbhflag                            @100    
    b1 kdbhntab                             @101    
    b2 kdbhnrow                             @102    
    sb2 kdbhfrre                            @104    
    sb2 kdbhfsbo                            @106    
    sb2 kdbhfseo                            @108    
    b2 kdbhavsp                             @110    
    b2 kdbhtosp                             @112    

 struct kdbt[1], 4 bytes                    @114    
    b2 kdbtoffs                             @114    
    b2 kdbtnrow                             @116    

 sb2 kdbr[4]                                @118    

 ub1 freespace[7994]                        @126    

 ub1 rowdata[68]                            @8120   

 ub4 tailchk                                @8188

 

 

找到对应的位置：

 

BBED> find /x 02c105
 File: /data/ora10g/RACDBSTD/TEST01.DBF (6)
 Block: 156              Offsets: 8123 to 8191           Dba:0x0180009c
------------------------------------------------------------------------
 02c1050a 61343434 34616162 61612c01 0202c104 0a613261 61616161 6161612c
 000202c1 030a6132 61616161 61616161 2c000202 c1020a61 32616161 61616161
 61020688 35

 <32 bytes per line>

BBED> modify /x 02c107
 File: /data/ora10g/RACDBSTD/TEST01.DBF (6)
 Block: 156              Offsets: 8123 to 8191           Dba:0x0180009c
------------------------------------------------------------------------
 02c1070a 61343434 34616162 61612c01 0202c104 0a613261 61616161 6161612c
 000202c1 030a6132 61616161 61616161 2c000202 c1020a61 32616161 61616161
 61020688 35

 <32 bytes per line>

BBED> sum apply
Check value for File 6, Block 156:
current = 0xc3fd, required = 0xc3fd

 

 

查看结果：

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> select id,name from MILIATEST;

        ID NAME
---------- --------------------
         1 a2aaaaaaaa
         2 a2aaaaaaaa
         3 a2aaaaaaaa
         6 a4444aabaa