RE: Move selected tables

Thanks for all the info and various ways to handle this. I was kinda hoping there was a magic tables=( abc_% ) parameter for export that I didn't know about. I think I'll use the SQL generating SQL idea. Sounds like the quickest way.

Thanks again!
Ron

-----Original Message-----
Sent: Monday, August 04, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L

You've gotten several suggestions for how to move the data - let me mention some of the caveats. We went from one big shared schema to several smaller schemas, which is what led to issue 3 below. If that's not what you're doing, you may not have this problem.

We've done some of this, and here are some of the issues we've run into.

1. make sure your referential integrity is maintained. If you are moving a parent or child table, you'll have to drop and recreate any foreign keys. export/import makes this somewhat easier than 'create as select *', but not trivial. It's easier if you move both parents and children in the same export set (assuming they're all being moved).
2. make sure the data doesn't change while you're moving the tables. What we've done in some cases is open an SQLPlus window, lock the tables, then do the export/import in a different shell.
3. make sure any code finds the correct tables. We've created private synonyms for the tables in the new locations as an interim solution. There is a little overhead associated with resolving synonyms while parsing queries but it doesn't seem to be a problem for us. The down side is that there is not much incentive for developers to point to the correct tables, and there's no good way to keep someone from referring to the old schema when they should be using the new schema. Some on this list will say this is a matter of DBA discipline: at any rate it is a matter of management discipline, and the DBA's ability to do anything about it depends on how much control they have over introduction of new code.

Good luck,
-Chris

> -----Original Message-----
> From: Smith, Ron L. [mailto:rlsmith_at_kmg.com]
> Sent: Monday, August 04, 2003 12:14 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Move selected tables
>
>
> I need to move selected tables from one schema to another
> schema within
> the same database. The tables I need to move all start with the same
> prefix (abc_sometablename). Say there are 200 tables out of
> 1000 that I
> want to move. Is there an easy way to do this?
>
> Thanks!
> Ron
> --

LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  INET: csarnows_at_CuraGen.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Smith, Ron L.
  INET: rlsmith_at_kmg.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).