-- select 'truncate table '|| owner|| '.' || table_name || ' drop storage ;' 
  -- from  dba_tab_columns 
 -- where owner='SYS' 
   -- and column_name ='SNAP_ID'
   -- and table_name NOT like 'DBA_%'
   -- and table_name not like 'WRM$%'
-- order by owner, table_name; 

ALTER SESSION SET DDL_lock_timeout=999;

WHENEVER SQLERROR EXIT

truncate table SYS.WRH$_ACTIVE_SESSION_HISTORY drop storage ;
truncate table SYS.WRH$_ACTIVE_SESSION_HISTORY_BL drop storage ;
truncate table SYS.WRH$_BG_EVENT_SUMMARY drop storage ;
truncate table SYS.WRH$_BUFFERED_QUEUES drop storage ;
truncate table SYS.WRH$_BUFFERED_SUBSCRIBERS drop storage ;
truncate table SYS.WRH$_BUFFER_POOL_STATISTICS drop storage ;
truncate table SYS.WRH$_CLUSTER_INTERCON drop storage ;
truncate table SYS.WRH$_COMP_IOSTAT drop storage ;
truncate table SYS.WRH$_CR_BLOCK_SERVER drop storage ;
truncate table SYS.WRH$_CURRENT_BLOCK_SERVER drop storage ;
truncate table SYS.WRH$_DATAFILE drop storage ;
truncate table SYS.WRH$_DB_CACHE_ADVICE drop storage ;
truncate table SYS.WRH$_DB_CACHE_ADVICE_BL drop storage ;
truncate table SYS.WRH$_DISPATCHER drop storage ;
truncate table SYS.WRH$_DLM_MISC drop storage ;
truncate table SYS.WRH$_DLM_MISC_BL drop storage ;
truncate table SYS.WRH$_DYN_REMASTER_STATS drop storage ;
truncate table SYS.WRH$_ENQUEUE_STAT drop storage ;
truncate table SYS.WRH$_EVENT_HISTOGRAM drop storage ;
truncate table SYS.WRH$_EVENT_HISTOGRAM_BL drop storage ;
truncate table SYS.WRH$_FILEMETRIC_HISTORY drop storage ;
truncate table SYS.WRH$_FILESTATXS drop storage ;
truncate table SYS.WRH$_FILESTATXS_BL drop storage ;
truncate table SYS.WRH$_IC_CLIENT_STATS drop storage ;
truncate table SYS.WRH$_IC_DEVICE_STATS drop storage ;
truncate table SYS.WRH$_INSTANCE_RECOVERY drop storage ;
truncate table SYS.WRH$_INST_CACHE_TRANSFER drop storage ;
truncate table SYS.WRH$_INST_CACHE_TRANSFER_BL drop storage ;
truncate table SYS.WRH$_INTERCONNECT_PINGS drop storage ;
truncate table SYS.WRH$_INTERCONNECT_PINGS_BL drop storage ;
truncate table SYS.WRH$_IOSTAT_DETAIL drop storage ;
truncate table SYS.WRH$_IOSTAT_FILETYPE drop storage ;
truncate table SYS.WRH$_IOSTAT_FUNCTION drop storage ;
truncate table SYS.WRH$_JAVA_POOL_ADVICE drop storage ;
truncate table SYS.WRH$_LATCH drop storage ;
truncate table SYS.WRH$_LATCH_BL drop storage ;
truncate table SYS.WRH$_LATCH_CHILDREN drop storage ;
truncate table SYS.WRH$_LATCH_CHILDREN_BL drop storage ;
truncate table SYS.WRH$_LATCH_MISSES_SUMMARY drop storage ;
truncate table SYS.WRH$_LATCH_MISSES_SUMMARY_BL drop storage ;
truncate table SYS.WRH$_LATCH_PARENT drop storage ;
truncate table SYS.WRH$_LATCH_PARENT_BL drop storage ;
truncate table SYS.WRH$_LIBRARYCACHE drop storage ;
truncate table SYS.WRH$_LOG drop storage ;
truncate table SYS.WRH$_MEMORY_RESIZE_OPS drop storage ;
truncate table SYS.WRH$_MEMORY_TARGET_ADVICE drop storage ;
truncate table SYS.WRH$_MEM_DYNAMIC_COMP drop storage ;
truncate table SYS.WRH$_MTTR_TARGET_ADVICE drop storage ;
truncate table SYS.WRH$_MUTEX_SLEEP drop storage ;
truncate table SYS.WRH$_MVPARAMETER drop storage ;
truncate table SYS.WRH$_MVPARAMETER_BL drop storage ;
truncate table SYS.WRH$_OPTIMIZER_ENV drop storage ;
truncate table SYS.WRH$_OSSTAT drop storage ;
truncate table SYS.WRH$_OSSTAT_BL drop storage ;
truncate table SYS.WRH$_PARAMETER drop storage ;
truncate table SYS.WRH$_PARAMETER_BL drop storage ;
truncate table SYS.WRH$_PERSISTENT_QMN_CACHE drop storage ;
truncate table SYS.WRH$_PERSISTENT_QUEUES drop storage ;
truncate table SYS.WRH$_PERSISTENT_SUBSCRIBERS drop storage ;
truncate table SYS.WRH$_PGASTAT drop storage ;
truncate table SYS.WRH$_PGA_TARGET_ADVICE drop storage ;
truncate table SYS.WRH$_PROCESS_MEMORY_SUMMARY drop storage ;
truncate table SYS.WRH$_RESOURCE_LIMIT drop storage ;
truncate table SYS.WRH$_ROWCACHE_SUMMARY drop storage ;
truncate table SYS.WRH$_ROWCACHE_SUMMARY_BL drop storage ;
truncate table SYS.WRH$_RSRC_CONSUMER_GROUP drop storage ;
truncate table SYS.WRH$_RSRC_PLAN drop storage ;
truncate table SYS.WRH$_RULE_SET drop storage ;
truncate table SYS.WRH$_SEG_STAT drop storage ;
truncate table SYS.WRH$_SEG_STAT_BL drop storage ;
truncate table SYS.WRH$_SEG_STAT_OBJ drop storage ;
truncate table SYS.WRH$_SERVICE_NAME drop storage ;
truncate table SYS.WRH$_SERVICE_STAT drop storage ;
truncate table SYS.WRH$_SERVICE_STAT_BL drop storage ;
truncate table SYS.WRH$_SERVICE_WAIT_CLASS drop storage ;
truncate table SYS.WRH$_SERVICE_WAIT_CLASS_BL drop storage ;
truncate table SYS.WRH$_SESSMETRIC_HISTORY drop storage ;
truncate table SYS.WRH$_SESS_TIME_STATS drop storage ;
truncate table SYS.WRH$_SGA drop storage ;
truncate table SYS.WRH$_SGASTAT drop storage ;
truncate table SYS.WRH$_SGASTAT_BL drop storage ;
truncate table SYS.WRH$_SGA_TARGET_ADVICE drop storage ;
truncate table SYS.WRH$_SHARED_POOL_ADVICE drop storage ;
truncate table SYS.WRH$_SHARED_SERVER_SUMMARY drop storage ;
truncate table SYS.WRH$_SQLSTAT drop storage ;
truncate table SYS.WRH$_SQLSTAT_BL drop storage ;
truncate table SYS.WRH$_SQLTEXT drop storage ;
truncate table SYS.WRH$_SQL_BIND_METADATA drop storage ;
truncate table SYS.WRH$_SQL_PLAN drop storage ;
truncate table SYS.WRH$_SQL_SUMMARY drop storage ;
truncate table SYS.WRH$_SQL_WORKAREA_HISTOGRAM drop storage ;
truncate table SYS.WRH$_STREAMS_APPLY_SUM drop storage ;
truncate table SYS.WRH$_STREAMS_CAPTURE drop storage ;
truncate table SYS.WRH$_STREAMS_POOL_ADVICE drop storage ;
truncate table SYS.WRH$_SYSMETRIC_HISTORY drop storage ;
truncate table SYS.WRH$_SYSMETRIC_SUMMARY drop storage ;
truncate table SYS.WRH$_SYSSTAT drop storage ;
truncate table SYS.WRH$_SYSSTAT_BL drop storage ;
truncate table SYS.WRH$_SYSTEM_EVENT drop storage ;
truncate table SYS.WRH$_SYSTEM_EVENT_BL drop storage ;
truncate table SYS.WRH$_SYS_TIME_MODEL drop storage ;
truncate table SYS.WRH$_SYS_TIME_MODEL_BL drop storage ;
truncate table SYS.WRH$_TABLESPACE drop storage ;
truncate table SYS.WRH$_TABLESPACE_SPACE_USAGE drop storage ;
truncate table SYS.WRH$_TABLESPACE_STAT drop storage ;
truncate table SYS.WRH$_TABLESPACE_STAT_BL drop storage ;
truncate table SYS.WRH$_TEMPFILE drop storage ;
truncate table SYS.WRH$_TEMPSTATXS drop storage ;
truncate table SYS.WRH$_THREAD drop storage ;
truncate table SYS.WRH$_UNDOSTAT drop storage ;
truncate table SYS.WRH$_WAITCLASSMETRIC_HISTORY drop storage ;
truncate table SYS.WRH$_WAITSTAT drop storage ;
truncate table SYS.WRH$_WAITSTAT_BL drop storage ;

