Reddy - You didn't mention your Oracle version. I am on 8.1.6 and I don't think those scripts are available there (sorry, busy day, no time to research). So I just delete from stats$snapshot. It seems to remove data from the associated tables just fine. First I select the snap_id and snap_time from stats$snapshot to determine which snapshots to remove. It runs slowly (because of all the child tables?), so I just remove a hundred or so at a time.
Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Tuesday, June 10, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Hello All, We have a job ( shell Script ) which deletes from the statspack tables every Sunday, but uses SPPURGE.sql ( $ORACLE_HOME/rdbms/admin). Seems like it is not deleting all tables and hence causing the tablespace to grow. Does anybody has a different approach which deletes all table without any referential integrity issues ?? I do not want to use SPTRUNC.sql please. Pl find below some of the statspack tables are growing so big ( also Indexes /PKs ). STATS$BG_EVENT_SUMMARY 7 STATS$ROWCACHE_SUMMARY 8 STATS$SGASTAT 8 STATS$LATCH_PARENT 10 STATS$SYSTEM_EVENT 19 STATS$ROLLSTAT 37 STATS$LATCH 50 STATS$SYSSTAT 51 TABLE Size in MB ------------------------------ ---------- STATS$PARAMETER 56 STATS$LATCH_MISSES_SUMMARY 84 STATS$SQLTEXT 122 STATS$FILESTATXS 234 STATS$SQL_SUMMARY 886 STATS$LATCH_CHILDREN 2872 ( These are just tables , didnt show indexes here) Thank You , Madhu Reddy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
