How to create a table from remote database using dblink

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>