Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    370

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

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Feb 2004
    Location
    Eternity
    Posts
    31

    No simple statement available

    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
    Obviousness is the enemy of Correctness
    --Bertand Russel

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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).

  5. #5
    Join Date
    Mar 2004
    Posts
    370

    Red face Thanx but my q was another thing!

    Thanx all for help,
    but I want to delete all the records from all my tables not truncating all tables.Any idea?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What difference do you see between deleting all of the rows and truncating the table?

    -PatP

Posting Permissions

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