Re: Maintenance on an older DB

jjolly972_at_comcast.net (Jack Jolly) wrote in message news:<8e5516d9.0302270512.1d645263_at_posting.google.com>...
> I have a 10G db (CAD data) thats about 3y old and to date has been
> running without incident. What kind of "preventative maintenance" can
> be run on this guy to make sure he stays healty?
>
> Thanks - Jack
> CAD Analyst
> Washington Group Int'l
> Princeton, NJ

It would have been nice to know the version of Oracle.

I would take a look at the space utilization to be sure that none of the tablespaces in nearing a no free space condition. See dba_tablespaces, dba_extents, and dba_free_space plus dba_segments in the Oracle version# Reference Manual for information on these views (manual exists since 7.3).

If the tablespaces are dictionary managed I would determine the database block size and verify that no object is approaching the max extents limit for 1- the object as defined and 2- the database blocksize limit though altering it to be unlimited might be an option depending of release level being 7.3+

Next for dictionary managed space you would need to make sure that an extent equal to the largest next extent for any object allocated to the tablespace is available. Perhaps several need to be available or the next extent size need changing on the object. Make sure all pctincrease values on large tables and indexes is set to zero. Actually I would want all objects to be using pctincrease 0, but you will have to make judgments based on what you have.

I would verify that the backup job is complete (covers all tablespace files) and is running successfully. This should probably be the first thing you do since ensuring proper backup and recovery is the primary job of a DBA.

I would check the optimizer mode in use and compare statistics if present to current actual data. Re-analyzing might be called for any table with significant row count differences between the actual and the statistics.

Repost with specific questions related to

HTH -- Mark D Powell -- Received on Thu Feb 27 2003 - 12:43:55 CST