0

I made Redefining Tables Online on a table . It worked fine but later when I tried to truncate the table I got this error .

ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "CURRENT_SCN": invalid identifier.

also I get the same error when I move the table:

 alter table tab1 move;

what is the reason of the error ? If you need more information please ask me.

6
  • Is there a materialized view log on the table? Can you remove it? Commented Jun 2, 2015 at 8:43
  • @BalazsPapp I run this SELECT * FROM all_mviews; and I didnt find on the table tab1 ...
    – moudiz
    Commented Jun 2, 2015 at 9:05
  • Query ALL_MVIEW_LOGS, not ALL_MVIEWS. Commented Jun 2, 2015 at 9:14
  • I found it like that MLOG$_TAB1 .. how to drop it ? this didnt worked Drop materialized view MLOG$_TAB1 @BalazsPapp
    – moudiz
    Commented Jun 2, 2015 at 9:19
  • DROP MATERIALIZED VIEW LOG ON TAB1; Commented Jun 2, 2015 at 9:28

1 Answer 1

2

Performing online redefinition creates materialized view log on the source table, that is where DML changes are tracked during the redefinition process.

Finishing the online redefinition gracefully (finish_redef_table or abort_redef_table) removes the materialized view log automatically, otherwise it will stay there.

Truncating the table with an existing materialized view works with the following syntax:

truncate table tab1 purge materialized view log;

Or just simply drop the materialized view log before.

Without specifying the above, truncate fails because of a recursive query on SYS.MLOG$, as that table does not have the column CURRENT_SCN (version <= 11.2). It is fixed from 12.1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.