Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jeff.

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

Last updated: March 26, 2020 - 4:37 pm UTC

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 Tom said...

you cannot do DDL over a dblink

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

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

dll over a database link

Tak Tang, June 05, 2004 - 4:35 am UTC

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

Sangeetha Ganeshrao, September 27, 2012 - 12:46 am UTC

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
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

Sangeetha Ganeshrao, September 28, 2012 - 12:26 am UTC

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

Remote DDL over DB link in CDB

Aamir, March 17, 2020 - 4:22 pm UTC

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
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

Aamir, March 17, 2020 - 6:19 pm UTC

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
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

Aamir, March 23, 2020 - 3:47 pm UTC

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
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

Aamir, March 23, 2020 - 5:40 pm UTC

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
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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library