Partition table datafile lost for partition data in Oracle

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.