ORA-14404: partitioned table contains partitions in a different tablespace
Hi Experts
I have a database that has been tuned for a data migration project, the database is 8TB and has no archivelogs. One of the datafiles became corrupt when it was moved to a new mountpoint and I'm unable to being it back online (ORA-01122). I'm able to reload the data for the corrupt datafile and therefore I want to just drop it and recreate it. However this datafile is part of a very large IOT partitioned table. I've tried the following script but get a different error.
CREATE TABLE HPS.PARM_VALUE_20100315x
(
TEST_EVENT_KEY NUMBER NOT NULL,
EVENT_START_TIMESTAMP TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
PARAMETER_TYPE_KEY NUMBER NOT NULL,
PARAMETER_DATA_QUALITY CHAR(1 BYTE) DEFAULT 'G' NOT NULL,
PARAMETER_VALUE_CHAR VARCHAR2(4000 BYTE),
PARAMETER_VALUE_NUMBER NUMBER,
CONSTRAINT PARM_VALUE_20100315_PK
PRIMARY KEY
(EVENT_START_TIMESTAMP, TEST_EVENT_KEY, PARAMETER_TYPE_KEY)
)
ORGANIZATION INDEX
COMPRESS
/
alter table PARAMETER_VALUE exchange partition PARM_VALUE_20100315 with table PARM_VALUE_20100315x without validation;
ORA-00376: file 507 cannot be read at this time
ORA-01110: data file 507: '/oracle/hpsp/dwr2/PARM_VALUE_2010W11.dbf'
Is there anyway to just drop the invalid tablespace/datafile without dropping the entire table and losing what I've already migrated?