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