Waht is the SQL syntax for deleting all the data from all the tables at onec.
Thanks in Advance
Printable View
Waht is the SQL syntax for deleting all the data from all the tables at onec.
Thanks in Advance
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.
delete from xyz
or
truncate table xyz
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
Then look at jh1.sql and run it...Code: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
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
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]