RE: Move selected tables
What about this SELECT 'CREATE TABLE NEWUSER.'||TABLE_NAME ||' TABLESPACE NEWTBS AS SELECT * FROM '||TABLE_NAME ||';' FROM USER_TABLES WHERE TABLE_NAME LIKE 'ABC_%' / Ramon E. Estevez [EMAIL PROTECTED] 809-535-8994 -Original Message- Smith, Ron L. Sent: Monday, August 04, 2003 12:14 PM To: Multiple recipients of list ORACLE-L 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ramon E. Estevez INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Move selected tables
something like ... set feed off set echo off set pages 0 select 'alter table '||table_name||' move tablespace NEW_TS;' from user_tables where table_name like 'ABC_%' spool move_tables.sql / spool off would generate you a script to move your tables. Cheers, Mike PS That's off the top of my head so exercise care when running it. -Original Message- Sent: 04 August 2003 16:14 To: Multiple recipients of list ORACLE-L 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Move selected tables
select 'create table newuser'||'.'||table_name||' as select * from '||owner||'.'||table_name||';' from dba_tables where table_name like 'abc%'; Will give you some dynamic sql to do the creates. A similar statement would do the drops for the original tables afterwards assuming you have space for both. Allan -Original Message- Sent: Monday, August 04, 2003 11:14 AM To: Multiple recipients of list ORACLE-L 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Move selected tables
Bugger. I missed the schema bit didn't I. Never mind, back to the drawing board ... Mike -Original Message- Sent: 04 August 2003 16:14 To: Multiple recipients of list ORACLE-L 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 E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Move selected tables
Title: RE: Move selected tables I thought the tables were changing their ownership (schema) not the address (tablespace) Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED]] Sent: Monday, August 04, 2003 12:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: Move selected tables something like ... set feed off set echo off set pages 0 select 'alter table '||table_name||' move tablespace NEW_TS;' from user_tables where table_name like 'ABC_%' spool move_tables.sql / spool off would generate you a script to move your tables. Cheers, Mike PS That's off the top of my head so exercise care when running it. This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Move selected tables
Ron, You can certainly create an Export Parameter file listing all of the tables you want to export - Select the table names and spool the result - then edit the result, creating an export parameter file - it would look something like: userid= schema/pw file=exp_file_name.dmp buffer=65000 direct=y log=exp.log tables=( table1, table2... ) exp parfile=(file name of par file) imp userid=dbauser/pw file=exp_file_name.dmp fromuser=sourceschemauser touser=targetschemauser full=y log=imp.log good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, August 04, 2003 12:14 PM To: Multiple recipients of list ORACLE-L 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Mercadante, Thomas F INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Move selected tables
the problem with the 'create table newuser' concept is that none of the constraints (except for NOT NULL) get created. So, all primary key, foreign key and indexes do not come along. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, August 04, 2003 12:35 PM To: Multiple recipients of list ORACLE-L select 'create table newuser'||'.'||table_name||' as select * from '||owner||'.'||table_name||';' from dba_tables where table_name like 'abc%'; Will give you some dynamic sql to do the creates. A similar statement would do the drops for the original tables afterwards assuming you have space for both. Allan -Original Message- Sent: Monday, August 04, 2003 11:14 AM To: Multiple recipients of list ORACLE-L 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Mercadante, Thomas F INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Move selected tables
Try using the SQLPlus command COPY or the SQL command create table xxyy AS SELECT * FROM dddrrr. Or use OEM or some other GUI tool. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] mTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Move selected tables ity.com 08/04/2003 11:14 AM Please respond to ORACLE-L 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Move selected tables
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:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Move selected tables
the problem with the 'create table newuser' concept is that none of the constraints (except for NOT NULL) get created. So, all primary key, foreign key and indexes do not come along. Tom Mercadante Oracle Certified Professional Right - but it is quite easy to use the dynamic SQL already illustrated in an earlier email reply (eg below) to capture the entirety of the DDL for a suite of tables, and transpose the whole lot into a new schema. In fact, I am doing this sort of thing repeatedly, as our users areas are regarded as 'development', with their objects (tables, triggers, constraints, the lot) being moved into our production schema more or less on demand. peter edinburgh . -Original Message- Sent: Monday, August 04, 2003 12:35 PM To: Multiple recipients of list ORACLE-L select 'create table newuser'||'.'||table_name||' as select * from '||owner||'.'||table_name||';' from dba_tables where table_name like 'abc%'; Will give you some dynamic sql to do the creates. A similar statement would do the drops for the original tables afterwards assuming you have space for both. Allan -Original Message- Sent: Monday, August 04, 2003 11:14 AM To: Multiple recipients of list ORACLE-L 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 -- * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. .http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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', ... 'TABX) It's quite easy to generate this kind of parameter file dynamically to have all necessary tables. You can use parameter parfile with export, to specify where from it should read it's parameters. 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. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 04, 2003 8:19 PM 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:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Move selected tables
Hi! Right - but it is quite easy to use the dynamic SQL already illustrated in an earlier email reply (eg below) to capture the entirety of the DDL for a suite of tables, and transpose the whole lot into a new schema. In fact, I am doing this sort of thing repeatedly, as our users areas are regarded as 'development', with their objects (tables, triggers, constraints, the lot) being moved into our production schema more or less on demand. In 9i, you could make life easier with dbms_metadata functions get_ddl and get_dependent_ddl... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Move selected tables
Title: RE: Move selected tables exp tables ... imp fromuser=old_user touser=new_user Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Smith, Ron L. [mailto:[EMAIL PROTECTED]] 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
