SQl for deleting Table

[irehman]

Waht is the SQL syntax for deleting all the data from all the tables at onec.

Thanks in Advance

[Halo]

You can use: delete * from table_name; but this will generate a lot of redo and can take some time. Best thing to do is to truncate the table using the command: truncate table table_name; which does not generate any redo and is done in a second or two.

[marist89]

delete from xyz

or

truncate table xyz

[irehman]

Actually I was asking that I have over 700 tables in my database I want to delete all the data of every single table with only one query can I do That?

Thanks

[marist89]

set pages 0
set trimspool on
set feedback off
set linesize 222
spool jh1.sql
select 'truncate table ' || owner || '.' || table_name || ';'
from dba_tables
where owner not in ('SYS','SYSTEM')
spool off

Then look at jh1.sql and run it...

[Mr.Hanky]

Jeff is right but I would be damn sure before I ran that command. I would double check those table names, or at least verify all your usernames. Maybe create and export beforehand just to be safe.
MH

[marist89]

Amen to that!

Maybe I should change my signature to:
[disclaimer]
You must read, understand, and obey all safety rules when using power tools. And remember this: There is no more important safety device than these; safety glasses!
[/disclaimer]