Skip to Main Content

Breadcrumb

Easter

Question and Answer

Tom Kyte

Thanks for the question, Jeff.

Asked: June 04, 2004 - 4:58 pm UTC

Answered by: Tom Kyte - Last updated: March 26, 2020 - 4:37 pm UTC

Category: Developer - Version: 9.2.0

Viewed 1000+ times

You Asked

Hi Tom,

We are able to execute remote procedures, but we are not able to create them remotely.

Example:

12:59:54 vdr@rdmdev02 > create or replace procedure c2a.r@rmsdev06
2 as
3 begin
4 dbms_output.put_line( 'hello' );
5 dbms_output.put_line( 'world' );
6* end;
12:59:54 vdr@rdmdev02 > /
create or replace procedure c2a.r@rmsdev06
*
ERROR at line 1:
ORA-01031: insufficient privileges


What am I missing?!

Thanks in advance,

Jeff

and we said...

you cannot do DDL over a dblink

you have to connect to the remote database to perform DDL.

and you rated our response

  (7 ratings)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

dll over a database link

June 05, 2004 - 4:35 am UTC

Reviewer: Tak Tang from United Kingdom

Jeff,

Well, you could do this :-

begin
dbms_utility.exec_ddl_statement@rmsdev06
(
'create or replace procedure r is '
|| 'begin '
|| ' dbms_output.put_line(''hello''); '
|| ' dbms_output.put_line(''world''); '
|| 'end; '
);
end;

But get someone to take your temperature first! :-)

Takmeister


To execute dbms_utility.exec_ddl_statement dynamically

September 27, 2012 - 12:46 am UTC

Reviewer: Sangeetha Ganeshrao from India

Hi Tom,

The requirement is as follows:

Through an Oracle Apex application I need to drop a user/schema in another Oracle database. i.e., drop user remotely using an Oracle Apex application.

Currently, I am using the below procedure for this task. But, as there are many remote databases, so, I would like to know is there a way to make this dynamic, i.e., to pass the DB link name to DBMS_UTILITY.EXEC_DDL_STATEMENT dynamically. So, that the user will pass the user name and the database name and based on the database name the procedure will connect to the correct database/DB link and drop the given user. Please let me know if there is any other way to accomplish this task. Thank you so much in advance.

CREATE OR REPLACE
PROCEDURE drop_user_proc(
p_username VARCHAR2,
p_databasename VARCHAR2)
IS
ddl_stmt VARCHAR2(100);
BEGIN
IF p_databasename = ‘REMOTEDB1’ THEN
ddl_stmt := 'DROP USER '||p_username||' CASCADE';
dbms_utility.exec_ddl_statement@remotedb1(ddl_stmt);
END IF;
END drop_user_proc;
Tom Kyte

Followup  

September 27, 2012 - 9:00 am UTC

this is so unsafe.

https://www.google.com/search?q=site%3Atkyte.blogspot.com+sql+injection

you need to sanitize your inputs!!! seriously.


you can use execute immediate 'begin dbms_utility.exe...@' || dblink || '(:x); end;' using ddl_stmt;


but you'll need to

a) verify p_username is safe
b) dblink is safe

read up on dbms_assert.

Thank you

September 28, 2012 - 12:26 am UTC

Reviewer: Sangeetha Ganeshrao from India

This is really great.... Thank you so much for you quick response and your time.

Remote DDL over DB link in CDB

March 17, 2020 - 4:22 pm UTC

Reviewer: Aamir from Irving, TX USA

I am trying to run following DDL in a remote CDB (CDB to CDB) but getting ORA 12828. It works in PDB.

CDB$ROOT>exec DBMS_UTILITY.EXEC_DDL_STATEMENT@cdb2 ('ALTER USER C##USER1 quota 1G on USERS');
BEGIN DBMS_UTILITY.EXEC_DDL_STATEMENT@cdb2 ('ALTER USER C##USER1 quota 1G on USERS'); END;
*
ERROR at line 1:
ORA-12828: Can't start parallel transaction at a remote site
ORA-06512: at "SYS.DBMS_UTILITY", line 593
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at "SYS.DBMS_UTILITY", line 586
ORA-06512: at line 1


Any idea on how to resolve this?
Chris Saxon

Followup  

March 17, 2020 - 5:39 pm UTC

Look at the error:

ORA-12828: Can't start parallel transaction at a remote site

Have you got parallel DDL/DML enable on the remote CDB?

Remote DDL over DB link in CDB

March 17, 2020 - 6:19 pm UTC

Reviewer: Aamir from Irving, TX USA

It seems parallel DDL is on by default in root CDB.

SYS@CDB$ROOT>select PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS, count(*) from v$session group by PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS;
   PDML_ENABLED    PDML_STATUS    PDDL_STATUS    PQ_STATUS    COUNT(*)
_______________ ______________ ______________ ____________ ___________
NO              DISABLED       ENABLED        ENABLED              116
NO              DISABLED       DISABLED       DISABLED              37


2 rows selected.


How can I disable it?
Chris Saxon

Followup  

March 20, 2020 - 2:46 pm UTC

It's probably best to just disable it in your remote session before creating your user:

exec DBMS_UTILITY.EXEC_DDL_STATEMENT@cdb2 ('alter session disable parallel ddl');

Remote DDL over DB link in CDB

March 23, 2020 - 3:47 pm UTC

Reviewer: Aamir from Irving, TX USA

Thanks Chris. I tried your suggestion but still getting the same error.

begin
  2     DBMS_UTILITY.EXEC_DDL_STATEMENT@cdb2 ('alter session disable parallel ddl');
  3     DBMS_UTILITY.EXEC_DDL_STATEMENT@cdb2 ('ALTER USER C##USER1 quota 1G on USERS');
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-12828: Can't start parallel transaction at a remote site
ORA-06512: at "SYS.DBMS_UTILITY", line 593
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at "SYS.DBMS_UTILITY", line 586
ORA-06512: at line 3

Chris Saxon

Followup  

March 23, 2020 - 4:08 pm UTC

What happens if you try and create the user on the remote site itself - i.e. not via a db link, just a normal connection?

Remote DDL over DB link in CDB

March 23, 2020 - 5:40 pm UTC

Reviewer: Aamir from Irving, TX USA

It works when directly connected to the database

C##SEC_ADMIN@CDB$ROOT>conn c##sec_admin@cdb2
Password? (**********?) **********
Connected.
C##SEC_ADMIN@cdb2>CREATE USER C##USER1
  2    IDENTIFIED BY "Monday23!"
  3    DEFAULT TABLESPACE USERS
  4    TEMPORARY TABLESPACE TEMP
  5    PROFILE C##DB_USER
  6    ACCOUNT UNLOCK;

User created.



Chris Saxon

Followup  

March 26, 2020 - 4:37 pm UTC

Hmmm, I'm not sure what the issue is here...

Do you have to create the user over the db link? If so, I think this is one you'll have to take up with support.