LOB Chunks

To continue with the previous test case, I'll insert a large enough LOB so that the change record is splitted into several chunks. To perform that operation :

• Setup the example as described in Streams LCRs and LOBs
• Insert a LOB that is large enough to guaranty it will be deleted
  

What is interesting is less the output than the number of chunks and the size of each chunks.

Insert a large CLOB

The script below create a LOB as a temporary resource and, once built, insert it in SOURCE.T9:

connect source/source

declare
  z clob;
begin
  DBMS_LOB.CREATETEMPORARY(z,true,DBMS_LOB.SESSION);
  for i in 1..20 loop
     dbms_lob.append(z,rpad('Z',1024,'Z'));
  end loop;
  insert into source.T9 values (2,z);
end;
/
commit;

connect strmadmin/strmadmin
set lines 1000
set serveroutput on
exec print_xml_fromq('MYQUEUE');

The result

You'll find below the output that matches the set of LCRs generated by the one only insert:

---------------------------------------------------------
<ROW_LCR xmlns="http://xmlns.oracle.com/streams/schemas/lcr"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://xmlns.oracle.com/streams/schemas/lcr
               http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd">
   <source_database_name>BLACK</source_database_name>
   <command_type>INSERT</command_type>
   <object_owner>SOURCE</object_owner>
   <object_name>T9</object_name>
   <transaction_id>5.5.913</transaction_id>
   <scn>1448259</scn>
   <new_values>
      <new_value>
         <column_name>ID</column_name>
         <data>
     <number>2</number>
         </data>
      </new_value>
      <new_value>
         <column_name>TEXT</column_name>
         <data>
     <varchar2 nil="true">
         </data>
         <lob_information>EMPTY LOB</lob_information>
      </new_value>
   </new_values>
</row_lcr>
---------------------------------------------------------
---------------------------------------------------------
<ROW_LCR xmlns="http://xmlns.oracle.com/streams/schemas/lcr"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://xmlns.oracle.com/streams/schemas/lcr
               http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd">
   <source_database_name>BLACK</source_database_name>
   <command_type>LOB WRITE</command_type>
   <object_owner>SOURCE</object_owner>
   <object_name>T9</object_name>
   <transaction_id>5.5.913</transaction_id>
   <scn>1448259</scn>
   <new_values>
      <new_value>
         <column_name>ID</column_name>
         <data>
     <number>2</number>
         </data>
      </new_value>
      <new_value>
         <column_name>TEXT</column_name>
         <data>
            <varchar2>ZZZ[...]ZZZ</varchar2>
         </data>
         <lob_information>LAST LOB CHUNK</lob_information>
         <lob_offset>1</lob_offset>
      </new_value>
   </new_values>
</row_lcr>
---------------------------------------------------------
---------------------------------------------------------
<ROW_LCR xmlns="http://xmlns.oracle.com/streams/schemas/lcr"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://xmlns.oracle.com/streams/schemas/lcr
               http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd">
   <source_database_name>BLACK</source_database_name>
   <command_type>LOB WRITE</command_type>
   <object_owner>SOURCE</object_owner>
   <object_name>T9</object_name>
   <transaction_id>5.5.913</transaction_id>
   <scn>1448259</scn>
   <new_values>
      <new_value>
         <column_name>ID</column_name>
         <data>
     <number>2</number>
         </data>
      </new_value>
      <new_value>
         <column_name>TEXT</column_name>
         <data>
            <varchar2>ZZZ[...]ZZZ</varchar2>
         </data>
         <lob_information>LAST LOB CHUNK</lob_information>
         <lob_offset>8061</lob_offset>
      </new_value>
   </new_values>
</row_lcr>
---------------------------------------------------------
---------------------------------------------------------
<ROW_LCR xmlns="http://xmlns.oracle.com/streams/schemas/lcr"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://xmlns.oracle.com/streams/schemas/lcr
              http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd">
   <source_database_name>BLACK</source_database_name>
   <command_type>LOB WRITE</command_type>
   <object_owner>SOURCE</object_owner>
   <object_name>T9</object_name>
   <transaction_id>5.5.913</transaction_id>
   <scn>1448259</scn>
   <new_values>
      <new_value>
          <column_name>ID</column_name>
          <data>
      <number>2</number>
          </data>
      </new_value>
      <new_value>
          <column_name>TEXT</column_name>
          <data>
             <varchar2>ZZZ[...]ZZZ</varchar2>
          </data>
          <lob_information>LAST LOB CHUNK</lob_information>
          <lob_offset>16121</lob_offset>
      </new_value>
   </new_values>
</row_lcr>
---------------------------------------------------------
---------------------------------------------------------
<ROW_LCR xmlns="http://xmlns.oracle.com/streams/schemas/lcr"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://xmlns.oracle.com/streams/schemas/lcr
              http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd">
   <source_database_name>BLACK</source_database_name>
   <command_type>LOB TRIM</command_type>
   <object_owner>SOURCE</object_owner>
   <object_name>T9</object_name>
   <transaction_id>5.5.913</transaction_id>
   <scn>1448259</scn>
   <new_values>
      <new_value>
         <column_name>ID</column_name>
         <data>
     <number>2</number>
         </data>
      </new_value>
      <new_value>
         <column_name>TEXT</column_name>
            <data>
        <varchar2 nil="true">
            </data>
         <lob_information>LAST LOB CHUNK</lob_information>
         <lob_operation_size>20480</lob_operation_size>
      </new_value>
   </new_values>
</row_lcr>
---------------------------------------------------------
---------------------------------------------------------
<ROW_LCR xmlns="http://xmlns.oracle.com/streams/schemas/lcr"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://xmlns.oracle.com/streams/schemas/lcr
               http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd">
   <source_database_name>BLACK</source_database_name>
   <command_type>UPDATE</command_type>
   <object_owner>SOURCE</object_owner>
   <object_name>T9</object_name>
   <transaction_id>5.5.913</transaction_id>
   <scn>1448259</scn>
   <old_values>
      <old_value>
         <column_name>ID</column_name>
         <data>
     <number>2</number>
         </data>
      </old_value>
   </old_values>
</row_lcr>
---------------------------------------------------------
No more messages

Read more...