schedule rename partitions

[Roshan]

Oracle Database 12c
Red Hat Linux

Hi Erman,

I have a script to rename all the system generated partitions.

renamepartition.txt

I want to schedule this script using jobs and scheduler

I have created a procedure for the script first

CREATE OR REPLACE
 
 
  PROCEDURE  renameproc1
 
 
    IS
Declare
  c_table_name  varchar2(30):='MOBILE_DATA'; --specify name of table
  c_table_owner varchar2(30):='ARCHICOM'; --you can specify owner
  v_highvalue   varchar2(8000);
  v_highdate    date;
  v_newname     varchar2(30);
Begin
  --DBMS_OUTPUT.ENABLE(1000000);
  for r1 in (
    select partition_name
    from all_tab_partitions
    where table_name=c_table_name and table_owner=c_table_owner
      and partition_name like 'P%'
  ) LOOP
    select high_value into v_highvalue from all_tab_partitions
    where table_name=c_table_name and table_owner=c_table_owner
      and partition_name=r1.partition_name;
    execute immediate 'select '||v_highvalue||' from dual' into v_highdate;
    v_newname:='MOBILE_INOCS_CBP_'||to_char(v_highdate-1,'YYYYMMDD'); -- or another notation
    --DBMS_OUTPUT.PUT_LINE(v_newname);
    execute immediate 'Alter table '||c_table_owner||'.'||c_table_name
        ||' rename partition '||r1.partition_name||' to '||v_newname;
  end LOOP;
End;
/

Then I created a scheduler and job


BEGIN
 
 
    DBMS_SCHEDULER.CREATE_SCHEDULE(
 
 
                                   schedule_name   => 'renamemobiledata',
 
 
                                   start_date      => TIMESTAMP '2017-09-12 14:20:00.000000 US/EASTERN',
 
 
                                   repeat_interval => 'FREQ=MONTHLY; INTERVAL=1'
 
 
                                  );
 
 
    DBMS_SCHEDULER.CREATE_JOB(
 
 
                              job_name      => 'job_mobiledata',
 
 
                              job_type      => 'STORED_PROCEDURE',
 
 
                              job_action    => 'renameproc1',
 
 
                              schedule_name => 'renamemobiledata',
 
 
                              enabled       => TRUE
 
 
                             );
 
 
    COMMIT;
 
 
END;
 
 
/

The error I am getting is when executing the job:
BEGIN
EXECUTE DBMS_SCHEDULER.RUN_JOB('job_mobiledata');

END;
/

SQL> BEGIN
EXECUTE DBMS_SCHEDULER.RUN_JOB('job_mobiledata');

END;
/  2    3    4    5
EXECUTE DBMS_SCHEDULER.RUN_JOB('job_mobiledata');
        *
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00103: Encountered the symbol "DBMS_SCHEDULER" when expecting one of the
following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "DBMS_SCHEDULER" to continue.


Regards,

Roshan

[ErmanArslansOracleBlog]

remove the "execute" from there, Try with the following:

BEGIN
DBMS_SCHEDULER.RUN_JOB('job_mobiledata');
END;

[Roshan]

DBMS_SCHEDULER.RUN_JOB('job_mobiledata');
END;  2    3
  4  /
BEGIN
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
ORA-06512: at "SYS.DBMS_ISCHED", line 196
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

[ErmanArslansOracleBlog]

create the job with the schema user, which you use for executing dbms_scheduler.run_job.

I mean, suppose your DB user is XXERMAN;

recreate your jobs in the XXERMAN schema
run "BEGIN DBMS_SCHEDULER.RUN_JOB"  from XXERMAN.

Update me with the outcome.
 

[Roshan]

SQL> BEGIN
DBMS_SCHEDULER.RUN_JOB('job_mobiledata');
END;  2    3
  4  /
BEGIN
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
ORA-06512: at "SYS.DBMS_ISCHED", line 196
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

[Roshan]

The syntax was wrong. Now it is ok

SQL> BEGIN
  DBMS_SCHEDULER.RUN_JOB(
    JOB_NAME            => 'mobiledataJ'
,
    USE_CURRENT_SESSION => FALSE);
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

https://docs.oracle.com/html/E25494_01/scheduse002.htm#i1021522

[Roshan]

Hi,

the error

SQL> BEGIN
EXECUTE DBMS_SCHEDULER.RUN_JOB('job_mobiledata');

END;
/  2    3    4    5
EXECUTE DBMS_SCHEDULER.RUN_JOB('job_mobiledata');
        *
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00103: Encountered the symbol "DBMS_SCHEDULER" when expecting one of the
following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "DBMS_SCHEDULER" to continue.



is due to the 'Declare' part which should not have been included in the procedure. Instead, we should have  replaced it with 'IS'

Now it is executing properly.

Thanks for support.

Regards,
Roshan