Hi,
I am getting following error when I try to create a table in remote database.
ORA-02021: DDL operations are not allowed on a remote database
What privileges do we have to provide to the local database so that it will create object in remote database schema?
This is not a matter of missing privileges, but a matter of implementation ,
probably first of all due to security reasons.
What you can do is to create a stored procedure at the remote database that
creates the table ( of course, using dynamic SQL for doing this in a PL/SQL procedure ),
and then grant EXECUTE on that procedure to the user (schema) that your db-link is using
for connecting to the remote database.
Please however be aware that you should be very careful with such an approach,
because the procedure will execute under the security privileges of its owner,
and using dynamic SQL for performing DDL statements is always prone to being exploited
for SQL injection, a.s.o.
So, such an approach should always be used with maximum caution,
usually only from application code that is fully under your control.
But, since a CREATE TABLE for a specific table name, under a specific OWNER
is only possible once, I don't exactly see why do you need to to this from another database,
why not just once, from the same (remote) database ?
If you want to create each time a different table (with a different name),
then you may be better off passing the table name + column names as parameters
to the procedure, and create the dynamic SQL statement inside the procedure,
so that you can exercise full control upon it.