Creating remote table thru a DB Link

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 28 Apr 2006 17:48 GMT

I am trying to do a 'create table as select' to create a remote table,
but I am getting the error "ORA-02021 DDL operations are not allowed on
a remote database". Is this operation at all possible? The error
seems to say no but I've read and been told that you just need the
privs to do this on the remote database but I don't know the privs.

For example, this is what I need to do:
- have a dblink from instance 1 (local) to instance 2 (remote)
- create table XXX on the local database
- create table on the remote database using the statement:
create table XXX@remote_db as select * from XXX;

The database link is logging on to the remote db as a user that has the
privs to create tables. Is there something I need to do to make this
work or is the operation just not possible?

Thanks,
Dan

Reply to this Message

Michel Cadot - 28 Apr 2006 18:09 GMT

|I am trying to do a 'create table as select' to create a remote table,
| but I am getting the error "ORA-02021 DDL operations are not allowed on
[quoted text clipped - 14 lines]
| Thanks,
| Dan

As the error message states, you can't do it.

Regards
Michel Cadot

Reply to this Message

Brian Peasland - 28 Apr 2006 19:51 GMT

Not possible through the database link. But since you have the account
to in the remote database, why not just sign on to the remote database
with SQL*Plus? Then create your table.

HTH,
Brian

Signature

===================================================================

Brian Peasland
oracle_dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Reply to this Message

Vladimir M. Zakharychev - 29 Apr 2006 13:43 GMT

Dan,

you can

create table xxx as select from xxx@remote;

but you can't

create table xxx@remote as select from xxx;

The difference is obvious. You have a few ways around this, but they
require that you create a dblink on remote server back to the source
server. You can then log into the remote db and ctas * from xxx@source
or you can create a stored procedure like this:

procedure copy_table
is
begin
execute immediate 'create table xxx as select * from xxx@source';
end copy_table;

and then run it from source db:

SQL> exec copy_table@remote

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Reply to this Message