RE: Move selected tables

2003-08-04 Thread Jamadagni, Rajendra
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

Re: Move selected tables

2003-08-04 Thread Tanel Poder
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 a

Re: Move selected tables

2003-08-04 Thread Tanel Poder
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 eas

RE: Move selected tables

2003-08-04 Thread Robson, Peter
>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 alrea

RE: Move selected tables

2003-08-04 Thread Smith, Ron L.
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, Au

RE: Move selected tables

2003-08-04 Thread Sarnowski, Chris
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

Re: Move selected tables

2003-08-04 Thread Reginald . W . Bailey
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 GD

RE: Move selected tables

2003-08-04 Thread Mercadante, Thomas F
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 T

RE: Move selected tables

2003-08-04 Thread Mercadante, Thomas F
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=

RE: Move selected tables

2003-08-04 Thread Jamadagni, Rajendra
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

RE: Move selected tables

2003-08-04 Thread Hately, Mike (LogicaCMG)
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 ne

RE: Move selected tables

2003-08-04 Thread Nelson, Allan
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 b

RE: Move selected tables

2003-08-04 Thread Hately, Mike (LogicaCMG)
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 hea

RE: Move selected tables

2003-08-04 Thread Ramon E. Estevez
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