2

I created table t1 in Oracle 12c. Table has data and it is partitioned on list partition and also has subpartitions.

Now I want to delete whole table and all associated partitions (and subpartitions).

Is this the right command to delete all?

DROP TABLE t1 PURGE;
Joe
  • 11,983
  • 31
  • 109
  • 183
  • You can use it, but it works without PURGE as well. Then, the whole table is moved into Recyclebin and you can recover it easily, if you realize it wasn't intended to drop this table. If you are sure, then you can afterwards manually purge the recyclebin. With PURGE, the table won't be moved to RecycleBin and so, undoing this operation would require very high efforts. – D. Lohrsträter Oct 26 '21 at 13:14

2 Answers2

3

The syntax is right but not preferable,

just drop without purge so that whenever you need you could have it back, if your flashback option is enabled. If your database's flashback option is in charge, you could issue this command (provided you don't use purge):

SQL> DROP TABLE T1;
SQL> FLASHBACK TABLE T1 TO BEFORE DROP RENAME TO T1_ver_2;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
3

When you run DROP then the table is removed entirely from database, i.e. the table does not exist anymore.

If you just want to remove all data from that table run

truncate table T1 drop storage;

You can also truncate single (sub-)partition if required.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Just truncate is not always the best option, because all partitions will be empty, but still exists physically, even if they will never be used at any later point in time and it might have impact to query-performance, if partition-keys don't allow partition pruning. – D. Lohrsträter Oct 26 '21 at 13:03
  • @D.Lohrsträter I don't think so, because of `DROP STORAGE` clause the partition does not have any data er empty space (or maybe just 1 extend), it exists only in the data dictionary. – Wernfried Domscheit Oct 26 '21 at 13:39
  • Yes, storage will be gone, but partition still exists. I've myself a situation with old partitions and unlucky WHERE condition, which requires an execplan to scan each of hundreds of partitions. Since this is executed million times per day, it might make a small difference each, if it can ommit checking some hundreds of empty partitions. To be honest, I haven't it verified in this detail. – D. Lohrsträter Oct 27 '21 at 13:41
  • Yes, scanning hundreds of partitions takes time. But an empty one should not make any difference. – Wernfried Domscheit Oct 27 '21 at 17:03