schedule rename partitions

Posted by Roshan on Sep 12, 2017; 10:34am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/schedule-rename-partitions-tp3462.html

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