Use an Oracle database job to analyze the state_lineages table

Ensure one SNP process is running to execute queued jobs in the background. SNP processes will periodically wake up and execute any queued jobs. SNP background processes differ from other Oracle background processes in that the failure of an SNP process does not cause the instance to fail. If an SNP process fails, Oracle will restart it.

SNP background processes will not execute jobs if the system has been started in restricted mode. You can, however, use the ALTER SYSTEM statement to turn this behavior on and off as follows:

Code:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION; 

Creating the database job requires performing two steps: creating the stored procedure to be executed by the job, and creating the job that executes the procedure.

In SQL*Plus, as the SDE user, create the stored procedure that the database job will execute:

Code:
CREATE OR REPLACE PROCEDURE s_lineage_analyze IS

sqlstm VARCHAR2(64);

 BEGIN
   sqlstm := 'ANALYZE TABLE state_lineages COMPUTE STATISTICS';
   EXECUTE IMMEDIATE sqlstm;
 END;
 / 

You could simply execute the new stored procedure when they needed to update the table's statistics:

Code:
EXECUTE s_lineage_analyze; 

It is better, however, to use a database job that can execute the procedure on a daily basis. Create the database job by submitting the request to the Oracle instance in SQL*Plus.

1. Declare a variable called 'jobno' to store the number of the job when you submit the request.

Code:
VARIABLE jobno number; 

2. Submit the database job by using the SYS.DBMS_JOB package. If your user does not have permissions to execute the procedure, connect as the SYS user and grant the appropriate privileges. This example submits a job to execute the 'sde.s_lineage_analye' procedure every night at midnight.

Code:
BEGIN
   DBMS_JOB.SUBMIT
      (:jobno,
      'sde.s_lineage_analyze;', 
      trunc(sysdate) + 1,
      'SYSDATE + 1');
   COMMIT;
END;
/ 

Once submitted, you can print the value of the jobno variable.

Code:
PRINT jobno; 

Use the 'jobno' to execute the database job. This will confirm that the job has been submitted and works correctly.

Code:
EXECUTE dbms_job.run(jobno); 

Alternatively, you can select from user_jobs for the job's relevant information:

Code:
SELECT job, next_date, interval, failures, broken FROM user_jobs; 

Note:
Portions of this article are from Oracle's Documentation. It is not intended to replace or supersede Oracle documentation. Please refer to Oracle documentation for additional information and further clarifications.