-- Table MEDIA.TABLE1
-- PARTITION by DATE_KEY
-- Retention 6 Month
CREATE TABLE MEDIA.TABLE1
(
MEDIA_KEY NUMBER(38) NOT NULL ,
SURFACE VARCHAR2(3) NOT NULL ,
DATE_KEY NUMBER(38) NOT NULL,
TIME_KEY NUMBER(38) NOT NULL,
LOCATION_KEY NUMBER(38) NOT NULL,
SHIFT_KEY NUMBER(38),
MEDIA_LOT VARCHAR2(32),
MEDIA_LOT_SLOT NUMBER,
TEST_DATE DATE NOT NULL,
CERT_MEDIA_KEY NUMBER(38)NOT NULL,
AUDIT_TYPE VARCHAR(10),
PROD_GRP2 VARCHAR(12),
AUDIT_TOOL_ID VARCHAR(12),
SOURCE_WORK_CELL VARCHAR(12),
DATA_SOURCE VARCHAR(12),
RECIPE VARCHAR(64),
V8DZ NUMBER,
ETL_LOAD_DATE DATE NOT NULL,
ETL_SOURCE_NAME VARCHAR2(64)
)
IF TIME_KEY is NUMBER format
TABLESPACE MEDIA_FACT_DP_A01 PARTITION BY RANGE
(
DATE_KEY
)
(
PARTITION P20170331 VALUES LESS THAN (20170331) TABLESPACE MEDIA_FACT_DP_A01,
PARTITION P20170430 VALUES LESS THAN (20170430) TABLESPACE MEDIA_FACT_DP_A01
)
compress ENABLE ROW MOVEMENT ;
OR IF TIME_KEY is DATE format
TABLESPACE MEDIA_FACT_DP_A01 PARTITION BY RANGE
(
DATE_KEY
)
(
PARTITION P20131130 VALUES LESS THAN(TO_DATE(' 2013-11-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "QPM_MP_A01" NOCOMPRESS,
PARTITION P20131231 VALUES LESS THAN(TO_DATE(' 2013-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "QPM_MP_A01" NOCOMPRESS
)
compress ENABLE ROW MOVEMENT ;
COMMENT ON COLUMN MEDIA.TABLE1.SURFACE is 'measured surface';
COMMENT ON COLUMN MEDIA.TABLE1.MEDIA_LOT is 'media process lot id';
COMMENT ON COLUMN MEDIA.TABLE1.MEDIA_LOT_SLOT is 'media process lot slot';
COMMENT ON COLUMN MEDIA.TABLE1.TEST_DATE is 'test timestamp';
COMMENT ON COLUMN MEDIA.TABLE1.AUDIT_TYPE is 'data type';
COMMENT ON COLUMN MEDIA.TABLE1.PROD_GRP2 is 'media product name';
COMMENT ON COLUMN MEDIA.TABLE1.AUDIT_TOOL_ID is 'audit tool id';
COMMENT ON COLUMN MEDIA.TABLE1.SOURCE_WORK_CELL is 'sample source work cell';
COMMENT ON COLUMN MEDIA.TABLE1.DATA_SOURCE is 'sample source operation';
COMMENT ON COLUMN MEDIA.TABLE1.RECIPE is 'audit recipe';
COMMENT ON COLUMN MEDIA.TABLE1.V8DZ is 'data zone count';
CREATE INDEX MEDIA.MEDIA_SPC_CANDELA_etl_dt_ix
ON MEDIA.TABLE1
( etl_load_date )
local compress tablespace MEDIA_I_A02;
CREATE INDEX MEDIA.MEDIA_SPC_CANDELA_CERT_ix
ON MEDIA.TABLE1
( CERT_MEDIA_KEY )
local compress tablespace MEDIA_I_A02;
CREATE BITMAP INDEX MEDIA.MEDIA_SPC_CANDELA_DATE_BIX
ON MEDIA.TABLE1
( date_key )
local compress tablespace MEDIA_I_A02;
CREATE BITMAP INDEX MEDIA.MEDIA_SPC_CANDELA_LOCATION_bix
ON MEDIA.TABLE1
( LOCATION_KEY )
local tablespace MEDIA_I_A02;
-- Constraints for MEDIA.TABLE1
ALTER TABLE MEDIA.TABLE1
ADD CONSTRAINT MEDIA_SPC_CANDELA_PK PRIMARY KEY (MEDIA_KEY,SURFACE,DATE_KEY,TIME_KEY)
USING INDEX
local tablespace MEDIA_I_A02;
GRANT SELECT ON MEDIA.TABLE1 TO CDA;
GRANT SELECT ON MEDIA.TABLE1 TO CDA2;
GRANT SELECT ON MEDIA.TABLE1 TO REPORTER;
GRANT SELECT ON MEDIA.TABLE1 TO REPORTERMD;
GRANT SELECT ON MEDIA.TABLE1 TO MANUFACTURING_ROLE;
grant INDEX, REFERENCES, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK, ALTER, SELECT, INSERT, UPDATE, DELETE on MEDIA.TABLE1 to ITDBA;
INSERT INTO ITDBA.DELETE_PARAM VALUES
('MEDIA', 'TABLE1','YES','DATE_KEY','MONTHLY',6,3,1,'MEDIA_FACT_DP_A01','YES',NULL, NULL,NULL,NULL,NULL, NULL,NULL);
INSERT INTO itdba.event_log ( owner, table_name, operation, status, last_updated )
SELECT distinct owner, a1.table_name, 'FLIP', 'CLEAR', sysdate
FROM dba_tables a1, dba_tab_partitions b1
WHERE OWNER = 'MEDIA'
AND ( a1.table_name = 'TABLE1')
AND a1.table_name = b1.table_name
AND a1.owner = b1.table_owner
AND NOT EXISTS (
SELECT null
FROM itdba.event_log b1
WHERE a1.owner = b1.owner
AND A1.TABLE_NAME = B1.TABLE_NAME);
commit;
___________________________________________________________________________
To run the flip partition manually
login to edw
Unix
okserver1:okpedw1:/u01/oracle/admin/okpedw1/scripts/dba/CUSTOM/boeys >cds
okserver1:okpedw1:/u01/oracle/admin/okpedw1/scripts/dba/sql >cd ../plsql
okserver1:okpedw1:/u01/oracle/admin/okpedw1/scripts/dba/plsql >
ls -ltr run*
run_flip.sql
this is the sql
okserver1:okpedw1:/u01/oracle/admin/okpedw1/scripts/dba/plsql >msql
okserver1.okpedw1.ITDBA> @run_flip.sql
Enter value for table_owner: MEDIA
Enter value for table_name: TABLE1
REM ***** [16-MAR-2017 01:23:41] Processing MEDIA.TABLE1
REM Enabled_Flag = YES
ALTER TABLE MEDIA.TABLE1 ADD PARTITION P20170321 VALUES LESS THAN ( 20170321 ) TABLESPACE MEDIA_FACT_DP_A01;
ALTER TABLE MEDIA.TABLE1 ADD PARTITION P20170322 VALUES LESS THAN ( 20170322 ) TABLESPACE MEDIA_FACT_DP_A01;
okserver1'.'_connect_identifier'.'_user>
It will give you a result
copy and paste the alter table statement
OR run this "run_flip.run"
@run_flip.run