This discussion is archived
0 Replies Latest reply: May 14, 2010 8:45 AM by jfernandez RSS

ILM Assistant Problem

jfernandez Newbie
Currently Being Moderated
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;
/



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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points