How to drop all user tables?
OracleConstraintsSql DropOracle Problem Overview
How can I drop all user tables in oracle?
I have problem with constraints. When I disable all it is still no possible.
Oracle Solutions
Solution 1 - Oracle
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'MATERIALIZED VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE',
'SYNONYM',
'PACKAGE BODY'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
FOR cur_rec IN (SELECT *
FROM all_synonyms
WHERE table_owner IN (SELECT USER FROM dual))
LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || cur_rec.synonym_name;
END;
END LOOP;
END;
/
Solution 2 - Oracle
If you just want a really simple way to do this.. Heres a script I have used in the past
select 'drop table '||table_name||' cascade constraints;' from user_tables;
This will print out a series of drop commands for all tables in the schema. Spool the result of this query and execute it.
Source: https://forums.oracle.com/forums/thread.jspa?threadID=614090
Likewise if you want to clear more than tables you can edit the following to suit your needs
select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')
Solution 3 - Oracle
Another answer that worked for me is (credit to http://snipt.net/Fotinakis/drop-all-tables-and-constraints-within-an-oracle-schema/)
BEGIN
FOR c IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE ('DROP TABLE "' || c.table_name || '" CASCADE CONSTRAINTS');
END LOOP;
FOR s IN (SELECT sequence_name FROM user_sequences) LOOP
EXECUTE IMMEDIATE ('DROP SEQUENCE ' || s.sequence_name);
END LOOP;
END;
Note that this works immediately after you run it. It does NOT produce a script that you need to paste somewhere (like other answers here). It runs directly on the DB.
Solution 4 - Oracle
begin
for i in (select 'drop table '||table_name||' cascade constraints' tbl from user_tables)
loop
execute immediate i.tbl;
end loop;
end;
Solution 5 - Oracle
The simplest way is to drop the user that owns the objects with the cascade command.
DROP USER username CASCADE
Solution 6 - Oracle
SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;'
FROM user_tables;
user_tables
is a system table which contains all the tables of the user
the SELECT clause will generate a DROP statement for every table
you can run the script
Solution 7 - Oracle
The easiest way would be to drop the tablespace then build the tablespace back up. But I'd rather not have to do that. This is similar to Henry's except that I just do a copy/paste on the resultset in my gui.
SELECT
'DROP'
,object_type
,object_name
,CASE(object_type)
WHEN 'TABLE' THEN 'CASCADE CONSTRAINTS;'
ELSE ';'
END
FROM user_objects
WHERE
object_type IN ('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION','SEQUENCE')
Solution 8 - Oracle
To remove all objects in oracle :
-
Dynamic
DECLARE CURSOR IX IS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE ='TABLE' AND OWNER='SCHEMA_NAME'; CURSOR IY IS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION', 'VIEW') AND OWNER='SCHEMA_NAME'; CURSOR IZ IS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TYPE') AND OWNER='SCHEMA_NAME'; BEGIN FOR X IN IX LOOP EXECUTE IMMEDIATE('DROP '||X.OBJECT_TYPE||' SCHEMA_NAME.'||X.OBJECT_NAME|| ' CASCADE CONSTRAINT'); END LOOP; FOR Y IN IY LOOP EXECUTE IMMEDIATE('DROP '||Y.OBJECT_TYPE||' SCHEMA_NAME.'||Y.OBJECT_NAME); END LOOP; FOR Z IN IZ LOOP EXECUTE IMMEDIATE('DROP '||Z.OBJECT_TYPE||' SCHEMA_NAME.'||Z.OBJECT_NAME||' FORCE '); END LOOP; END; /
2)Static
SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables
union ALL
select 'drop '||object_type||' '|| object_name || ';' from user_objects
where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION')
union ALL
SELECT 'drop '
||object_type
||' '
|| object_name
|| ' force;'
FROM user_objects
WHERE object_type IN ('TYPE');
Solution 9 - Oracle
Please follow the below steps.
begin
for i in (select 'drop table '||table_name||' cascade constraints' tb from user_tables)
loop
execute immediate i.tb;
end loop;
commit;
end;
purge RECYCLEBIN;