Re: Copy table data from one schema to another scheman

From: Doggy Daddy <spamhole_at_nospam.org>
Date: Sat, 26 Apr 2003 17:31:11 -0700
Message-ID: <pan.2003.04.26.17.31.10.563058.8478@nospam.org>

On Mon, 21 Apr 2003 10:55:15 -0700, L1 wrote:

> Doggy Daddy <spamhole_at_nospam.org> wrote in message
> news:<pan.2003.04.17.20.22.49.958965.18158_at_nospam.org>...

>> On Thu, 17 Apr 2003 15:17:21 -0700, L1 wrote:
>>
>> > Another question I have is if this could be done for VIEW as well. I
>> > want to do the exact same thing for VIEW, copy views from schema A to
>> > schema B and then add that extra Archived_year column to the
>> > newly-created views in schema B.
>>
>> You can do it, but you probably don't want to use the same technique
>> that you used to copy the base tables. If you just SELECT from the
>> existing views, you're actually copying the rows, not copying the view.
>>
>> To copy the view, you'd want to use CREATE VIEW statements in the
>> archive schema that reference your archive tables. As with the table
>> copy operation, you could either hand-write a script to create
>> appropriate archive views, or you could inspect the data dictionary to
>> get the view definitions and then either generate a SQL script with the
>> appropriate CREATE VIEW statements or use dynamic SQL as before.

> 
> Since the Alter View xxx Add xxx. will only allow adding constraints,
> but not columns, creating the view manually is the only way i can think
> of to accomplish the task described above which will be very tedious.
> 
> Any other suggestions?
> 
> l1

Look at USER_VIEWS first to see what you have to work with. There's a column there that contains the SQL definition of each view. You can take this text, edit it (either by hand, in PL/SQL, or by some other means) so it also includes whatever additional columns you want (and change the base table names, if necessary), and then execute the modified CREATE VIEW in your archive schema. How complicated this editing will be depends on how complicated your views are. That in turn will determine whether it makes more sense for you to do this by hand or by some kind of text-modifying automation.

And don't forget, if you're actually planning to USE your archive tables (and not just retain them for historical purposes), you may also need to duplicate indexes and constraints from the main schema. Received on Sat Apr 26 2003 - 19:31:11 CDT