Scripts of Creating_Partitions_Steps



1) Take the back of original transform table TM_POSITION_HCC_ASSIGNMENT in transform schema.

Query :
create table tm_position_hcc_assign_bkp as
select * from tm_position_hcc_assignment;

2) Drop the original table

Query :
drop table tm_position_hcc_assignment;

3) Recreate the original table with partitions. Use the below definition query

Query:
CREATE TABLE "EDW_TRANSFORM"."TM_POSITION_HCC_ASSIGNMENT"
  (
    "POSITION_HCC_ASSIGN_KEY" NUMBER NOT NULL ENABLE,
    "HEALTH_CARE_CLIENT_KEY"  NUMBER NOT NULL ENABLE,
    "POSITION_KEY"            NUMBER NOT NULL ENABLE,
    "IS_VALID_FROM_KEY"       NUMBER NOT NULL ENABLE,
    "IS_VALID_TO_KEY"         NUMBER NOT NULL ENABLE,
    "IS_VALID_FROM" DATE NOT NULL ENABLE,
    "IS_VALID_TO" DATE NOT NULL ENABLE,
    "MARKET_CODE" VARCHAR2(20 CHAR),
    "RUN_ID"      NUMBER NOT NULL ENABLE,
    CONSTRAINT "XPKTM_POSITION_HCC_ASSIGNMENT" PRIMARY KEY ("POSITION_HCC_ASSIGN_KEY") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ENABLE,
    CONSTRAINT "R_972" FOREIGN KEY ("POSITION_KEY") REFERENCES "EDW_TRANSFORM"."TD_POSITION" ("POSITION_KEY") ENABLE,
    CONSTRAINT "R_973" FOREIGN KEY ("HEALTH_CARE_CLIENT_KEY") REFERENCES "EDW_TRANSFORM"."TD_HEALTH_CARE_CLIENT" ("HEALTH_CARE_CLIENT_KEY") ENABLE,
    CONSTRAINT "R_974" FOREIGN KEY ("IS_VALID_FROM_KEY") REFERENCES "EDW_TRANSFORM"."TD_DATE" ("DATE_KEY") ENABLE,
    CONSTRAINT "R_975" FOREIGN KEY ("IS_VALID_TO_KEY") REFERENCES "EDW_TRANSFORM"."TD_DATE" ("DATE_KEY") ENABLE
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "EDW_TRANSFORM_DATA" PARTITION BY RANGE
  (
    "IS_VALID_TO_KEY"
  )
  (
    PARTITION "TM_POSITION_20130101" VALUES LESS THAN (20130331) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20130401" VALUES LESS THAN (20130631) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20130701" VALUES LESS THAN (20130931) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20131001" VALUES LESS THAN (20131231) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20140101" VALUES LESS THAN (20140331) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20140401" VALUES LESS THAN (20140631) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20140701" VALUES LESS THAN (20140931) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20141001" VALUES LESS THAN (20141231) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20150101" VALUES LESS THAN (20150331) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20150401" VALUES LESS THAN (20150631) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20150701" VALUES LESS THAN (20150931) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20151001" VALUES LESS THAN (20151231) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20160101" VALUES LESS THAN (20160331) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20160401" VALUES LESS THAN (20160631) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20160701" VALUES LESS THAN (20160931) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "TM_POSITION_20161001" VALUES LESS THAN (20161231) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA" ,
    PARTITION "DEF" VALUES LESS THAN (99991232) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EDW_TRANSFORM_DATA"
    ) PARALLEL ENABLE ROW MOVEMENT ;
   

4) Insert the rows in the table using the back table.Use the below query:

Query:
Insert into TM_POSITION_HCC_ASSIGNMENT
Select * from tm_position_hcc_assign_bkp;
Commit;

5) Grant the permission to the table to access from central and control schema

Query:
grant all on TM_POSITION_HCC_ASSIGNMENT to EDW_CENTRAL;
GRANT ALL ON "TM_POSITION_HCC_ASSIGNMENT" TO "EDW_CONTROL";

6) Gather stats on the table

Query:
EXEC DBMS_STATS.GATHER_TABLE_STATS('EDW_TRANSFORM','TM_POSITION_HCC_ASSIGNMENT', estimate_percent=> 1);
Commit;