schedule rename partitions

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

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
Reply | Threaded
Open this post in threaded view
|

Re: schedule rename partitions

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

BEGIN
DBMS_SCHEDULER.RUN_JOB('job_mobiledata');
END;
Reply | Threaded
Open this post in threaded view
|

Re: schedule rename partitions

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
Reply | Threaded
Open this post in threaded view
|

Re: schedule rename partitions

ErmanArslansOracleBlog
Administrator
In reply to this post by 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.
 
Reply | Threaded
Open this post in threaded view
|

Re: schedule rename partitions

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
Reply | Threaded
Open this post in threaded view
|

Re: schedule rename partitions

Roshan
In reply to this post by ErmanArslansOracleBlog
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