I am trying to recreate the process “CREATE_CHUNKS_BY_ROWID”
https://oracle-base.com/articles/11g/dbms_parallel_execute_11gR2#create_a_task
but failed.
I follow the following steps:
CONN / AS SYSDBA
GRANT CREATE JOB TO test;
/
CONN test/test
/
DROP TABLE test_tab;
/
CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
num_col NUMBER,
session_id NUMBER,
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);
/
INSERT /*+ APPEND */ INTO test_tab
SELECT level,
'Description for ' || level,
CASE
WHEN MOD(level, 5) = 0 THEN 10
WHEN MOD(level, 3) = 0 THEN 20
ELSE 30
END,
NULL
FROM dual
CONNECT BY level <= 500000;
COMMIT;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'TEST_TAB', cascade => TRUE);
/
BEGIN
DBMS_PARALLEL_EXECUTE.create_task (task_name => 'test_task');
END;
When doing this step
BEGIN
DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => 'test_task',
table_owner => 'TEST',
table_name => 'TEST_TAB',
by_row => TRUE,
chunk_size => 10000);
END;
i get the following errors:
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 126
ORA-06512: at line 2
29491. 00000 – “invalid table for chunking”
*Cause: An attempt was made to chunk a table by ROWID, but the table was not a physical table or the table was an IOT.
physical table or the table is an IOT.
*Action: Use a table which has physical ROWID.
2