Team:

what could be the reason for this error ORA-29491 in this below demo.

demo@PDB1> create table t
  2  nologging
  3  tablespace users
  4  as
  5  select *
  6  from demo.some_sample_table
  7  where rownum <=1000;

Table created.

demo@PDB1> exec dbms_parallel_execute.create_Task('DEMO_TASK');

PL/SQL procedure successfully completed.

demo@PDB1> begin
  2     dbms_parallel_execute.create_chunks_by_rowid(
  3             task_name=>'DEMO_TASK',
  4             table_owner=>'demo',
  5             table_name=>'t',
  6             by_row=>false,
  7             chunk_size=>1000);
  8  end;
  9  /
ORA-01403: no data found
begin
*
ERROR at line 1:
ORA-29491: invalid table for chunking
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 28
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 14
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 120
ORA-06512: at line 2

did a 10046 trace - it has this sql in it

SELECT T.IOT_TYPE, T.CLUSTER_NAME 
FROM
 SYS.ALL_TABLES T WHERE T.OWNER = :B2 AND T.TABLE_NAME = :B1 

running that sql here -it gives output like this

demo@PDB1> set null ?
demo@PDB1> select iot_type,cluster_name
  2  from dba_tables
  3  where owner = user
  4  and table_name ='T';

IOT_TYPE     CLUSTER_NAME
------------ --------------------------------
?            ?

demo@PDB1> select count(*) from t;

  COUNT(*)
----------
      1000

Kindly let us know what are we missing here. this is on Oracle 18c (18.10)

create table t
tablespace users
as
select *
from all_objects
where rownum <=1000;

exec dbms_parallel_execute.create_Task('DEMO_TASK');

begin
   dbms_parallel_execute.create_chunks_by_rowid(
           task_name=>'DEMO_TASK',
           table_owner=>user,
           table_name=>'t',
           by_row=>false,
           chunk_size=>1000);
end;
/

ORA-29491: invalid table for chunking

begin
   dbms_parallel_execute.create_chunks_by_rowid(
           task_name=>'DEMO_TASK',
           table_owner=>user,
           table_name=>'T', --uppercase
           by_row=>false,
           chunk_size=>1000);
end;
/

PL/SQL procedure successfully completed.

thanks.

But how did you figured that it is due the lower case of table name? what tool you use to get that error identified?

Hello Rajeshwaran,

I enabled SQL trace with bind variables for my session:

alter session set events 'sql_trace bind=true';

Then, I ran the problem block that generated the following lines in the trace file:

PARSING IN CURSOR #139748923335120 len=99 dep=1 uid=86 oct=3 lid=86 tim=525089281 hv=3557751834 ad='6748c618' sqlid='8ujrdwba0xz0u'
SELECT T.IOT_TYPE, T.CLUSTER_NAME FROM SYS.ALL_TABLES T WHERE T.OWNER = :B2 AND T.TABLE_NAME = :B1
END OF STMT
BINDS #139748923335120:

 Bind#0
  oacdty=01 mxl=32(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=873 siz=64 off=0
  kxsbbbfp=7f19d500cfc0  bln=32  avl=04  flg=05
  value="demo"
 Bind#1
  oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=873 siz=0 off=32
  kxsbbbfp=7f19d500cfe0  bln=32  avl=01  flg=01
  value="t"
EXEC #139748923335120:c=284,e=284,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3098175566,tim=525089514
FETCH #139748923335120:c=12,e=11,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=1,plh=3098175566,tim=525089552
CLOSE #139748923335120:c=1,e=1,dep=1,type=3,tim=525089619

There are a couple of things that I would like to point out:
1. bind variable values are in lower-case:
value="demo"
value="t"
2. The FETCH line has zero rows: r=0

The first point shows what bind variable values were used to run that query.
The second point shows that the query returned no rows.
Given all of that, we can see how we can amend that query to make it return a row - both the schema and the table name should be in upper-case.

An errorstack trace event could be another option but not in this specific example. Oracle just raises no_data_found in PL/SQL without a 'SELECT INTO' throwing an error.

Kind Regards,
Mikhail Velikikh.

Mikhail Velikikh - thanks for the above details, it helps.

Team:

is this a correct approach to chunk a specific partition in a table? Kindly advice.

demo@XEPDB1> create table t
  2  partition by list( x )
  3  (  partition p1 values (1) ,
  4     partition p2 values (2) ,
  5     partition p3 values (3) ,
  6     partition p4 values (4) ,
  7     partition p5 values (5) )
  8  as
  9  select a.*, mod(rownum,5)+1 as x
 10  from all_objects a,
 11       all_users
 12  where rownum <=100000;

Table created.

demo@XEPDB1> select min(rid) as start_id, max(rid) as end_id
  2  from (
  3  select rowid rid, ntile(5) over(order by rowid) nt
  4  from t partition(p1)
  5      )
  6  group by nt
  7  /

START_ID           END_ID
------------------ ------------------
AAATw9AAMAAAOISAAA AAATw9AAMAAAOJUAAu
AAATw9AAMAAAOJUAAv AAATw9AAMAAAOKaAAA
AAATw9AAMAAAOLhAAC AAATw9AAMAAAOMpAAD
AAATw9AAMAAAOMpAAE AAATw9AAMAAAONyAAC
AAATw9AAMAAAOKaAAB AAATw9AAMAAAOLhAAB

demo@XEPDB1> exec dbms_parallel_execute.create_task('DEMO_TASK');

PL/SQL procedure successfully completed.

demo@XEPDB1> declare
  2     l_sql long;
  3  begin
  4     l_sql :=q'# select min(rid) as start_id, max(rid) as end_id
  5                             from (
  6                             select rowid rid, ntile(5) over(order by rowid) nt
  7                             from t partition(p1)
  8                                      )
  9                             group by nt #';
 10     dbms_parallel_execute.create_chunks_by_SQL(
 11             task_name=>'DEMO_TASK',
 12             sql_stmt=>l_sql,
 13             by_rowid=>true);
 14  end;
 15  /

PL/SQL procedure successfully completed.

demo@XEPDB1> select status, count(*)
  2  from user_parallel_execute_chunks
  3  group by status ;

STATUS                 COUNT(*)
-------------------- ----------
UNASSIGNED                    5

SQL>
SQL> create table t
  2      partition by list( x )
  3      (  partition p1 values (1) ,
  4         partition p2 values (2) ,
  5         partition p3 values (3) ,
  6         partition p4 values (4) ,
  7         partition p5 values (5) )
  8      as
  9      select a.*, mod(rownum,5)+1 as x
 10     from all_objects a,
 11          ( select 1 from dual connect by level <= 100 )
 12     where rownum <=2000000;

Table created.

SQL>
SQL>
SQL>     select o.data_object_id, e.relative_fno, e.block_id, e.blocks
  2      from dba_extents e
  3         , dba_objects o
  4      where e.owner = o.owner
  5      and e.segment_name = o.object_name
  6      and nvl(e.partition_name, '"') = nvl(o.subobject_name, '"')
  7      and e.segment_type = o.object_type
  8      and e.segment_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
  9      and e.owner = user
 10      and e.segment_name = 'T'
 11      and e.partition_name = 'P1';

DATA_OBJECT_ID RELATIVE_FNO   BLOCK_ID     BLOCKS
-------------- ------------ ---------- ----------
        115970           64     619392       1024
        115970           64     624512       1024
        115970           64     629632       1024
        115970           64     634752       1024
        115970           64     639872       1024
        115970           64     645760       1024
        115970           64     650880       1024
        115970           64     656000       1024

8 rows selected.

SQL> select
  2   dbms_rowid.rowid_create(
  3        rowid_type=>1
  4       ,object_number=>data_object_id
  5       ,relative_fno=>relative_fno
  6       ,block_number=>start_block_id
  7       ,row_number=>0
  8    ) start_rowid,
  9   dbms_rowid.rowid_create(
 10        rowid_type=>1
 11       ,object_number=>data_object_id
 12       ,relative_fno=>relative_fno
 13       ,block_number=>end_block_id
 14       ,row_number=>32767
 15    ) end_rowid
 16  from
 17    (
 18      select o.data_object_id, e.relative_fno, e.block_id start_block_id, e.block_id+e.blocks-1 end_block_id
 19      from dba_extents e
 20         , dba_objects o
 21      where e.owner = o.owner
 22      and e.segment_name = o.object_name
 23      and nvl(e.partition_name, '"') = nvl(o.subobject_name, '"')
 24      and e.segment_type = o.object_type
 25      and e.segment_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 26      and e.owner = user
 27      and e.segment_name = 'T'
 28      and e.partition_name = 'P1'
 29    );

START_ROWID        END_ROWID
------------------ ------------------
AAAcUCABAAACXOAAAA AAAcUCABAAACXd/H//
AAAcUCABAAACYeAAAA AAAcUCABAAACYt/H//
AAAcUCABAAACZuAAAA AAAcUCABAAACZ9/H//
AAAcUCABAAACa+AAAA AAAcUCABAAACbN/H//
AAAcUCABAAACcOAAAA AAAcUCABAAACcd/H//
AAAcUCABAAACdqAAAA AAAcUCABAAACd5/H//
AAAcUCABAAACe6AAAA AAAcUCABAAACfJ/H//
AAAcUCABAAACgKAAAA AAAcUCABAAACgZ/H//

8 rows selected.

SQL>