I have an ILM policy established on a table. Is the Export DLL ILM dropdown “Only when Enabled” supposed to be doing something?
Yes, it should be including the policy in the table DDL. One possible reason that it’s not showing up is that the table is not in your schema and you do not have the SELECT priv on either DBA_ILMOBJECTS or DBA_ILMDATAMOVEMENTPOLICIES. We use the USER_ version of those views if the table is in your schema, and there is no ALL_ version of them, unfortunately.
If you do have privs on those views, make sure that options -> “Check for access to DBA_ views” is checked.
The DBA_views is checked. I’m on 12.10 with 12.1 client. I added an ILM policy on a single partition with the syntax below. I don’t see Toad generating the syntax as SYS or as the schema owner. I don’t see the %ILM% views hit when spooling the Toad generated SQL. DBMS_METADATA.get_ddl picks up the ALTER TABLE at the end of the table DDL. I’ve tried table level ADO policies as well. as well with no luck.
ALTER TABLE static_studies_wrkld_base_f
MODIFY PARTITION static_studies_wrkld_basejrmc
ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW
AFTER 1 DAYS OF NO MODIFICATION;
Also loosely related for In-Database Archiving, I do see the “ROW ARCHIVAL” generated in the table DDL syntax.
send me a ddl from dbms_metadata for the whole table and let me know the full Oracle server version. I’ll try to reproduce it.
send to john.dorlon@quest.com if you don’t want to post it to the forum.