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,
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
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
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
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
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
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
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
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
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,
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
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
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
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
14 matches
Mail list logo
