ADO table movement error

[Roshan]

Oracle Database 12.2.0
Red Hat Enterprise Linux 7.4

Hi Erman,


I am testing the ILM policy as from

http://blog.sqlora.com/en/ilm-storage-tiering/

My aim is to first move table to another tablespace and later test compression levels.

There is a table called EMP located in USERS tablespace. I would like some help to move it to low_cost_store tablespace as in the tutorial.

select distinct(to_char(time,'month')) from BIMOBDATAPREP;

select * from dba_tables where table_name='EMP';
--Tablespace_name: USERS
--Owner: scott

ALTER TABLE scott.emp   ILM ADD POLICY TIER TO low_cost_store READ ONLY   SEGMENT AFTER 10 DAYS OF NO MODIFICATION;


SELECT policy_name, action_type, scope, tier_tablespace, condition_type, condition_days   FROM user_ilmdatamovementpolicies;
--no rows returned

SELECT policy_name, object_name, object_type, enabled FROM user_ilmobjects;
--no rows returned
  commit;
 
  declare

    v_executionid number;

    begin
      dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,
            execution_mode => dbms_ilm.ilm_execution_offline,
            task_id   => v_executionid);
    end;
    /
--no tables in low_cost tablespace

Thanks and Regards,

Roshan

[Roshan]

Issue is solved now. My mistake was the ILM movement task command needs to be run as 'BIREPORT' user and not sys user.

[ErmanArslansOracleBlog]

Good for you Roshan :)
Thanks for sharing