Truncate multiple tables script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Truncate multiple tables script

  1. #1
    31006558 Guest

    Truncate multiple tables script

    I'm having a problem with a truncate script. I want to truncate all tables owned by 'PERFSTAT' (This is a test I want to run before the real thing).

    My script:

    select 'truncate table ' || owner || '.' || table_name || ' cascade constraints; '
    from sys.all_all_tables
    where owner in ('PERFSTAT')

    IF A RUN A SIMPLE SELECT STATEMENT AFTER THIS EXECUTION, NOTHING WAS TRUNCATED

    Please help!

    NB:this script was previously used for 'drop table' with success.

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Why don't you use SPTRUNC.SQL script, it truncates all performance data in Statspack tables..

    Sameer

  3. #3
    Join Date
    Nov 2002
    Posts
    39
    I believe, there is no option cascade constraints in Truncate table.
    Do this excercise.

    1. spool trunc.sql
    2. select 'truncate table ' || owner || '.' || table_name || ';' from sys.all_all_tables where owner in ('PERFSTAT');

    Caution: The above will truncate all the tables owned by PERFSTAT.

    2. spool off
    3. Edit the trunc.sql and remove the unwanted scripts.
    4. Then, finally run the trunc.sql

    Hope this will work.
    Sathish

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Is it poossible that all you are doing is just running the SELECT, and not the script generated by the SELECT?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    31006558 Guest
    thanks to username slimdave...it is a plain select with the output used to truncate every table individualy.

    thanks to username sai_sathish...will try your reply!!

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