Re: data migration question. source & target tables are the same-
From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 08 Nov 2006 09:05:14 -0800
Message-ID: <1163005513.962217_at_bubbleator.drizzle.com>
>> Bill wrote:
>>
>>> I'm doing a one time task of migrating some data from one oracle
>>> database into another, bigger, more centralized database in order
>>> to consolidate things and make access more convenient. Source and
>>> target table definitions are mirror images. However, I'm not sure of
>>> the best way to proceed with the 2 step process:
>>>
>>> 1: The source table, which has been in use for a 6 or 8 months, now
>>> has about 50,000 records. Before the migration, 2 fields in each
>>> source table record will need to be populated using data items
>>> appearing in other tables of the target database. (we will be ousting
>>> the source table key of SSN, replacing it with a more benign key
>>> item). The second field is another, internal key, to be used as a
>>> future cross reference.
>>> Both tables also store the SSN in an alternate field, in case it will be
>>> needed for future internal verification purposes (so right now at
>>> this point, the source table actually has 2 fields populated with SSN
>>> data. One of these will be replaced with another key during the
>>> migration). SSN data can also be pulled from at least one other table
>>> in the target database, so it can somehow be used for cross
>>> referencing purposes.
>>>
>>> 2: Once these two fields have been replaced, a 'migration wizard'
>>> will be used to move the data.
>>>
>>>
>>>
>>> Step 2 is cake, but I'm not sure how to proceed with step 1. Is there
>>> a shareware or other oracle tool that will let me do this? I believe
>>> this can be done with MS Access but I'm not familiar enough with MS
>>> Access to do it-
>>>
>>>
>>> Hope the weekend was good-
>>> Bill
>>
>>
>> Given only 50K records one could accomplish step 1, using SQL*Plus, in
>> somewhere between 5 and 10 minutes.
>>
>> But I do take exception to your phrase "benign key item." I'll grant
>> that an SSN is not the perfect natural key but given only 50K records
>> it is close enough.
>> introduction of corrupt data or force you to use a unique index to, in
>> effect, recreate the original key. It sounds like a very bad idea indeed.
Date: Wed, 08 Nov 2006 09:05:14 -0800
Message-ID: <1163005513.962217_at_bubbleator.drizzle.com>
Bill wrote:
> > > DA Morgan wrote:
>> Bill wrote:
>>
>>> I'm doing a one time task of migrating some data from one oracle
>>> database into another, bigger, more centralized database in order
>>> to consolidate things and make access more convenient. Source and
>>> target table definitions are mirror images. However, I'm not sure of
>>> the best way to proceed with the 2 step process:
>>>
>>> 1: The source table, which has been in use for a 6 or 8 months, now
>>> has about 50,000 records. Before the migration, 2 fields in each
>>> source table record will need to be populated using data items
>>> appearing in other tables of the target database. (we will be ousting
>>> the source table key of SSN, replacing it with a more benign key
>>> item). The second field is another, internal key, to be used as a
>>> future cross reference.
>>> Both tables also store the SSN in an alternate field, in case it will be
>>> needed for future internal verification purposes (so right now at
>>> this point, the source table actually has 2 fields populated with SSN
>>> data. One of these will be replaced with another key during the
>>> migration). SSN data can also be pulled from at least one other table
>>> in the target database, so it can somehow be used for cross
>>> referencing purposes.
>>>
>>> 2: Once these two fields have been replaced, a 'migration wizard'
>>> will be used to move the data.
>>>
>>>
>>>
>>> Step 2 is cake, but I'm not sure how to proceed with step 1. Is there
>>> a shareware or other oracle tool that will let me do this? I believe
>>> this can be done with MS Access but I'm not familiar enough with MS
>>> Access to do it-
>>>
>>>
>>> Hope the weekend was good-
>>> Bill
>>
>>
>> Given only 50K records one could accomplish step 1, using SQL*Plus, in
>> somewhere between 5 and 10 minutes.
> Actually I took another look at this. Unfortunately, it can't be done > using SQL*Plus.
>>
>> But I do take exception to your phrase "benign key item." I'll grant
>> that an SSN is not the perfect natural key but given only 50K records
>> it is close enough.
> "Benign" in terms of security. > What you seem to be considering will guarantee the
>> introduction of corrupt data or force you to use a unique index to, in
>> effect, recreate the original key. It sounds like a very bad idea indeed.
> > That's because you don't appear to understand it. > > take care, > b
[Quoted] Au contraire.
It is because I have made those mistakes in the past and paid the price.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Wed Nov 08 2006 - 18:05:14 CET