Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ANALYZE statement as reoccurring job via dbms_job
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