Hello
Oracle 10.2.0.2
Redhat 5 -32bit
Table: GAME_XXX
Tablespace: GAME_YYY_2001_01_01
Partitions: 25 (2001_01 [...] and so on)
The datafile for tablespace 2007_09 was dropped, and Oracle shutdown. I opened in restricted mode and took the datafile offline drop.
I have NO BACKUPS, i have another server with identical data (but not identical datafile structure)
I tried to drop the tablespace, but it wont allow me
SQL> drop tablespace GAME_YYY_2007_09 including contents;
drop tablespace GAME_TRANS_2007_09 including contents
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
I tried to drop the partition, but it wont allow me
SQL> alter table GAME_TRANS_64 drop partition P_2007_09;
alter table GAME_TRANS_64 drop partition P_2007_09
*
ERROR at line 1:
ORA-00376: file 151 cannot be read at this time
ORA-01110: data file 151: '/data2/oracle/padb/game_trans_2007_09_01.dbf'
I would like to take an export of the partition P_2007_09 (that has tablespace GAME_YYY_2007_09), from the other database, and then import it here to a tablespace called the same, however, to do this i must drop the old tablespace first, but as you can see from the error messages, i cant.
Could i disable the index on all the partitions (25 of them), then drop the tablespace (would it allow me to drop it if i removed index?), then recreate the tablespace, and then import partition from old server, then create the index on all of them?
Thanks