"Partitioning" without partitioningThis is a featured page

If you want to process your data by parts in the very-very big table without partitions (for example to move your data over dblink or to organize parallel export by EXP or for another reason), you can use this script. With this script you can get first and last rowid of each extent to catch the data of each extent:
select * from IDEEV.CL_DATA where rowid between chartorowid('AABO8MAAEAAAJtRAAA') and chartorowid('AABO8MAAEAAAJtYCAA');
/*
---------------------------------------------------------------------------------------------------------------
Author : Ilya Deev
Description: to organize data access by separate extents through rowid range
----------------------------------------------------------------------------------------------------------------
*/

declare
-- parameters:
p_owner varchar2(30) := 'IDEEV'; --<-- owner
p_object_name
varchar2(30) := 'CL_DATA'; --<-- table name
p_extent_id number := 0; --<-- extent number
-- variables
v_tablespace_name varchar2(30);
v_block_size
number := 8192;
v_object_id
number;
v_file_id
number;
v_block_id
number; -- first block number
v_blocks number; -- number of blocks
v_rowid_first rowid;
v_rowid_second
rowid;
begin
-- object_id
begin
select o.object_id into v_object_id
from dba_objects o
where o.owner = p_owner
and o.object_name = p_object_name
and o.object_type = 'TABLE';
exception
when no_data_found
then dbms_output.put_line('Table not found: '||p_owner||'.'||p_object_name);
return;
end;

-- extent params
begin
select e.file_id, e.block_id, e.blocks, e.tablespace_name
into v_file_id, v_block_id, v_blocks, v_tablespace_name
from dba_extents e
where e.owner = p_owner
and e.segment_name = p_object_name
and e.extent_id = p_extent_id;
exception
when no_data_found
then dbms_output.put_line('Extent '||to_char(p_extent_id)||' of table '||p_owner||'.'||p_object_name||' not found');
return;
end;
-- db blcok size
select block_size into v_block_size
from dba_tablespaces t
where t.tablespace_name = v_tablespace_name;
-- first rowid
v_rowid_first :=dbms_rowid.rowid_create(1, v_object_id, v_file_id, v_block_id, 0);
-- last rowid (last row number is equel to db block size ;) )
v_rowid_second :=dbms_rowid.rowid_create(1, v_object_id, v_file_id, v_block_id + v_blocks - 1, v_block_size);
-- res
dbms_output.put_line('------------------------------------------------------');
dbms_output.put_line(
'owner : '||p_owner );
dbms_output.put_line(
'table name: '||p_object_name );
dbms_output.put_line(
'extent_id: '||p_extent_id);
dbms_output.put_line(
'rowid first: '||rowidtochar(v_rowid_first));
dbms_output.put_line(
'rowid last: '||rowidtochar(v_rowid_second));
dbms_output.put_line(
'-------------------------------------------------------');
dbms_output.put_line(
'select count(*) from '||p_owner||'.'||p_object_name);
dbms_output.put_line(
' where rowid between chartorowid('''||rowidtochar(v_rowid_first)||''') ');
dbms_output.put_line(
' and chartorowid('''||rowidtochar(v_rowid_second)||''');');
dbms_output.put_line(
'-------------------------------------------------------'); exception
when others
then dbms_output.put_line(sqlerrm);
end;
/


ideev
ideev
Latest page update: made by ideev , May 8 2008, 12:50 PM EDT (about this update About This Update ideev Formatting - ideev

2 words added
1 word deleted

view changes

- complete history)
More Info: links to this page
There are no threads for this page.  Be the first to start a new thread.

Related Content

  (what's this?Related ContentThanks to keyword tags, links to related pages and threads are added to the bottom of your pages. Up to 15 links are shown, determined by matching tags and by how recently the content was updated; keeping the most current at the top. Share your feedback on Wetpaint Central.)