Re: Oracle Archive Solution
From: <gints.plivna_at_gmail.com>
Date: Mon, 23 Apr 2012 20:45:03 +0300
Message-Id: <0F48221E-7465-461C-A2F5-BE6F9DA4EDE2_at_gmail.com>
We used homegrown scripts. Partition, compression etc. are very cool features except two things, $$ and only in EE. So we had SE on both ends, and the business need was to move a catalogue after manual initiation (a row from a table and related info from ~30 tables) from production to archive and then delete the stuff. Similar scenario was for audit tables, but the restriction was date. So I used data pump to export data, one package for export, associative array for each table's where clause and dynamic sql to use for dp export. .NET was used to move dp export file to necessary destination via ftp, then another package for dp import. Everything went quite smoothly except the fact that our developers were too smart and used nested tables, even worse, they used object types and nested tables in more than one level. As a result it was major pain to force datapump export to export and then import correct data. Schema names in both environments were different, according to docs d p import doesn't remap schema for enclosed nested table types. The solution was to make temp tables for enclosed nested tables. So since then I have another very strong argument NOT to use nested tables in DB.
Date: Mon, 23 Apr 2012 20:45:03 +0300
Message-Id: <0F48221E-7465-461C-A2F5-BE6F9DA4EDE2_at_gmail.com>
We used homegrown scripts. Partition, compression etc. are very cool features except two things, $$ and only in EE. So we had SE on both ends, and the business need was to move a catalogue after manual initiation (a row from a table and related info from ~30 tables) from production to archive and then delete the stuff. Similar scenario was for audit tables, but the restriction was date. So I used data pump to export data, one package for export, associative array for each table's where clause and dynamic sql to use for dp export. .NET was used to move dp export file to necessary destination via ftp, then another package for dp import. Everything went quite smoothly except the fact that our developers were too smart and used nested tables, even worse, they used object types and nested tables in more than one level. As a result it was major pain to force datapump export to export and then import correct data. Schema names in both environments were different, according to docs d p import doesn't remap schema for enclosed nested table types. The solution was to make temp tables for enclosed nested tables. So since then I have another very strong argument NOT to use nested tables in DB.
Gints Plivna
On 2012. gada 20. apr., at 19:30, Li Li <litanli_at_gmail.com> wrote:
> Hi List,
>
> I am wondering if anyone has implemented an archive solution and would
> be willing to share what product you used, either commercial, open
> source or home-grown? We are at a point that we have to archive data
> based on dates as our database has grown tremendously since the
> company started and data has never been archived.
>
> TIA,
> -Li
> --
> http://www.freelists.org/webpage/oracle-l
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 23 2012 - 12:45:03 CDT