SQl for deleting Table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQl for deleting Table

  1. #1
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Waht is the SQL syntax for deleting all the data from all the tables at onec.

    Thanks in Advance

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    delete from xyz

    or

    truncate table xyz
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    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

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    Then look at jh1.sql and run it...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131

    Lightbulb

    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

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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]
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width