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