3 Replies Latest reply: Jun 21, 2013 9:10 AM by mtefft RSS

    DBMS_REDEFINITION dependent objects issue , please help.....


      Hello Guru's,


      Today I have tried with DBMS_REDEFINITION package, I'm successfully partitioning the existing table, but there are some dependent objects are missing after completing the redefinition package.

      Im Using 11g R2.

      Please assist me , in which stage it went wrong, Actually my table is have around 12 dependent procedures, functions, Synonyms how ever, after completing the redefinition when i query the all_dependancies I m getting only 9 objects with correct status.

      Please suggest me that what happened to remaining 3 dependent objects.  Its a bit urgent.

      Steps I followed:

      1.Created a bkp table of same as existing table A.
      2.EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'A');  ---No errors got.
      3.DBMS_REDEFINITION.start_redef_table(uname      => USER, orig_table => 'A',int_table  => 'A_BKP'); ----No errors
      4. dbms_redefinition.sync_interim_table(
          uname      => USER,       
          orig_table => 'A',
          int_table  => 'A_BKP');  ----No errors got.
      5.Manually Added constraints on A_BKP what ever constraints on A .--- No errors
      6. Manually Added Indexes on A_BKP what ever indexs are there on A columns  ----No errors
      7.EXEC DBMS_STATS.gather_table_stats(USER, 'A_BKP', cascade => TRUE);  ---No errors
          uname      => USER,       
          orig_table => 'A',
          int_table  => 'A_BKP');  ----No errors
      9. Manually Droped A_BKP table
      10. Manually Renamed all constraints and Indexex to the original names  -----No errors
      11. Queried Select count(*) from A;

      Then I queried in all_dependencies, now there is mis match between before partitioned table dependencies and after ,

      Please tell me whats wrong with this steps , its a bit urgent, any fruther steps to be taken ?