Drop schema on Oracle databases

General Discussion
1

Hi,

We’re using liquibase for our integration test suites to drop and migrate the database schema on Oracle 11g instances and we found a serious performance issue: Depending on the database instance it takes serveral minutes up to one hour to drop a schema which currently contains only about 10 tables. The enterprise manager reports the following statement as top activity:

select ui.tablespace_name TABLESPACE, ucc.table_name TABLE_NAME, ucc.column_name COLUMN_NAME
FROM all_indexes ui , all_constraints uc , all_cons_columns ucc where uc.constraint_type = ‘P’ and ucc.constraint_name = uc.constraint_name and uc.index_name = ui.index_name and uc.owner = ‘DBUSER’ and ui.table_owner = ‘DBUSER’ and ucc.owner = 'DBUSER’

We tried to optimize this statement using the SQL Tuning Advisor and defining a SQL profile but this didn’t help. Is there any way to get around this?

Regards

Dirk