My Oracle Support Banner

How to Recreate a Table Partition After Having Dropped the Datafile? (Doc ID 272312.1)

Last updated on OCTOBER 03, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.0 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 11-Sep-2012***


Goal

You dropped a datafile containing a table partition.

For example:

SQL> create table t1
2 ( id number(2),
3 name varchar2(15))
4 partition by range (id)
5 (partition p1 values less than (10) tablespace users,
6 partition p2 values less than (20) tablespace test,
7 partition p3 values less than (30)) tablespace shailesh;

SQL> select * from t1;

ID NAME
---------- ---------------
1 XXX
12 XXX
25 XXX

SQL> alter database datafile '<Datafile_Location>\filename' offline drop;

Database altered.



Accessing the partitioned table t1 will give the following errror:

SQL> select * from t1;
ERROR:
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '<Datafile_Location>\filename'

SQL> drop tablespace test INCLUDING CONTENTS;
drop tablespace test INCLUDING CONTENTS
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace



Now you want to get back the partition segment structure (without any data)
and avoid this error.

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.