0 Replies Latest reply: May 14, 2010 10:45 AM by jfernandez RSS

    ILM Assistant Problem

    jfernandez
      Hi Guys,
      First of all Im having some trouble with the migration scripts. If im not wrong when I have a partition simulated table and the assistant offers me a migration scripts to make the table real partitioned. So I used that scripts but the script fails. I think the script is not correct and its not stating the correct info on the create table. on the part of the partition.

      See:.

      ------------------------------------------------------
      --
      -- Oracle ILM Assistant
      --
      -- Partition Migration Script
      -- Table: "ABM"."ABM_BORS"
      -- Lifecycle Definition: Ciclo Test
      -- Username: ILM
      -- Target Run Date: 2010/05/14 00:00
      --
      -- Tablespaces required:
      --
      -- This script contains the steps to transform an unpartitioned table
      -- using ILM simulated partitions into a new table with actual partitions.
      -- It creates an empty, intermediate table with the necessary partitioning
      -- structure. The later steps to populate the intermediate table and
      -- replace the original table are inside comments because there may be
      -- performance or application issues to consider before implementing them.
      --
      ------------------------------------------------------
      ------------------------------------------------------
      -- Creating the new partitioned table
      ------------------------------------------------------

      CREATE TABLE "ABM"."ABM_BORS1"
      (     "M25_MODEL_ID" VARCHAR2(15),
           "M18_BOR_TYPE_ID" VARCHAR2(30),
           "O06_MASTER_LIST_SETS_ID" VARCHAR2(15),
           "O09_TYPE_CODE" VARCHAR2(3),
           "O09_RESOURCE_ID" VARCHAR2(120),
           "STORE_DETAIL_FLAG" VARCHAR2(3),
           "MAINT_USER_ID" VARCHAR2(11),
           "MAINT_EFFECT_CODE" VARCHAR2(1),
           "MAINT_TIMESTAMP" DATE,
           "CREATION_DATE" DATE,
           "CREATED_BY" NUMBER(15,0),
           "LAST_UPDATE_DATE" DATE,
           "LAST_UPDATED_BY" NUMBER(15,0),
           "LAST_UPDATE_LOGIN" NUMBER(15,0),
           "I02_TAG_SET_ID" VARCHAR2(30),
           "I01_TAG_ID" VARCHAR2(30)
      ) PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
      TABLESPACE "APPS_TS_TX_DATA"
      PARTITION BY RANGE ("CREATION_DATE")
      (

      ------------------------------------------------------
      -- Copying constraints to new partitioned table
      ------------------------------------------------------

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("M25_MODEL_ID" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("M18_BOR_TYPE_ID" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("O06_MASTER_LIST_SETS_ID" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("O09_TYPE_CODE" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("O09_RESOURCE_ID" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("STORE_DETAIL_FLAG" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("CREATION_DATE" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("CREATED_BY" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("LAST_UPDATE_DATE" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("LAST_UPDATED_BY" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("I02_TAG_SET_ID" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" MODIFY ("I01_TAG_ID" NOT NULL ENABLE);

      ALTER TABLE "ABM"."ABM_BORS1" ADD CONSTRAINT "ABM_BORS1_PK" PRIMARY KEY ("M25_MODEL_ID", "M18_BOR_TYPE_ID", "O06_MASTER_LIST_SETS_ID", "O09_TYPE_CODE", "O09_RESOURCE_ID")
      USING INDEX PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
      TABLESPACE "APPS_TS_TX_IDX" ENABLE;

      ------------------------------------------------------
      -- Copying ref constraints to new partitioned table
      ------------------------------------------------------

      ALTER TABLE "ABM"."ABM_BORS1" ADD CONSTRAINT "ABM_BORS1_FK1" FOREIGN KEY ("M18_BOR_TYPE_ID")
           REFERENCES "ABM"."ABM_BOR_TYPES" ("M18_BOR_TYPE_ID") ENABLE NOVALIDATE;

      ALTER TABLE "ABM"."ABM_BORS1" ADD CONSTRAINT "ABM_BORS1_FK2" FOREIGN KEY ("M25_MODEL_ID", "O06_MASTER_LIST_SETS_ID", "O09_TYPE_CODE", "O09_RESOURCE_ID")
           REFERENCES "ABM"."ABM_RESOURCES" ("M25_MODEL_ID", "O06_MASTER_LIST_SETS_ID", "O09_TYPE_CODE", "O09_RESOURCE_ID") ENABLE NOVALIDATE;

      ALTER TABLE "ABM"."ABM_BORS1" ADD CONSTRAINT "ABM_BORS1_FK3" FOREIGN KEY ("I02_TAG_SET_ID", "I01_TAG_ID")
           REFERENCES "ABM"."ABM_TAGS" ("I02_TAG_SET_ID", "I01_TAG_ID") ENABLE NOVALIDATE;

      ------------------------------------------------------
      -- Copying indexes to new partitioned table
      ------------------------------------------------------

      CREATE UNIQUE INDEX "ABM"."ABM_BORS1_PK" ON "ABM"."ABM_BORS1" ("M25_MODEL_ID", "M18_BOR_TYPE_ID", "O06_MASTER_LIST_SETS_ID", "O09_TYPE_CODE", "O09_RESOURCE_ID")
      PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
      TABLESPACE "APPS_TS_TX_IDX" ;

      ------------------------------------------------------
      -- Copying object grants for new partitioned table
      ------------------------------------------------------

      GRANT ALTER ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      GRANT DELETE ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      GRANT INDEX ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      GRANT INSERT ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      GRANT SELECT ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      GRANT UPDATE ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      GRANT REFERENCES ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      GRANT ON COMMIT REFRESH ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      GRANT QUERY REWRITE ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      GRANT DEBUG ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      GRANT FLASHBACK ON "ABM"."ABM_BORS1" TO "APPS" WITH GRANT OPTION;

      ------------------------------------------------------
      -- Populating the new partitioned table with
      -- data from the original table
      ------------------------------------------------------

      insert /*+ append */ into "ABM"."ABM_BORS1"
      select * from "ABM"."ABM_BORS";

      commit;

      begin
      dbms_stats.gather_table_stats('"ABM"', '"ABM_BORS1"',
      NULL, dbms_stats.auto_sample_size);
      end;
      /

      ------------------------------------------------------
      -- Renaming tables to give the new partitioned table
      -- the original name
      ------------------------------------------------------

      alter table "ABM"."ABM_BORS"
      rename to "ABM_BORS13";

      alter table "ABM"."ABM_BORS1"
      rename to "ABM_BORS";

      ------------------------------------------------------
      -- Associate the new partitioned table with
      -- the lifecycle definition
      ------------------------------------------------------

      begin
      ilm_toolkit.ilm_toolkit.add_managed_table('Ciclo Test',
      'ABM','ABM_BORS');
      end;
      /



      -----------------------------------------------------------------------------------------------------------------------------------------------------