Online Reorg error

[Former Member]

Hi All,

I have Oracle 10G on AIX.One of the table APQD has grown 350GB.We have deleted data from the table.And now we need to reorg the table. Using brtools we are getting the following error.

->

{***********************************************************************************

BR0301W SQL error -12091 at location BrReorgCheck-5, SQL statement:

'BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE (UNAME => '"SAPSR3"', TNAME => '"APQD"', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK); END;'

ORA-12091: cannot online redefine table "SAPSR3"."APQD" with materialized views

ORA-06512: at "SYS.DBMS_REDEFINITION", line 137

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479

ORA-06512: at line 1

BR1111I Reorganization of table SAPSR3.APQD will be skipped

BR0280I BRSPACE time stamp: 2010-08-11 06.45.38

BR1113E All tables have been skipped for reorganization

BR0669I Cannot continue due to previous warnings or errors - you can go back to repeat the last action

************************************************************

->}

I have read the SAP Note 741478 , point 12.:ORA-12091: cannot online redefine table with materialized views

and tried to end the interrupted reorg.But getting the following error

*****************************

SQL> exec dbms_redefinition.abort_redef_table;

BEGIN dbms_redefinition.abort_redef_table; END;

*

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'ABORT_REDEF_TABLE'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

***********************************

Please guide

Regards

Anthony

Edited by: Anthony D'souza on Aug 11, 2010 10:36 AM

Edited by: Anthony D'souza on Aug 11, 2010 10:36 AM

Edited by: Anthony D'souza on Aug 11, 2010 10:37 AM

Edited by: Anthony D'souza on Aug 11, 2010 10:37 AM

Edited by: Anthony D'souza on Aug 11, 2010 10:37 AM

Edited by: Anthony D'souza on Aug 11, 2010 10:38 AM

[Former Member · Accepted solution]

SQL> exec dbms_redefinition.abort_redef_table;

> BEGIN dbms_redefinition.abort_redef_table; END;

>

> *

> ERROR at line 1:

> ORA-06550: line 1, column 7:

> PLS-00306: wrong number or types of arguments in call to 'ABORT_REDEF_TABLE'

> ORA-06550: line 1, column 7:

> PL/SQL: Statement ignored

> [quote}

Obviously you will have to tell Oracle for which table to apply dbms_redefinition.abort_redef_table.

Look for the correct syntax!

I haven't it at hand either, but Google is your friend.

prateek_y,

do you think it is helpful to cut&paste from SAP note 741478?

Anthony told us he already read it ....

regards

[Former Member]

Hi Anthony,

Try to do it with the commandline

brspace -f tbreorg -t apqd -p 4

and if you still face the issue patch up your BRTOOLS to the latest.

FYKI

ORA-12091: cannot online redefine table with materialized views

If a materialized view or a log file for a materialized view already exists for a table, an online reorganization will lead to an ORA-12091 error message. The error occurs if the online reorganization for a table is stopped improperly and you then try to restart the online reorganization. To solve the problem, you must end the interrupted reorganization using DBMS_REDEFINITION.ABORT_REDEF_TABLE. Then you must drop the table <table>#$. You can also use the following BRSPACE command to clean up after old reorganizations:

brspace -u / -f tbreorg -t "*" -a cleanup

All the best !