Recover Partition table data for other present tablespace in Oracle
Example of drop the partition tablespace associated with partition table in Oracle. For use the Partition table we need to use exchange command to access that table.
Error
Drop the tablespace associated with Oracle table partition
SQL> insert into t1 values(5,'aaa');
1 row created.
SQL> insert into t1 values(15,'bbb');
1 row created.
SQL> insert into t1 values(25,'ccc');
1 row created.
SQL> select * from t1;
ID NAME
---------- ---------------
5 aaa
15 bbb
25 ccc
SQL> alter database datafile 'D:\ORACLE19C\ORADATA\ORCL\ORCLPDB\TEST02.DBF' offline drop;
Database altered.
SQL> select * from t1;
ERROR:
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: 'D:\ORACLE19C\ORADATA\ORCL\ORCLPDB\TEST02.DBF'
Solution: We can access the table data which not present in that tablespace
-- Create the table sturcture
SQL> create table test as select * from t1 where 1=2;
Table created.
-- Exchange partition
SQL> alter table t1 exchange partition p2 with table test without validation including indexes
Table altered.
SQL> select * from t1;
ID NAME
---------- ---------------
5 aaa
25 ccc
- If you want to use again for P2 partition
SQL> drop tablespace test2 including contents and datafiles;
Tablespace dropped.
SQL> create tablespace test2 datafile 'D:\ORACLE19C\ORADATA\ORCL\ORCLPDB\TEST02.DBF' SIZE 100m;
Tablespace created.
SQL> alter table t1 move partition p2 tablespace test2;
Table altered.
SQL> select * from t1;
ID NAME
---------- ---------------
5 aaa
25 ccc
Smart way of Technology 