0

I have about 30 tables in Oracle. Names of the tables have some template format, for example:

DF_D_AUTO, DF_D_PERSON

and so on. So the first part of a table name is always

DF_D_

I would like to clear all these tables. Of course I can clear them manually one by one.

However I would like to know may be someone knows a good fast way using SQL to clear all such tables in one scope.

Florian Humblot
  • 1,121
  • 11
  • 29
Kirill Ch
  • 5,496
  • 4
  • 44
  • 65
  • What do you mean by 'clear ' - delete? truncate? or even drop? You could use dynamic SQL in a PL/SQL block but that isn't 'using SQL', and is probably overkill unless tables are being added and removed (so you don't have a static list to work with). – Alex Poole Jun 20 '17 at 08:55

1 Answers1

2

You can run a loop on all such tables

BEGIN

    for table_names in (select table_name from dba_tables where table_name like 'DF\_D\_%' escape '\') 
    loop
        EXECUTE immediate 'truncate table ' ||  table_names.table_name;
    end loop;
END;

DBA_TABLES - reference

Difference between dba_tables, user_tables, all_tables - reference

Sudipta Mondal
  • 2,550
  • 1
  • 19
  • 20
  • Thank you. What is 'dba_tables'? – Kirill Ch Jun 20 '17 at 09:06
  • @KirillCh Updated answer. – Sudipta Mondal Jun 20 '17 at 09:07
  • Should I insert something instead of 'dba_tables'? Because in my PL/SQL Developer I couldn't execute this exact code. – Kirill Ch Jun 20 '17 at 09:11
  • 1
    @KirillCh - if you own those tables, just use `user_tables` instead. (If you were using `all_tables` or `dba_tables` - you don't seem to have permission to see the latter - then you'd probably want to filter on `owner` as well). – Alex Poole Jun 20 '17 at 09:11
  • If use "delete from" instead of "truncate table" in the loop it is much faster. In my case from 30-50 seconds to 1 or less second. – Kirill Ch Jun 22 '17 at 14:08
  • @KirillCh - Maybe something else is causing that issue, `TRUNCATE` usually is much faster than `DELETE`. Have a look at [link](https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6654614400346481630) – Sudipta Mondal Jun 22 '17 at 14:21
  • Absolutely. Normally Truncate is much faster. However in this particular loop "Truncate" is going about 30-50 seconds every time in my case. I have tried to change it on "Delete from" and the result is now approximetely 0.2-0.5 second every time. Just for sure currently tried Truncate - again 40 seconds. – Kirill Ch Jun 22 '17 at 15:33