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).
select 'truncate table ' || owner || '.' || table_name || ' cascade constraints; '
where owner in ('PERFSTAT')
IF A RUN A SIMPLE SELECT STATEMENT AFTER THIS EXECUTION, NOTHING WAS TRUNCATED
NB:this script was previously used for 'drop table' with success.
Why don't you use SPTRUNC.SQL script, it truncates all performance data in Statspack tables..
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.
Is it poossible that all you are doing is just running the SELECT, and not the script generated by the SELECT?
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!!
Click Here to Expand Forum to Full Width