Stored Procedure Job Locked

[THoey]

We were doing some maintenance and noticed that a stored procedure run through DBMS_JOB that analyzes a schema was taking way to long to run. The code for the stored procedure is:

CREATE OR REPLACE  PROCEDURE 
    "SMFRPT_PROD"."ANALYZEREPORTSOBJECTS" 
IS
BEGIN
   DBMS_UTILITY.ANALYZE_SCHEMA ('SMFRPT_PROD', 'COMPUTE');
END AnalyzeReportsObjects;

We determined that the reason it is taking so long is that one of the tables has over 200 million rows and it was taking a bit of time to COMPUTE the statistics. When that job was created it wasn't known by the creator there would be that many rows, so ESTIMATE was not used, but now, that is what we want to use. The job was running at the time, so the DBA killed off the job.

First question, we are unable to alter the stored procedure, it appears as if it is locked. We think the COMPUTE statistics is rolling back. Does that make sense? The job shows killed, but it also appears on the lock list in OEM.

Second question, I verified the syntax that we want to use and the specification shows the following:

  procedure analyze_schema(
	schema 		 varchar2, 
	method 		 varchar2, 
    	estimate_rows    number   default null, 
    	estimate_percent number   default null, 
	method_opt 	 varchar2 default null);

Description:
  Analyze all the tables, clusters and indexes in a schema.

  Input arguments:
    schema
      Name of the schema.
                      
    method
      One of ESTIMATE, COMPUTE or DELETE. 
      If ESTIMATE, then either estimate_rows or estimate_percent 
      must be non-zero.  

    estimate_rows
      Number of rows to estimate.  

    estimate_percent
      Percentage of rows to estimate. 
      If estimate_rows is specified, then this parameter is ignored.  

    method_opt
      Method options of the following format: 
		[ FOR TABLE ]
		[ FOR ALL [INDEXED] COLUMNS] [SIZE n]
		[ FOR ALL INDEXES ]

The code I was writing was:

CREATE OR REPLACE PROCEDURE ANALYZEREPORTSOBJECTS IS
BEGIN
   DBMS_UTILITY.ANALYZE_SCHEMA ('SMFRPT_PROD', 
                                'ESTIMATE', 
                                0, 
                                10, 
                                '');
END AnalyzeReportsObjects;

I am not sure what to use for the method_opt. Any suggestions?

Third and final question. In the not so distant future, this database schema is being migrated by itself to a new database server using EXP. Do jobs in the DBMS_JOB queue get migrated that way or will they have to be recreated?

Sorry, I know this was long with several questions. Any help would be appreciated.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...