BEGIN FOR i IN (SELECT table_name FROM user_tables) LOOP EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i.table_name; END LOOP; COMMIT; END;
(Use truncate, it'll be faster then using delete)
select 'truncate table '||table_name||';' from user_tables;
SQL> create table tm 2 (id number primary key 3 ) 4 / Table created. SQL> SQL> create table td 2 (id number primary key 3 ,tm_id number references tm 4 ) 5 / Table created. SQL> SQL> truncate table tm 2 / truncate table tm * ERROR at line 1: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
CoolDecent wrote:Let me guess,.. you didn't read the thread, did you? ;)
FOR i IN (select tname from tab where tabtype ='TABLE' and tname not like '%$%')
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i.tname;
Edited by: CoolDecent on Nov 26, 2009 2:11 AM
user8105261 wrote:A typical way to reset a schema (e.g. to recreate a schema on the test database) is totally different.
I want to delete all records of all tables of a schema and I think there should be some statement for this but I don't know how?
may you help?
Edited by: user8105261 on Nov 25, 2009 11:06 PM