Fischer asked this 3 years ago

Deleting all tables in a oracle schema

I want to delete all tables from a schema in Oracle. What is the best/easiest way to do this.

I tried this in SQL developer but i have to select each table, then right click and select drop. :(


Best Answer by guruprakash 3 years ago

Login as sydba and run the following query:

SELECT 'DROP TABLE ' || TABLE_NAME || ' CASCADE CONSTRAINTS;' from dba_tables;

Above query will generate SQL statements to delete each table in the schema. You just need to copy the output and run those sql statements.

 

 

Rene Soto 1 year ago
1 like

DECLARE  

  CURSOR rs_update_curs  

  IS

     select OWNER||'.'||TABLE_NAME as Table_toDelete from SYS.ALL_TABLES where OWNER = 'MyPersonalSchema' and upper(TABLE_NAME) not like upper('%Table_TO_Exclude%');     

  my_rel rs_update_curs%ROWTYPE;

               

BEGIN

  OPEN rs_update_curs;

  FETCH rs_update_curs INTO my_rel;

  WHILE rs_update_curs%FOUND LOOP

    DBMS_OUTPUT.put_line(my_rel.Table_toDelete);

    execute immediate 'DROP TABLE ' || my_rel.Table_toDelete;

  FETCH rs_update_curs INTO my_rel;

  END LOOP;      

  CLOSE rs_update_curs;  

  COMMIT;

END;