Home » RDBMS Server » Performance Tuning » Partitioning with ONline Redefinition
Partitioning with ONline Redefinition [message #215762] Tue, 23 January 2007 16:32 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

I am trying to do partitioning online with DBMS_redefinition
here is where i stucked, before starting i just created a copy of table using
create table proc_bat_copy as select * from proc_bat

then i issed
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('TEST_SCHEMA','PROC_BAT');
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_SCHEMA','PROC_BAT'); END;

*
ERROR at line 1:
ORA-12091: cannot online redefine table "TEST_SCHEMA"."PROC_BAT" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 247
ORA-06512: at line 1


SQL> select * from tab where tname like '%PROC_BAT%';

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PROC_BAT_COPY                  TABLE
PROC_BAT_VIEW                  VIEW
PROC_BAT                       TABLE

I don't see any Materialized log or table based on my main table why it still shows this error.

Thanks
Re: Partitioning with ONline Redefinition [message #215763 is a reply to message #215762] Tue, 23 January 2007 16:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
complete solution described in Note:177408.1

Solution Description:
=====================

Remove the snapshot log and materialized view created during the unsuccessfull
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure:

SQL> select log_table from user_snapshot_logs;
Re: Partitioning with ONline Redefinition [message #215765 is a reply to message #215763] Tue, 23 January 2007 16:45 Go to previous message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks for reply

This worked with dbms_redefinition.abort_redef_table



Thanks
Previous Topic: Does view change table_plan for select stmts. on base table
Next Topic: Append hint, Truncate, Global Temp Table
Goto Forum:
  


Current Time: Wed Nov 27 03:28:21 CST 2024