I'm writing a generic drop-all-objects script for our Oracle databases. It generates drop table TABLE_NAME cascade constraints purge lines for all tables, amongst other object types, by looping through user_objects.

The problem is that tables which are have reference-partitioned tables dependent on them can't be dropped in this way: ORA-14656: cannot drop the parent of a reference-partitioned table.

How can I detect which tables are the parents of reference partitioned tables from the data dictionary, so I can skip them in the first loop, and drop them in a second loop?

  • ALL_DEPENDENCIES doesn't seem to contain these types of dependency! – Barn Sep 23 '13 at 12:27

To get a list of partitioned tables that have at least one referencing partitioned child table the [dba][all][user]_part_tables and [dba][all][user]_constrains data dictionary views, depending on the privileges granted, can be queried:

create table tb_part_parent(
  col  number primary key,
  col2 number
partition by range (col2) (
  partition part_1 values less than (100),
  partition part_2 values less than (300),
  partition part_3 values less than (500)

create table tb_part_child(
  col  number not null,
  col2 number,
  constraint fk_parent_1 foreign key(col) references tb_part_parent(col)
)partition by reference (fk_parent_1)

The query:

select t.table_name
  from user_constraints t
 where t.constraint_name in ( select w.r_constraint_name
                                from user_constraints w
                                join user_part_tables q
                                  on (q.table_name = w.table_name and
                                      q.ref_ptn_constraint_name = w.constraint_name)



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.