-- ==================================================

ALTER TABLE SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB
ADD TIME_PST_VC VARCHAR2(8);

UPDATE SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB
SET TIME_PST_VC = TO_CHAR(TIME_PST, 'HH24:MI');

SELECT constraint_name
FROM user_constraints
WHERE table_name = 'SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB'
  AND constraint_type = 'P';
  
-- note constraint name and use it below
-- make sure you know what is the target PK before deleting it
  
ALTER TABLE SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB
DROP CONSTRAINT PK_SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB;

ALTER TABLE SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB
MODIFY TIME_PST_VC NOT NULL;

ALTER TABLE SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB
RENAME COLUMN TIME_PST TO TIME_PST_OLD;

ALTER TABLE SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB
RENAME COLUMN TIME_PST_VC TO TIME_PST;

-- if the above fails, identify duplicates on the whole row and drop one

ALTER TABLE SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB
ADD CONSTRAINT PK_SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB
PRIMARY KEY (METADATA_COMPID, METADATA_TIMESTAMP, EXECUTION_DATE_PST, TIME_PST);

ALTER TABLE SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB
DROP COLUMN TIME_PST_OLD;

-- ==================================================

-- ==================================================

ALTER TABLE SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB
ADD TIME_PST_VC VARCHAR2(8);

UPDATE SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB
SET TIME_PST_VC = TO_CHAR(TIME_PST, 'HH24:MI');

SELECT constraint_name
FROM user_constraints
WHERE table_name = 'SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB'
  AND constraint_type = 'P';
  
-- note constraint name and use it below
-- make sure you know what is the target PK before deleting it
  
ALTER TABLE SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB
DROP CONSTRAINT PK_SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB;

ALTER TABLE SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB
MODIFY TIME_PST_VC NOT NULL;

ALTER TABLE SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB
RENAME COLUMN TIME_PST TO TIME_PST_OLD;

ALTER TABLE SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB
RENAME COLUMN TIME_PST_VC TO TIME_PST;

ALTER TABLE SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB
ADD CONSTRAINT PK_SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB
PRIMARY KEY (METADATA_COMPID, METADATA_TIMESTAMP, EXECUTION_DATE_PST, TIME_PST, PRIORITY);

-- if the above fails, identify duplicates on the whole row and drop one

ALTER TABLE SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB
DROP COLUMN TIME_PST_OLD;

-- ==================================================
