I am using oracle DB to maintain more than 30 tables, how can I delete all the data from all the tables? I only want to delete the data but not drop the tables.
-
This question appears to be off-topic because it belongs on dba.stackexchange.comuser207421– user2074212014-08-14 01:13:36 +00:00Commented Aug 14, 2014 at 1:13
Add a comment
|
9 Answers
There is no command 'ALTER TABLE XXX DISABLE ALL CONSTRAINTS'
I propose this;
BEGIN
FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R')
LOOP
EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' disable constraint ' || c.constraint_name);
END LOOP;
FOR c IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE ('truncate table ' || c.table_name);
END LOOP;
FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R')
LOOP
EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
2 Comments
Aditya T
This should a better solution. You can also tweak it to actually DROP TABLES and CONSTRAINTS rather than TRUNCATE.
Generate a script to truncate (= remove all rows from) all tables:
select 'truncate table ' || table_name || ';' from user_tables
And then execute the script.
3 Comments
Neil Barnwell
I did edit this answer, sorry about that but the all_tables thing was quite dangerous.
Jeffrey Kemp
Just for newbies: note that
truncate is non-transactional, i.e. it cannot be rolled back.
René Nyffenegger
This won't work if there are foreign-primary key constraints defined.
To address the issue of constraints, something like this should work:
BEGIN
FOR T in (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' DISABLE ALL CONSTRAINTS';
END LOOP;
FOR T in (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||T.table_name;
END LOOP;
FOR T in (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' ENABLE ALL CONSTRAINTS';
END LOOP;
END;
1 Comment
René Nyffenegger
alter table ... disable all constraints throws a ORA-01735: invalid ALTER TABLE option if there is no constraint defined for the table, which would cause the script to fail to truncate every table if there is at least one table without constraint. You might want to place the execute immediate within a begin .. exception block.The potential drawback with a truncate is that it may fail on referential integrity constraints. So you'd want to disable foreign key constraints first, then do the truncate, then re-enable constraints. The 'plus' with cloning the schema (exp and imp) is that you could also drop and recreate the tablespace too (which you may want to do if you want to reclaim some physical disk space as a result of removing all the data).
Comments
Clone the schema and then drop the old tables?
4 Comments
BCS
Or the "real DBA" way of doing it: backup the full DB sans the data, and then rebuilt the DB (... the server ... from the motherboard on up ;).
Eric Wilson
@TomH. How is this simple? I'm not a DBA, and it gives me no idea what commands to execute.
BCS
@FarmBoy, IIRC that will depend on the DBMS.
Eric Wilson
@TomH. It's an Oracle question . . .
I created this stored proc, using the answers mentioned above. This works perfectly without any errors or exceptions.
create or replace PROCEDURE DELETE_ALL_DATA
AS
cursor r1 is select * from user_constraints;
cursor r2 is select * from user_tables;
cursor r3 is select * from user_constraints;
cursor r4 is select * from user_tables;
BEGIN
FOR c1 IN r1
loop
for c2 in r2
loop
begin
if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable constraint ' || c1.constraint_name);
end if;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
continue;
WHEN OTHERS
THEN
continue;
end;
end loop;
END LOOP;
FOR T in (SELECT table_name FROM user_tables) LOOP
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||T.table_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
continue;
WHEN OTHERS
THEN
continue;
end;
END LOOP;
FOR c1 IN r3
loop
for c2 in r4
loop
begin
if c1.table_name = c2.table_name and c1.status = 'DISABLED' THEN
dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable constraint ' || c1.constraint_name);
end if;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
continue;
WHEN OTHERS
THEN
continue;
end;
end loop;
END LOOP;
commit;
END DELETE_ALL_DATA;
Comments
A slight variation on Andomar's answer to truncate all tables for a specific user instead of only those of the current user:
SELECT 'TRUNCATE TABLE ' || owner || '.' || table_name || ';' FROM all_tables WHERE owner = 'user/schema'
Replace the user/schema bit above with the name of the user/schema (between the quotes) you are interested in.
1 Comment
payket
thanks this query helping me so well. I prefer to this query if you have more than 1 db/schema
Delete all the data from all tables in oracle
DECLARE
str VARCHAR2(100);
BEGIN
FOR i IN
(SELECT object_name FROM user_objects WHERE object_type='TABLE'
)
LOOP
str := 'Truncate table '|| i.object_name;
EXECUTE IMMEDIATE str;
DBMS_OUTPUT.PUT_LINE('table data deleted :' || i.object_name);
END LOOP;
END;
For more info: http://www.oracleinformation.com/2014/10/delete-all-the-data-from-all-tables.html
Comments
these two line script are the best
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
1 Comment
K4timini
That's the easiest way to truncate all tables in a Microsoft SQL Server database. Even if you forgot to reenable constraints after removing data. But ... this question is about Oracle ... not SQL Server.