Re: Can Oracle move tables cross schema without physical copying!

Dong Tang <dtang_at_minn.net> wrote in article <LIBi1.172$P8.711179_at_ptah.visi.com>...
> Hi, gurus:
>
> I have a question for all the gurus on this group.
> Can I move tables cross schemas with doing
> create table target_table_name
> select * from source_table_name.
>
> Since I can't use RENAME, because I want to move it
> cross different schemas.
>
> If anyone knows, could you e-mail me at
> tangd_at_ctcdist.com
>
>
> Thanx
>
> Dong Tang
>
>
>
>
>

Uhh yes - of course you can - this is how the rest of the known universe does it. Connect as your target user and do:

create table XXX
tablespace WHATEVER
storage ( initial MY_INITIAL next MY_NEXT etc.......) as select * from <your original owner>.<table_name>;

Beware though that this does not copy accross any indexes, primary keys or foreign key constraints - you'll have to rebuild these yourself on the new copy.

You could also use the "copy table" command by setting up a database link back to the same database but this is more trouble than its worth.

BC Received on Fri Jun 19 1998 - 19:57:25 CDT