Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-14404: partitioned table contains partitions in a different tablespace

user12002352Jan 27 2011 — edited Jan 27 2011
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 24 2011
Added on Jan 27 2011
2 comments
1,151 views