-- 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 ;