Monday, August 2, 2021

How to work around ORA-38323: policy conflicts with policy

You try to enable a tablespace-wide ADO policy:
ORA-38323: policy conflicts with policy 42
If you lookup the error with oerr ora 38323, oracle will give you the following solution:
* Cause: An attempt was made to create a policy with the same action
         type and based on the same statistic as another policy on the
         object.
 *Action: Use a different action and/or statistic for the new policy,
          or delete the old policy.
Verify that there is a policy on the tablespace already:
SELECT * 
FROM DBA_ILMPOLICIES 
WHERE TABLESPACE IS NOT NULL;
Result:
POLICY_NAME POLICY_TYPE TABLESPACE ENABLED DELETED
P43 DATA MOVEMENT DATA1 YES NO

Theres is indeed a policy named P43. What kind of policy is it?
SELECT policy_name,action_type,scope,compression_level,condition_type,condition_days,policy_subtype
FROM DBA_ILMDATAMOVEMENTPOLICIES 
WHERE POLICY_NAME IN (SELECT POLICY_NAME FROM DBA_ILMPOLICIES WHERE TABLESPACE IS NOT NULL); 
Result:
POLICY_NAME ACTION_TYPE SCOPE COMPRESSION_LEVEL CONDITION_TYPE CONDITION_DAYS POLICY_SUBTYPE
P43 COMPRESSION GROUP ADVANCED LAST ACCESS TIME
1
DISK

So the policy is a directive to the oracle server to compress all objects after one day of no access. If you need to alter this policy, the old one must be dropped:
ALTER TABLESPACE DATA1 DEFAULT ILM DELETE POLICY P43;
You are now free to add a new ADO policy to the tablespace:
ALTER TABLESPACE DATA1
DEFAULT ILM ADD POLICY
ROW STORE COMPRESS ADVANCED
GROUP AFTER 30 DAYS OF NO ACCESS;

No comments:

Post a Comment