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. :(
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.
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;