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;

How to add a default ADO compression policy to a tablespace

Create the tablespace:

CREATE BIGFILE TABLESPACE DATA1 DATAFILE
  '/oradata/mydb/data1.dbf' SIZE 1024M AUTOEXTEND ON NEXT 256M MAXSIZE 2T
DEFAULT
COMPRESS FOR OLTP
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT ILM ADD POLICY 
COMPRESS FOR ALL OPERATIONS 
GROUP 
AFTER 1 DAYS OF NO ACCESS;

Notice the "GROUP" keyword. It states the scope of an ADO policy, and can be a group of related objects, segment level or row-level, indicated by the keywords GROUP, ROW, or SEGMENT.

According to the documentation, by default, this will give you advanced compression on heap tables, standard compression for indexes and LOW for LOB segments created in the tablespace.

Any table created in this tablespace from now on will now inherit the ADO policy. Let's see it in action:

create table sh.test_ado_tabspc_compression as select * from sh.sales;

Table created.

Verify:

SELECT policy_name,
       object_owner,
       object_name,
       subobject_name,
       object_type,
       inherited_from,
       enabled,
       deleted
FROM   dba_ilmobjects
WHERE  object_owner='SH'
AND    object_name='SALES'
ORDER BY 1;

Result:

POLICY_NAME	OBJECT_OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	INHERITED_FROM	ENABLED	DELETED
P43	SH	TEST_ADO_TABSPC_COMPRESSION		TABLE	TABLESPACE	YES	NO

The table has inherited the ADO policy from the tablespace it was created in.

How to add an ADO compression policy to a table partition

ADO (Automatic Data Optimization) is one component of the Oracle 12c ILM (Information Life Cycle Management) solution.
For ADO to work, access to and modification of data needs to be constantly tracked. This is done by enabling a heat map in your database:

alter system set heat_map=on scope=both;

The database will immediately start sampling information. After a while, the heat map will contain information about how your objects are being used.

My table contains 211 partitions. I would like to add an ADO policy to one of them to illustrate how ADO policies works.
Let's list the 3 largest partitions:

select p.TABLE_NAME, s.partition_name,s.tablespace_name, p.compression,p.num_rows,Round(sum(s.bytes)/1024/1024/1024,1) "GB"
from dba_segments S join dba_tab_partitions p
on (s.partition_name = p.partition_name)
where s.segment_name='ENTITIY_PR'
and   s.owner='SSB'
group by p.table_name,s.partition_name,s.tablespace_name,p.compression,p.num_rows
order by 5 desc
fetch first 3 rows only;

TABLE_NAME	PARTITION_NAME	TABLESPACE_NAME	COMPRESSION	NUM_ROWS	GB
ENTITIY_PR	SYS_P5055	SSB	DISABLED	43448193	3,3
ENTITIY_PR	SYS_P4518	SSB	DISABLED	43447546	3,4
ENTITIY_PR	SYS_P4709	SSB	DISABLED	43217045	3,3

Add a policy to have Oracle automatically compress the partition segment after one day without any access (read and write):

  
alter table SSB.ENTITIY_PR 
modify partition SYS_P5055 ilm add policy 
row store compress advanced segment after 1 days of no access;

In addition to no access, you could also use low access, no modification or creation to express activity type

Verify that the ADO policy is in place:

SELECT policy_name,
       object_owner,
       object_name,
       subobject_name,
       object_type,
       inherited_from,
       enabled,
       deleted
FROM   dba_ilmobjects
WHERE  object_owner='SSB'
AND    object_name='ENTITIY_PR'
AND    subobject_name='SYS_P5055'
ORDER BY 1;

POLICY_NAME	OBJECT_OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	INHERITED_FROM	ENABLED	DELETED
P22	SSB	ENTITIY_PR	SYS_P5055	TABLE PARTITION	POLICY NOT INHERITED	YES	NO

What does the heat map say about this particular partition?

select * 
from dba_heat_map_segment
where owner='SSB'
and   object_name='ENTITIY_PR' 
AND   subobject_name='SYS_P5055';

No rows returned.


So this partitioned hasn't actually been modified or accessed since the heat map was enabled.


Let's access the partition with a query, and see if this will be registered in the heat map.
SELECT SYSTIMESTAMP FROM DUAL; --> 25-JUN-21 10.04.30

SELECT *
FROM   SSB.ENTITIY_PR
PARTITION (SYS_P5055)
fetch first 10 rows only;

Run the query against dba_heat_map_segment again, and there is now an entry in the heat map for my visit to the table:



  

  
OWNER
  
OBJECT_NAME
  
SUBOBJECT_NAME
  
SEGMENT_WRITE_TIME
  
SEGMENT_READ_TIME
  
FULL_SCAN
  
LOOKUP_SCAN
  
  
    
SSB
    
ENTITIY_PR
    
SYS_P5055
    
 
    
25.06.2021 10:04:34
    
25.06.2021 10:04:34
    
 
  


Notice that the column "LOOKUP_SCAN" has no value.


Let's try to send a query to the database that will require an PK index lookup:
SELECT *
FROM   SBB.ENTITIY_PR
PARTITION (SYS_P5055)
WHERE systemid = 1145708618;

Run the query against dba_heat_map_segment again, and you can now see that the lookup was recorded in the heat map:



  

  
OWNER
  
OBJECT_NAME
  
SUBOBJECT_NAME
  
SEGMENT_WRITE_TIME
  
SEGMENT_READ_TIME
  
FULL_SCAN
  
LOOKUP_SCAN
  
  
    
SBB
    
ENTITIY_PR
    
SYS_P5055
    
 
    
25.06.2021 10:25:33
    
25.06.2021 10:25:33
    
25.06.2021 10:25:33
  


If you wonder why the timestamps in dba_heat_map_segment are incrementing, although you're not executing any queries, it is because the object is still in the shared pool.
There is no such thing as purging a table from the shared pool. However, you can purge the cursors that refer to it.
To find those cursors, use the following query:
set lines 200
spool purge.sql
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on

select 'exec DBMS_SHARED_POOL.PURGE (''' || ADDRESS ||','|| HASH_VALUE || ''',''C'');'
from V$SQLAREA where SQL_ID IN (SELECT SQL_ID FROM v$sqlarea where sql_text like 'SELECT * FROM%SBB.ENTITIY_PR%SYS_P5055%');
select 'exit' from dual;
exit


Run the script and it will spool a file "purge.sql" to your current directory, which looks like this in my case:
exec DBMS_SHARED_POOL.PURGE ('00000000BF56D560,1541949375','C');
exec DBMS_SHARED_POOL.PURGE ('00000000C67994B8,3904135316','C');
exec DBMS_SHARED_POOL.PURGE ('00000000C67973D8,27827680','C');

Run these statements, and the cursors refering to ENTITY_PR will be removed from memory, and the incrementing timestamps will stop.
Obviously, if other users are actually using the object, you would need to be careful about purging the shared pool.


After one full day of no access, let's check if the ADO policy has done its work:
select p.TABLE_NAME, s.partition_name,s.tablespace_name, p.compression,p.num_rows,Round(sum(s.bytes)/1024/1024/1024,1) "GB"
from dba_segments S join dba_tab_partitions p
on (s.partition_name = p.partition_name)
where s.segment_name='ENTITIY_PR'
and   s.owner='SSB'
and   s.partition_name in ( 'SYS_P5055')
and   s.segment_name = p.table_name
group by p.table_name,s.partition_name,s.tablespace_name,p.compression,p.num_rows
order by 5 desc;

The output confirms that the partition has been compressed, and if you check the output from the same query earlier in this post, 
you'll notice that the size of the partition has dropped from 3,3G to 1,3G, a 60% reduction:



  

  
TABLE_NAME
  
PARTITION_NAME
  
TABLESPACE_NAME
  
COMPRESSION
  
NUM_ROWS
  
GB
  
  
    
ENTITY_PR
    
SYS_P5055
    
SSB
    
ENABLED
    
43448193
    
1,3
  


When the policy now has been enforced, what is the status of the policy?
SELECT policy_name,
       object_owner,
       object_name,
       subobject_name,
       object_type,
       inherited_from,
       enabled,
       deleted
FROM   dba_ilmobjects
WHERE  object_owner='SSB'
AND    object_name='ENTITY_PR'
AND    subobject_name='SYS_P5055'
ORDER BY 1;



  

  
POLICY_NAME
  
OBJECT_OWNER
  
OBJECT_NAME
  
SUBOBJECT_NAME
  
OBJECT_TYPE
  
INHERITED_FROM
  
ENABLED
  
DELETED
  
  
    
P22
    
SAKSINFO_SKFIN
    
ENTITET_EGENSKAP
    
SYS_P5055
    
TABLE PARTITION
    
POLICY NOT INHERITED
    
NO
    
NO
  

Note how the rule is no longer enabled, now that it has been executed.


Further reading:

The official Oracle 19c ADO documentation
As always, the excellently written tutorials from Tim Hall
I also used examples from the book "Oracle Database 12c New Features", Chapter 8, by Robert G. Freeman published by Oracle Press.

How to work around ORA-38338: incorrect ILM policy scope for row-level ADO policies

When adding an ADO policy on a table, like this:

CREATE TABLE TEST_TABLE1
(
  col1 NUMBER(38),
  col2 NUMBER(38),
  col3 DATE
)
NOCOMPRESS 
TABLESPACE USERS
ILM ADD POLICY ROW STORE COMPRESS ADVANCED 
ROW AFTER 1 DAY OF NO ACCESS;

oracle returned

ORA-38338: incorrect ILM policy scope

This policy is not valid for row-level ADO policy. The only valid option is

ILM ADD POLICY ROW STORE COMPRESS ADVANCED 
ROW AFTER X DAY OF NO MODIFICATION;