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.