Oracle-L: RE: Move selected tables

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 04 Aug 2003 08:49:28 -0800
Message-ID: <F001.005C8D43.20030804084928@fatcity.com>

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: 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).

__
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: anelson_at_midf.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------