Geek asks and answers

Oracle | a table

I want to drop specific tables in a tablespace with a common name added to the end of each table, for example:

TABLE1_NAME1_COMMON
TABLE2_NAME2_COMMON
TABLE3_NAME3_COMMON

I have heard about Oracle functions, but I am not very familiar with them, so I expect some help.

Thank.

+3
source share
3 answers

If you are completely sure of what you are doing, that is, if you are sure that it is not by chance that you select a table that you do not want to discard, you can do the following:

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 :

  • Changed Now choose from user_tablesinstead dba_tables, as it seems more secure.
  • Added set serveroutput onfor print.dbms_output.put_line
  • Added begin .. exception .. endto display errors.
+9
source

, DROP -statements, :

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


DROP TABLE TABLE1_NAME1_COMMON;
DROP TABLE TABLE2_NAME2_COMMON;
DROP TABLE TABLE3_NAME3_COMMON;
+3

to identify them you can use:

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

You can then either get your DROP reports using SELECT. Or you can write a PL / SQL function to cycle through the "General Tables" and DROP them using EXECUTE IMMEDIATE.

I would make sure that you are 100% sure of the choice in the first place.

0
source

More articles:


All Articles