Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ANALYZE statement as reoccurring job via dbms_job

Re: ANALYZE statement as reoccurring job via dbms_job

From: Sybrand Bakker <gooiditweg_at_nospam.demon.nl>
Date: Thu, 27 Mar 2003 03:00:07 +0100
Message-ID: <9mc48v4cp7qsidbfju4db4m3plpui1urv8@4ax.com>


On 26 Mar 2003 12:02:45 -0800, jsmith_at_swanstone.com (Jason) wrote:

>Oracle 8.0.5.x
>
>I have a sql statement that analyzes a particualr table in my database
>as follows:
>
>ANALYZE TABLE <SCHEMA.TABLENAME> COMPUTE STATISTICS FOR TABLE FOR ALL
>INDEXES FOR ALL INDEXED COLUMNS;
>
>I run this daily as user SYS for a table in one user schema.
>
>I would like to create a stored procedure for this statement and then
>run this each day at 3am via dbms_job.
>
>I know that I need to have job_queue_processes and job_queue_interval
>included in my init.ora file.
>
>I know to submit the job I would use something like the following:
>
>>declare
>>l_job number;
>>begin
>>dbms_job.submit(l_job,
>> 'name_of_procedure;'
>> trunc(sysdate)+1+3/24,
>> 'trunc(sysdate)+1+3/24' );
>>end;
>>/
>
>
>But I'm not sure how to convert my sql statement into a procedure.
>
>>create or replace procedure name_of_procedure
>>as
>>beigin
>> ???????????
>
>Any help appreciated.

Just use

ANALYZE_OBJECT Procedure
This procedure provides statistics for the given table, index, or cluster. It is equivalent to the following SQL statement:

ANALYZE TABLE|CLUSTER|INDEX [<schema>.]<name> [<method>] STATISTICS [SAMPLE <n>
[ROWS|PERCENT]] Syntax
DBMS_DDL.ANALYZE_OBJECT (

   type             VARCHAR2, 
   schema           VARCHAR2, 
   name             VARCHAR2, 
   method           VARCHAR2, 
   estimate_rows    NUMBER   DEFAULT NULL, 
   estimate_percent NUMBER   DEFAULT NULL,
   method_opt       VARCHAR2 DEFAULT NULL,
   partname         VARCHAR2 DEFAULT NULL);


and don't worry further

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Mar 26 2003 - 20:00:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US