My customer has a special requirement.We need keep the historical record in oracle database and the data which is very old should keep in the offline storage.So , I guess we need integrate Workspace Manager and ILM.
Workspace manager record the timestamp in xxx_LT table when the data has update\insert or delete.
In ILM,lifecycle definition established the relationship between table partition and storage.
Do ILM support workspace manager's LT table?
Does integration to meet the needs of users?
Where is the best practice?
At the moment ILM Assistant (ILMA) is not designed to work directly with Oracle Workspace Manager. However, as ILMA only generates the ILM scripts and does not execute them it might be possible to make these two features work together although I am not aware of anyone trying to use these features together.
According to the [Workspace Manager 11g whitepaper|http://www.oracle.com/technology/products/database/workspace_manager/pdf/twp_AppDev_Workspace_Manager_11gR1.pdf] Partition operations ADD, MERGE, and SPLIT can be performed on version enabled tables using the AlterVersionedTable procedure. All partitioning schemes are supported.
But the ILM Assistant is not aware of Workspace enabled tables. I think the complicating factor would be the change of name for the main table from XXXX to XXXX_LT. Obviously you have to version enable the table first and then select to place the LT table under the control of the ILMA. But the documentation states that you should not reference the 'LT' table directly so this might not be a good idea. If you want to try and make this work, you will need to manually update the scripts generated by the ILMA to include calls to the Workspace Manager procedure AlterVersionedTable before executing any of the standard ILMA generated code. Personally, I would try this on a simple test system before committing to deliver this to a client.
Hope this helps