Oracle | drop table

I want to drop certain tables in a tablespace that has common name appended to end of each table for an example:

TABLE1_NAME1_COMMON
TABLE2_NAME2_COMMON
TABLE3_NAME3_开发者_运维知识库COMMON

I heard about Oracle functions but I'm not familiar much with those so I'm expecting some helping hand.

Thanks.

---

If you're completely sure what you're doing, ie, if you're sure that you don't accidentally drop a table that you don't want to drop, you can do a:

set serveroutput on size 1000000

begin
for r in (

  select table_name 
    from user_tables 
   where table_name like '%\_COMMON' escape '\')

loop

  execute immediate 'drop table ' || r.table_name;

end loop;
exception when others then
   dbms_output.put_line(sqlerrm);
end;
/

Edit:

1. Changed Now selecting from user_tables instead of dba_tables as it seems more safe to do.
2. Added set serveroutput on in order for dbms_output.put_line to be printed
3. Added begin .. exception .. end in order for errors to be shown.

---

You could do that in a procedure, but it might be better to just select those DROP-statements, review them and execute them manually:

SELECT 'DROP TABLE ' || table_name || ';'
FROM user_tables
WHERE table_name LIKE '%\_COMMON' ESCAPE '\';

would return

DROP TABLE TABLE1_NAME1_COMMON;
DROP TABLE TABLE2_NAME2_COMMON;
DROP TABLE TABLE3_NAME3_COMMON;

---

to identify them you can use:

SELECT * FROM user_tables WHERE tablespace_name='MySpace' AND table_name like '%COMMON';

You could then either, derive your DROP statements using a SELECT. Or you could write a PL/SQL function to loop through the "Common Tables" and DROP them using EXECUTE IMMEDIATE.

I would make sure you are 100% sure in the selections first however.