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

"Dan" <daniel.ostertag (AT) visaer (DOT) com> a écrit dans le message de news: 1146242891.400839.84040 (AT) y43g200...oglegroups.com...
As the error message states, you can't do it.

Regards
Michel Cadot

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




--
================================================== =================

Brian Peasland
oracle_dba (AT) nospam (DOT) 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

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