delete all data

How do you delete all data? The simplistic approach would be to truncate all tables


SQL> select table_name from user_tables;
TABLE_NAME
----------
T1
SQL> truncate table t1;
Table truncated.

You cannot truncate if you have referential integrity constraints.

SQL> truncate table t2;
ORA-02266: unique/primary keys in table
referenced by enabled foreign keys

Ok, let’s disable the RIC


SQL> select table_name, constraint_name
from user_constraints
where constraint_type='R';
TAB CONSTRAINT
--- ----------
T3 SYS_C00107
SQL> alter table t3 disable constraint SYS_C00107;
Table altered.
SQL> truncate table t2;
Table truncated.
SQL> truncate table t3;
Table truncated.

You cannot truncate cluster tables

SQL> truncate table t4;
ORA-03292: Table to be truncated is part of a cluster

Cluster tables could be dropped with TRUNCATE CLUSTER.

SQL> select cluster_name from user_clusters;
CLUSTER_NAME
------------
C
SQL> truncate cluster c;
Cluster truncated.

The code above doesn’t work with Partitioned cluster (12.1.0.2) because it was not properly implemented at the time of the writing.
Check Bug 20284579 : CAN NOT QUERY DYNAMIC CLUSTER PARTITIONS

For reference partitioning, it is not possible to disable the foreign key

SQL> alter table t6 disable constraint fk;
ORA-14650: operation not supported for
reference-partitioned tables

In 12c, if the foreign key is defined with ON DELETE CASCADE, you can truncate cascade the parent.

SQL> select table_name, REF_PTN_CONSTRAINT_NAME
from user_part_tables
where partitioning_type='REFERENCE';
TAB REF
--- ---
T6 FK
SQL> select r_constraint_name, delete_rule
from user_constraints
where constraint_name='FK';
R_CON DELETE_RULE
----- -----------
PK CASCADE
SQL> select table_name
from user_constraints
where constraint_name='PK';
TAB
---
T5
SQL> truncate table t5 cascade;
Table truncated.

But if one of child or child-child table is using reference partitioning without the ON DELETE CASCADE, then the parent or grand-parent could not be truncated. And truncate cascade for reference partitioning is not documented (yet).

But there is very nice alternative to TRUNCATE called is DELETE 🙂


SQL> select table_name, REF_PTN_CONSTRAINT_NAME
from user_part_tables
where partitioning_type='REFERENCE';
TAB REF
--- ---
T8 FK
SQL> select r_constraint_name, delete_rule
from user_constraints
where constraint_name='FK';
R_CON DELETE_RULE
----- -----------
PK NO ACTION
SQL> select table_name
from user_constraints
where constraint_name='PK'
TAB
---
T7
SQL> truncate table t7 cascade;
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "SCOTT"."T8"
SQL> truncate table t8;
Table truncated.
SQL> delete from t7;
2 rows deleted

To get the tables in the right order, parent tables after children, you can do some hierarchical query and then order by rownum desc, a construct I’m using for the first time I confess. Note the leaf tables are truncable.


select c_owner owner, child table_name
FROM
(
SELECT
p_OWNER, parent, nvl(c_owner, a.owner) c_owner,
nvl(child, a.table_name ) child
FROM
(
SELECT
PT.OWNER P_owner, pt.table_name parent,
pt2.owner c_owner, pt2.table_name child
FROM all_part_tables pt
JOIN all_constraints c
ON pt.OWNER = c.owner
AND PT.TABLE_NAME = c.table_name
AND c.constraint_type = 'P'
AND c.status = 'ENABLED'
JOIN all_constraints r
ON r.r_owner = c.owner
AND r.r_constraint_name = c.constraint_name
AND r.constraint_type = 'R'
AND r.status = 'ENABLED'
JOIN all_part_tables pt2
ON r.owner = pt2.owner
AND r.constraint_name = pt2.REF_PTN_CONSTRAINT_NAME
AND pt2.partitioning_type = 'REFERENCE'
) t
RIGHT JOIN all_tables a
ON child = table_name and a.owner = T.c_OWNER
)
where connect_by_isleaf=0
CONNECT BY parent = PRIOR child and p_owner=PRIOR c_owner
start with parent is null
order by rownum desc;

OWNER TAB
----- ---
SCOTT T10
SCOTT T9

Note the query above is very slow. If dictionary-performance is an issue, maybe we could delete all tables and catch exceptions and loop until all tables are empty

SQL> delete from t9;
ORA-02292: integrity constraint (SCOTT.F10) violated - child record found
SQL> delete from t10;
ORA-02292: integrity constraint (SCOTT.F11) violated - child record found
SQL> delete from t11;
1 row deleted.
SQL> delete from t9;
ORA-02292: integrity constraint (SCOTT.F10) violated - child record found
SQL> delete from t10;
1 row deleted.
SQL> delete from t11;
0 row deleted.
SQL> delete from t9;
1 row deleted.
SQL> delete from t10;
0 row deleted.
SQL> delete from t11;
0 row deleted.
SQL> delete from t9;
0 row deleted.
SQL> delete from t10;
0 row deleted.
SQL> delete from t11;
0 row deleted.

If you have close to zero reference-partitioning table, this approach will be more efficient.