Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
ALL_DEPENDENCIES doesn't seem to contain these types of dependency! –  Barn Sep 23 '13 at 12:27

1 Answer 1

up vote 2 down vote accepted

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)
                              )

Result:

TABLE_NAME
-----------------
TB_PART_PARENT 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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