Home > Oracle > Drop multiple tables in Oracle

Drop multiple tables in Oracle

April 23rd, 2009

I just noticed that the Oracle instance that I am working with has the SMP_% tables added from the Oracle Enterprise Manager. Since I don’t need them I decided to drop them. Here is my sql that generates the delete statements:

select ‘drop table ‘||table_name||’;’ from user_tables where table_name like ‘SMP_%’;

or..

select ‘drop table ‘||table_name||’;’ from user_tables where substr(table_name, 1, 3) =’SMP’;

This will generate something like this:

drop table SMP_VBOR_BACKUP_CONFIGURATION;
drop table SMP_VBOR_BLOB;
drop table SMP_VBOR_CHANNELS_INFORMATION;
drop table SMP_VBOR_DEFAULT_CONFIG;
drop table SMP_VBOR_STRATEGY_INFORMATION;
drop table SMP_VBO_JOB_CONFIG_TABLE;
drop table SMP_VBO_REPORTS;
drop table SMP_VBO_REPORTS_CONFIG;
drop table SMP_VBO_REPORTS_TYPE_DEFN;
drop table SMP_VBO_REPORT_ELEMENTS;
drop table SMP_VBO_REPORT_INFO_SOURCES;
drop table SMP_VDD_OPERATIONS_TABLE;
drop table SMP_VDE_EVENT;
drop table SMP_VDE_EVENT_ARCHIVE;
drop table SMP_VDE_EVENT_ARCHIVE_PURGE;
drop table SMP_VDE_EVENT_DETAILS;
drop table SMP_VDE_EVENT_LOCK_TAB;
drop table SMP_VDE_EVENT_LOG;
drop table SMP_VDE_EVENT_OCCURRENCE;
drop table SMP_VDE_EVENT_OCCUR_DETAILS;
drop table SMP_VDE_EVENT_TARGET_ACK;
drop table SMP_VDE_EVENT_TARGET_DETAILS;
drop table SMP_VDE_EVENT_TARGET_INFO;
drop table SMP_VDE_EVENT_TARGET_STATE;
drop table SMP_VDE_EVENT_UPDOWN_QUEUE;
drop table SMP_VDE_METRIC_THRESHOLDS;
drop table SMP_VDE_NODE_UPDOWN_QUEUE;
drop table SMP_VDE_THRESHOLD_ASSOC;
drop table SMP_VDE_TRY_REMOVE_EVENT_QUEUE;
drop table SMP_VDF_MASLIST;
drop table SMP_VDG_EVENTID_MAP;
drop table SMP_VDG_EVENT_DELETE_LIST;
drop table SMP_VDG_EVENT_NOTIF_LIST;
drop table SMP_VDG_GATEWAY_MAP;
drop table SMP_VDG_JOBID_MAP;
drop table SMP_VDG_NODE_LIST;
drop table SMP_VDG_NODE_LOCK_TABLE;
drop table SMP_VDI_AOBJECT_NOTIFICATION;
drop table SMP_VDI_OBJECT_TABLE;
drop table SMP_VDI_POS;
drop table SMP_VDI_TARGET_PROPERTIES;
drop table SMP_VDJ_JOB;
drop table SMP_VDJ_JOB_LOCK;
drop table SMP_VDJ_JOB_LOG;
drop table SMP_VDJ_JOB_LOG_COMMENT;
drop table SMP_VDJ_JOB_LOG_INTERMED;
drop table SMP_VDJ_JOB_OUTPUT;
drop table SMP_VDJ_JOB_PER_TARGET;
drop table SMP_VDJ_JOB_TARGET;
drop table SMP_VDM_ADDRESS;
drop table SMP_VDM_GLOBAL_INFO;
drop table SMP_VDM_LAST_NOTIF_SEQ_PERTYPE;
drop table SMP_VDM_NOTIFICATION;
drop table SMP_VDM_NOTIFICATION_DETAILS;
drop table SMP_VDM_NOTIFICATION_NVPAIRS;
drop table SMP_VDM_NOTIFICATION_SERVICES;
drop table SMP_VDM_PAGING_CARRIER_INFO;
drop table SMP_VDM_SESSION_NOTIFTYPE_PAIR;
drop table SMP_VDN_BLACKOUTSCHEDULE;
drop table SMP_VDN_GROUP_GROUP;
drop table SMP_VDN_GROUP_LIST;
drop table SMP_VDN_GROUP_TARGET;
drop table SMP_VDN_NODE_LIST;
drop table SMP_VDN_NOTIFY;
drop table SMP_VDN_STATE;
drop table SMP_VDN_TARGET_LIST;
drop table SMP_VDN_TARGET_PROPERTIES;
drop table SMP_VDN_TARGET_TYPE_DEFN;
drop table SMP_VDP_NODES;
drop table SMP_VDP_NODE_INFO;
drop table SMP_VDP_NODE_INFO_VDD;
drop table SMP_VDP_NODE_OMS_MAP;
drop table SMP_VDP_OMS_NUM_NODES;
drop table SMP_VDP_OMS_REGION_MAP;
drop table SMP_VDP_PGSRV_REGION_MAP;
drop table SMP_VDP_REGIONS;
drop table SMP_VDR_REGISTRY;
drop table SMP_VDS_REPOS_VERSION;
drop table SMP_VDS_SESSIONS_TABLE;
drop table SMP_VDU_CALLBACK_TABLE;
drop table SMP_VDU_OBJECTS_TABLE;
drop table SMP_VDU_PRINCIPALS_TABLE;
drop table SMP_VDU_PRIVILEGE_TABLE;
drop table SMP_VDV_DEFAULT_NOTIFY_PREFS;
drop table SMP_VDV_DEFAULT_PERMISSIONS;
drop table SMP_VDV_GENERAL;
drop table SMP_VDV_MAPI_EMAIL;
drop table SMP_VDV_NOTIFICATION_SCHEDULE;
drop table SMP_VDV_PAGE;
drop table SMP_VDV_PAGING;
drop table SMP_VDV_PREFERRED_CREDENTIALS;
drop table SMP_VDV_SERVICE_PARMS;
drop table SMP_VDV_SMTP_EMAIL;
drop table SMP_VDV_USER;
drop table SMP_VDV_USER_LOCALE;
drop table SMP_VDV_USER_PREF;
drop table SMP_VTD_DG_LOCATION;
drop table SMP_VTD_HISTORICAL_LOCATION;
drop table SMP_VXA_SYSTEM_PREFS;

Just execute it with Oracle SQL Developer and you are all set.

Categories: Oracle Tags: ,
  1. Robert Sills
    June 9th, 2009 at 07:59 | #1

    Based on this post, in SQL Developer I tried this sql:

    select ‘drop table ‘||table_name||’;’ as dropText from all_tables where table_name=’T_TMPL31′;

    and got this error:

    Error starting at line 1 in command:
    begin
    select ‘drop table ‘||table_name||’;’ from all_tables where table_name=’T_TMPL31′;
    end;
    Error report:
    ORA-06550: line 2, column 8:
    PL/SQL: ORA-00911: invalid character
    ORA-06550: line 2, column 1:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 2, column 37:
    PLS-00103: Encountered the symbol “¿” when expecting one of the following:

    begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with
    <a double-quoted delimited-i
    06550. 00000 – “line %s, column %s:\n%s”
    *Cause: Usually a PL/SQL compilation error.
    *Action:

  2. Robert Sills
    June 9th, 2009 at 11:01 | #2

    I found the problem – in the cut and paste of the select above my sql developer interpreted the single quotes as back ticks. When I retyped them that solved the issue.

  3. June 21st, 2009 at 02:34 | #3

    Hello Robert,
    I am glad to hear that you have found the issue. It gets quite ugly when you have to work with encoded characters. The good thing is that both SQL Developer and iSQLPlus are unicode compliant so you can copy and paste such encoded strings with no problem. I just have to be extra careful when it comes down to copy and pasting.
    HTH,
    St0ma

  4. Mohammad Rafi Ansari
    December 19th, 2010 at 23:48 | #4

    Thanks Robert for the code to delete multiple tables…

    Regards
    Mohammad Rafi Ansari

  5. HITESH
    March 3rd, 2011 at 02:13 | #5

    the above query is not working properly when i used it
    select ‘drop table ‘||table_name||’;’ from user_tables where table_name like ‘SMP_%’;

    then reult show drop the table but when i use such table again then its working………….please suggest me a command for droping multiple row

    regards
    hitesh

  6. March 8th, 2011 at 13:26 | #6

    Hello Hitesh,
    Please paste the exact command and result so we can review and correct them.
    Regards,
    S

  7. King of Terracotta Army
    June 17th, 2011 at 08:28 | #7

    you need “cascade constraints ” otherwise it will not work

    select ‘drop table ‘||table_name|| ‘ cascade constraints ;’
    from user_tables where table_name like ‘SMP_%’;

  8. admin
    July 6th, 2011 at 04:26 | #8

    Depending on the type of table, you might not have to cascade over the constraints.
    Thanks for the input

  9. Pat
    September 22nd, 2011 at 11:54 | #9

    Thanks so much for this very useful piece of code. I did have to re-type the single quotes after pasting, but that was no big deal and gave me just what I was looking for! Thanks again!

  10. luke samad
    October 26th, 2011 at 07:40 | #10

    thanks, that was very useful.

  11. Frank
    June 21st, 2012 at 02:03 | #11

    I tried it to erase all tables that have _Menores at the end of the table name with your script like this:

    select ‘drop table ‘||table_name||’;’ from user_tables where table_name like ‘%_Menores’

    But it gave me this error:

    ORA-00911: invalid character

  12. June 21st, 2012 at 03:19 | #12

    @Frank
    Hi Frank,
    The table names should be in upper case. Does the error say on which position in the line the invalid character is?
    I guess the issue is with the name of the table and the underscore character in it. What you can do is try to escape it with the following expression. My idea in this example is to just list the table names that are matching the criteria. Then you can easily convert it.

    select table_name from user_tables where table_name like ‘%\_MENORES%’ escape ‘\’ order by table_name;

    If this brings back the correct table names then you can just use it for the drop generation as:

    select ‘drop table ‘||table_name||’;’ from user_tables where table_name like ‘%\_MENORES%’ escape ‘\’ order by table_name;

    Let us know how it goes!
    St0ma

  13. siva karthik
    May 8th, 2013 at 05:25 | #13

    SQL> begin
    2 execute immediate ‘drop table siva’;
    3 execute immediate ‘drop table siva1’;
    4 end;
    5 /

    PL/SQL procedure successfully completed.

    SQL> CREATE TABLE SIVA(KEY NUMBER, KEY1 NUMBER);

    Table created.

    SQL> CREATE TABLE SIVA1(KEY NUMBER,KEY1 NUMBER);

    Table created.

    SQL> drop table &tablenmame;
    Enter value for tablenmame: siva
    old 1: drop table &tablenmame
    new 1: drop table siva

    Table dropped.

    SQL> /
    Enter value for tablenmame: siva1
    old 1: drop table &tablenmame
    new 1: drop table siva1

    Table dropped.

Comments are closed.