How to delete all rowa in all tables of a schema in Oracle?

Ach · #1 (**permalink**) 08-11-04, 04:19

Hi all,

I want to delete all records of all tables of a schema and think there should be some statement for this but I dont know how?

may you help?

Pat Phelan · #2 (**permalink**) 08-11-04, 08:41

As this question is Oracle specific, I'd suggest that you post it in the Oracle forum. One of the Oracle folks can probably answer your question definitively without even needing to look it up!

-PatP

spvijay · #3 (**permalink**) 08-11-04, 09:58

There is no simple statement available to delete only the tables.
U can instead use
DROP USER <username> CASCADE.
But caution.....this will delete everything belonging to the user tables, views, sequences..etc.
If u want to delete only the table of a schema
then u can write a PL/SQL which will query for all the table from user_objects and then execute statements to delete the data from the tables

Littlefoot · #4 (**permalink**) 08-12-04, 03:26

PL/SQL procedure would do the work indeed.

Perhaps another suggestion - write a query and spool its output to an .sql file and then run it. Such as:

Code:

> set heading off;
> set feedback off;
> 
> spool truncall.sql
> 
> select 'truncate table ' || tname ||';' from tab where tabtype = 'TABLE';
> 
> spool off;
> 
> @truncall

Why 'truncate' and not 'delete'? Delete saves all the deleted records in rollback segment(s) which slows things down.

However, you might need to run this script several times due to referential integrity constraints which might prevent some tables to be truncated (you can't delete parent while child exists).