How to create a table from remote database using dblink

Question

How to create a table from remote database using dblink

create table MYTABLE@DBLINK (name varchar2(20)));

*Cause: An attempt was made to use a DDL operation on a remote database. For example, "CREATE TABLE tablename@remotedbname ...".

*Action: To alter the remote database structure, you must connect to the remote database with the appropriate privileges.

Is this a privilege error or it is not possible to do with dblink?

Answer 1

If you managed to create a database link, you obviously have that user's credentials so the simplest option is to actually connect as remote user and create table in that schema directly.

If you want to do it from your schema, you can't do it using that syntax, but - there's a workaround: dbms_utility.exec_ddl_statement

Here's an example.

Database link first:

SQL> create database link dbl_mike connect to mike identified by lion using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = my_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XEPDB1)))';

Database link created.

Does it work? Yes!

SQL> select * from dual@dbl_mike;

D
-
X

This is what you tried, and found out that it won't work:

SQL> create table test_dbl@dbl_mike (id number);
create table test_dbl@dbl_mike (id number)
                     *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

Workaround I mentioned:

SQL> exec dbms_utility.exec_ddl_statement@dbl_mike('create table test_dbl (id number)');

PL/SQL procedure successfully completed.

Testing (if table exists, I can insert a row over database link and select from that table):

SQL> insert into test_dbl@dbl_mike (id) values (100);

1 row created.

SQL> select * from test_dbl@dbl_mike;

        ID
----------
       100

SQL>

---

[EDIT]

If you have several statements to run, you can do it one-by-one using exec. Note that it won't work everywhere; it works in SQL*Plus, SQL Developer and perhaps some other tools. But, as it is short for a BEGIN-END block, you could rather/better

SQL> begin
  2    dbms_utility.exec_ddl_statement@dbl_mike('create table test_dbl (id number)');
  3    dbms_utility.exec_ddl_statement@dbl_mike('create table test_dbl2 (id number)');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL>