This content has been marked as final. Show 17 replies
Afaik there's not a statement that will delete all records from all tables in a schema.
You can generate a script pretty easy however.
Connect to the correct schema and issue:
(Use truncate, it'll be faster then using delete)
select 'truncate table '||table_name||';' from user_tables;
You can spool the output to a file and run it (or copy-paste it to your developer tool).
Truncate won't work if you have foreign keys...
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
Well, yes and no ;)
Truncate details first, and masters after that.
"TRUNCATE is allowed even when there are child tables. However, child tables need to be empty for TRUNCATE to proceed. If any of the child tables have any rows in them, TimesTen returns an error indicating that a child table is not empty."
You're right, I 'tahiti'd' myzelf into the wrong reference, I ended up in the "Oracle® TimesTen In-Memory Database SQL Reference" instead of the [Oracle® Database SQL Language Reference|http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_10007.htm#SQLRF01707].
I guess I owe you a coffee ;)
Interesting though, TimesTen behaves differently apparently.
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
This answer was given a few times already, with the exception of your WHERE clause.
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
1) Drop the user
2) recreate the user including table scripts from
2a) your version control system
2b) from a export dumpfile using the "nodata" option while importing