• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1583
  • Last Modified:

dbms_job.change syntax

This is a jobno, what from  dba_jobs:

       188
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'CMS_FILES_ORG',
   cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');


I need to change the CMS_FILES_ORG to CMS_FILES_ORGS
What would be the dbms_job_change setup to do that?
0
xoxomos
Asked:
xoxomos
  • 3
  • 2
2 Solutions
 
xoxomosAuthor Commented:
When i try:


SQL> exec dbms_job.change(188,dbms_stats.gather_schema_stats(ownname=>'CMS_FILES_ORGS', cascade= TRUE, method_opt= 'FOR ALL INDEXED COLUMNS SIZE AUTO'),null, null, null, null);
BEGIN dbms_job.change(188,dbms_stats.gather_schema_stats(ownname=>'CMS_FILES_ORGS', cascade= TRUE, method_opt= 'FOR ALL INDEXED COLUMNS SIZE AUTO'),null, null, null, null); END;

                                                                                    *
ERROR at line 1:
ORA-06550: line 1, column 85:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
0
 
abuckheitCommented:
make sure your doing => and not =

cascade= TRUE, method_opt= 'FOR

those are wrong
0
 
YANN0SCommented:
First of all since you want to change only "what" of the job, you can use dbms_job.what

  PROCEDURE what      ( job       IN  BINARY_INTEGER,
                        what      IN  VARCHAR2 );
  -- Change what an existing job does, and replace its environment

Second in both procedures (change and what) what parameter is varchart2

So you should call:

dbms_job.what(188, 'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>''CMS_FILES_ORGS'',
   cascade=>TRUE, method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'');');
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
xoxomosAuthor Commented:
Yes, that looks like it but i must still be doing syntax wrong.
SQL> exec dbms_job.what(188,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>"CMS_FILES_ORGS",cascade=TRUE,method_opt=>"FOR ALL INDEXED COLUMNS SIZE AUTO");');
BEGIN dbms_job.what(188,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>"CMS_FILES_ORGS",cascade=TRUE,method_opt=>"FOR ALL INDEXED COLUMNS SIZE AUTO");'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 175:
PLS-00114: identifier 'FOR ALL INDEXED COLUMNS SIZE A' too long
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 205
ORA-06512: at line 1
0
 
YANN0SCommented:
I don't know if that  is the problem, but '' in my example is two single quotes not double.
0
 
xoxomosAuthor Commented:
Yeah, i read it should be two singles and everything looks like it's supposed to!!!!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now