Tim's script is not much of different except deleting from the stats$sqltext from original SPPURGE.sql . But if you see my mail below I do have a lot of other tables which needs to be purged periodically.
Thank You, Madhu Reddy -----Original Message----- Sent: Tuesday, June 10, 2003 9:50 PM To: Multiple recipients of list ORACLE-L Um, if I understand correctly, you're just trying to keep the volume down in your statspack tables. I use Tim Gorman's sppurpkg.sql package (on several different versions across several different operating systems). (www.evdbt.com) I have it set up to keep 14 days of data, but you can change that. It schedules the dbms_job for you. I just set up his job when I configure statspack on a new database, and then I never need to think about it again. You might take a look and see if it'll work for you. Barb --- "Reddy, Madhusudana" <[EMAIL PROTECTED]> wrote: > 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). __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: 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).
