Hello,
Oracle version 11.2.0.3.0, running on Enterprise Linux.
I need to remove all data from two large tables (several hundred million rows in each) that are partitioned.
1) Table1 is a ranged partition table on a DATE column
2) Table2 is a reference partition table; the partitioning is referenced on a Foreign Key relationship to Table1 (column: Table1.Primary Key)
As I have no need for the data but want to keep the table structure, I'd ideally like to remove the partitions at the same time as removing the data, so that the 2 tables end up unpartitioned and empty.
Additionally, Table1 has some partitioned indexes that I'd like to be rebuilt as non-partitioned indexes (since Table1 will be empty).
I thought I'd start off by dropping all of Table1's partitions (via 'ALTER TABLE Table1 DROP PARTITION partition_name') but when the script got to the last partition, I got this error message:
ORA-14083: cannot drop the only partition of a partitioned table
ORA-06512: at "SYS.DROP_PARTITIONS", line 46
Could anyone advise as to the best approach for what I would like to achieve with respect to the two tables? Would dropping the two tables and then recreating them without partitions be the easiest way?
Thanks in advance for any guidance.