Oracle AQ: History Table
Is there a simple way in oracle to create a History table of a queue table? The payload type of the queue table is specific type, so if i want to create a normal history table would this specific payload type be a problem ??
Since you are still on 10g, dbms_wm is one option you might want to take a look at. You can version enable a table and all further DML against the table will be converted so that history is maintained.
You can take a look at the implementation here. http://www.oracle-base.com/articles/9i/WorkspaceManagement9i.php#VersionEnablingATable
Please note that this will impact performance since you have triggers firing on different DML to implement versioning.
About the payload type comment, I don't know of any specific constraints with types. You can probably try a small table with your user-defined type and see if you have any issues.