Oracle-L: RE: PL/SQL & Analyze/validate

From: kkennedy <kkennedy_at_firstpoint.com>
Date: Thu, 27 Jun 2002 13:28:22 -0800
Message-ID: <F001.0048AF06.20020627132822@fatcity.com>

Have you considered "EXECUTE IMMEDIATE 'analyze table etc. etc. etc.'"?

Our statistics gathering procedure uses that method quite successfully. Maybe one day I'll get motivated enough and have time to convert to the DBMS_STATS package.

Kevin Kennedy
First Point Energy Corporation

-----Original Message-----
Sent: Thursday, June 27, 2002 1:04 PM
To: Multiple recipients of list ORACLE-L

I have been building a PL/SQL block to run an 'analyze table validate structure cascade' against a subset of tables in one schema of our database (8.1.7 - daily backup copy of production). I've initially set this up to use OPEN_CURSOR . . . PARSE . . . EXECUTE, but was looking for a prepackaged procedure to perform this step. After searching the PL/SQL ref guide, dbms_utility.analyze_part_object appears to be closest but is valid for partitioned objects only. Am I out of luck, or am I looking in the wrong place.

BTW, this is a corrupt block detection routine which needs to run in pieces as the production copy is refreshed each night. Running against all tables in the main schema would require at least 3 days. Also feel free to remind me that Export to /dev/null is an adequate test, and maybe I'll believe it this time ;)

Mike Hand
Polaroid Corp
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Hand, Michael T
INET: HANDM_at_polaroid.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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.com
--

Author: kkennedy
INET: kkennedy_at_firstpoint.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------