I am performing a schema dump of certain tables. This accompanied by dropping the tables that will be dumped. For example I need to drop all tables that start with, let's say, "SRC_". You guys think of a more efficient way of dropping those tables than the script below? Thanks.

  for rec in (select TABLE_NAME
                from ALL_TABLES
               where TABLE_NAME like 'SRC|_%' escape '|'
                 and OWNER = 'SOME_SCHEMA')
    execute immediate 'drop table ' || rec.TABLE_NAME;
  end loop;
  • 1
    AFAIK, there is no bulk drop. Your method is straight forward. And dropping is anyway fast, excluding the situation when there is a lock on the table. Jul 5, 2013 at 6:42

2 Answers 2


Why does the code need to be more efficient? It seems unlikely that you would be dropping large numbers of tables frequently or that it would take enough time to drop tables that it would be worth optimizing.

What trade-offs are you willing to make to cause the code to be more efficient? And what do you mean by "efficient" in this context-- wall clock time? The resources consumed on the server? Something else? The code that you have written is very straightforward and easy to follow. You could complicate things by, for example, submitting multiple jobs that each drop a subset of the tables so that you can have multiple threads working simultaneously. But that would involve making the code much more complicated-- you'd need to assign work to different threads and then coordinate the responses from each thread to verify that all the tables were dropped successfully. It seems most unlikely that this would be a trade-off that you would really want to make though it would reduce the wall clock time needed to drop all the tables.

  • Thanks for your comment. When I say efficiency I meant the time that was needed for the operation to be completed. Yes I agree with you that the code above does not need to be optimized further since the trade-off between execution time and the effort to make it more optimized will not be worth it. However, will there be a performance issue when dropping and adding of table columns is needed per table using the approach above?
    – Ram
    Jul 5, 2013 at 7:07
  • Why are you doing DDL against a large number of tables often enough to care about the performance? Adding or dropping columns should be infrequent and adding or dropping columns from a number of different tables should be exceedingly rare. If you're doing this often enough to really care about performance, it seems likely that you've got an underlying design issue. Jul 5, 2013 at 14:21
  • 2
    @JustinCave - there is one use case when the performance of DDL scripts really matter, and that is automated builds, usually as part of unit testing or integration testing. The time it takes to execute test fixtures - setup and teardown routines - which include DDL for the objects involved in the tests can have an impact on how often the tests are run. Continuous integration has a philosophy of running the build and the test suites as often as possible.
    – APC
    Jul 5, 2013 at 15:11
  • @APC - I get that you'd need to set up the schema at the beginning of the test suite and tear it down at the end. I've never seen a situation, though, where the time that was spent doing that DDL was meaningful in comparison to the time required to populate the data needed for the test suite or the time required to run the actual tests. And why would you be adding or removing large numbers of columns as part of setting up the schema for the tests? Am I missing something? Jul 5, 2013 at 15:17
  • @JustinCave - I'm saying it's common, but I've been in such a situation. Think "phase 2 release" with an existing application. The deployment and regression scripts both need to be tested, and both should be included in CI builds. Perhaps Edition-Based Redefinition will do away with the need for this phase of the activity.
    – APC
    Jul 5, 2013 at 15:24

The only alteration I might make is this:

execute immediate 'drop table ' || rec.TABLE_NAME||' cascade constraints';

This will prevent the statement failing if the table in question is used to enforce a foreign key. The assumption here is that any child tables are also in scope of this exercise. If that assumption is wrong you would want to handle the ORA-02449 exception in other ways.

Otherwise, as Justin says, the performance of DROP TABLE is about as fast as it can be.

"will there be a performance issue when dropping and adding of table columns is needed per table using the approach above?"

This is a different matter. Two different matters in fact. Although if the table contains no data the time taken to add or drop a column is negligible. Whereas there are potential issues when the table contains data.

  1. Adding a column. If the column is supposed to be mandatory you will not be able to add the NOT NULL constraint until all the rows are populated with data for the new column. You can do this with a DEFAULT clause when adding the column or as an UPDATE statement; either way it will take time proportional to the number of rows in the table.
  2. Dropping a column This action also takes time when the table is populated. That's why Oracle provide SET UNUSED as well as DROP COLUMN. SET UNUSED is way quicker, because it is just a data dictionary change; however it doesn't free up any space. Find out more.
  3. Other complications with dropping columns. Attempting to drop a column which is referenced by a foreign key hurls ORA-12992. And dropping columns on a partitioned table can be a total 'mare.

"A DEFAULT clause, at least in recent versions of Oracle, should make adding new non-NULL columns take more or less constant time since it's only populating the value in the data dictionary not in every row of the table."

Are you sure? It takes much longer to add a column with a default value when there are over 50000 rows compared to when there are two rows. Adding a column without a default clause is added for comparison.

SQL> select * from t1;

      COL1       COL2       COL3       COL4 C C
---------- ---------- ---------- ---------- - -
7.2000E+75 -7.200E-75                       Y N
1.2346E+14                                  Y N

SQL> set timing on
SQL> alter table t1 add col7 varchar2(1) default 'A'
  2  /

Table altered.

Elapsed: 00:00:00.06
SQL> insert into t1 (col1, col2, col3) select object_id, object_id*-1, rownum from all_objects 
  2  /

56481 rows created.

Elapsed: 00:00:11.35
SQL> alter table t1 add col8 varchar2(1) default 'X'
  2  /

Table altered.

Elapsed: 00:00:01.80
SQL> alter table t1 add col9 varchar2(1) ;

Table altered.

Elapsed: 00:00:00.08

For the record, I'm running almost the latest version....

SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release - Production
PL/SQL Release - Production
CORE      Production
TNS for Linux: Version - Production
NLSRTL Version - Production

Elapsed: 00:00:00.06

Wallclock timings are notoriously unreliable, but the general variations in elapsed time seem pretty consistent.

  • A DEFAULT clause, at least in recent versions of Oracle, should make adding new non-NULL columns take more or less constant time since it's only populating the value in the data dictionary not in every row of the table. Jul 5, 2013 at 14:23
  • @JustinCave - how recent is "recent"? Please see my edit.
    – APC
    Jul 5, 2013 at 15:05
  • You're adding new nullable columns so the DEFAULT clause forces Oracle to touch every row to set the value. If you add a column with a DEFAULT and a NOT NULL, then Oracle only has to touch the data dictionary and doesn't need to visit every row. On a 76,000 row table on my box alter table t1 add col1 varchar2(10) default 'X'; takes 1.5-2 seconds while alter table t1 add col3 varchar2(10) default 'Z' not null takes 0.02 seconds. Jul 9, 2013 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.