Sunday, October 11, 2009

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

No comments:

Post a Comment

Post a Comment