| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Move selected tables
Hi!
I think you should use export/import with rows=n parameter, because
otherwise you have to write manual scripts for transferring some
constraints, indexes, triggers, etc...
You should generate a parameter file for exp where you store:
tables=(
'TAB1', 'TAB2', 'TAB3',
If you got a lot of data to move, then you could first transfer only table structures without any indexes & constraints, then use insert /*+ APPEND PARALLEL NOLOGGING */ into new_schema.tab select * from old_schema.tab; (Parallel *hint* is new in 9i and should be used only if your IO subsystem is fast enough to handle the load.
Also, if you happend have *really lot* of data and very little allowed downtime you could create a single partition range partitioned table in new schema (with values less than (maxvalue) and then just exchange the partitions between two tables. This will save you a lot of IO bandwidth and downtime.
Of course it seems that you don't have a 24x7 production database anyway, since in those you usually don't move tables around in different schemas, so the easiest might be just to export everything (with parameterfile specifying table names), drop the exported tables and reimport them under new schema. If you drop old tables *after* import to new ones, you just might waste space in tablespace - but if you are dropping data before importing it back, you should make sure that the exportfile is ok (importing with show=y for testing or similar).
Tanel.
> 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).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: tanel.poder.003_at_mail.ee 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).Received on Mon Aug 04 2003 - 12:39:22 CDT
 |
 |