Home » SQL & PL/SQL » SQL & PL/SQL » DDL Operation On Remote database
DDL Operation On Remote database [message #255064] Mon, 30 July 2007 09:50 Go to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
Hi,
I have one doubt .Can we perform DDL operation in same ip -machine but in another schema.If yes, how can we perform.can anyone give me any idea.

thanks in advance....
Re: DDL Operation On Remote database [message #255066 is a reply to message #255064] Mon, 30 July 2007 09:53 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Another schema or a different database ?

Your title says remote database, your post says different schema.

What happens when you try it ?
Re: DDL Operation On Remote database [message #255068 is a reply to message #255066] Mon, 30 July 2007 09:59 Go to previous messageGo to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
Marcl
it is different database.
But i was trying through procedure during procedure i received like thar error
" ERROR CODE: -2021 ERROR MESSAGE: ORA-02021: DDL operations are not allowed on remote database
that's why i given this title.

Please tell me any solution in this case..
Re: DDL Operation On Remote database [message #255073 is a reply to message #255068] Mon, 30 July 2007 10:05 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
<picks jaw up off ground>
Did you look up the error? Google the error read the associated cause and action text. Come back if it is not clear.
Re: DDL Operation On Remote database [message #255076 is a reply to message #255073] Mon, 30 July 2007 10:12 Go to previous messageGo to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
hi pablolee,
It is not clear to me.can u tell me something more..
Re: DDL Operation On Remote database [message #255077 is a reply to message #255064] Mon, 30 July 2007 10:14 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Doesn't the error message say it all?

What else are you expecting?
Re: DDL Operation On Remote database [message #255079 is a reply to message #255077] Mon, 30 July 2007 10:23 Go to previous messageGo to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
Hi
Actuallly i want to know is it possible to perfrom DDL operation in different database or not?
Re: DDL Operation On Remote database [message #255080 is a reply to message #255079] Mon, 30 July 2007 10:25 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
you cant ,ONLY DML.
Re: DDL Operation On Remote database [message #255081 is a reply to message #255064] Mon, 30 July 2007 10:28 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
" ERROR CODE: -2021 ERROR MESSAGE: ORA-02021: DDL operations are not allowed on remote database

That wasn't clear enough?
Re: DDL Operation On Remote database [message #255083 is a reply to message #255079] Mon, 30 July 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually you can if you have a database link and can execute remote dbms_utility.exec_ddl_statement.

Regards
Michel
Re: DDL Operation On Remote database [message #255364 is a reply to message #255083] Tue, 31 July 2007 09:27 Go to previous messageGo to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
Hi Michel,
I want little bit help more, please.
Can i use this dbms package like this to perform ddl operation in another database

v_QUERY := 'CREATE TABLE ' || abc ||'@db2 AS SELECT * FROM XYZ WHERE 1=0';
dbms_utility.exec_ddl_statement(v_query);




@db2 is database link
Re: DDL Operation On Remote database [message #255376 is a reply to message #255364] Tue, 31 July 2007 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you can't do that.
You didn't say this a mixed DDL local AND remote.

Use SQL*Plus COPY command.
Or connect to the remote database and do the opposite.

Regards
Michel
Re: DDL Operation On Remote database [message #255657 is a reply to message #255376] Wed, 01 August 2007 07:26 Go to previous messageGo to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
Hi Michel,
Can i use this copy command in procedure.

begin
for i in c1 loop
v_tab_name:='abc'||i
v_QUERY :='COPY FROM d2/d2@d2 to d1/d1@d1 CREATE'||' '|| V_TAB_NAME||' '||' USING SELECT * FROM '||' '||V_TAB_NAME;
EXECUTE IMMEDIATE v_QUERY
end;

thanks in advance....
Re: DDL Operation On Remote database [message #255662 is a reply to message #255657] Wed, 01 August 2007 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No COPY is a SQL*Plus command not a PL/SQL statement.

Regards
Michel
Re: DDL Operation On Remote database [message #255665 is a reply to message #255662] Wed, 01 August 2007 07:47 Go to previous messageGo to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
Hi Michel,
Till now m on same problem,actully i hv some table like a1,a2,a3,a4,a5,a6 in my db so same table i wnat to create in another db also with same name
a1,a2,a3,a4a,a5,a6.After creating each table after that i want ot insert the data in their corresponding table.
how can i do this?Is it possible?Please help me.
that's why i was trying like this,but now u told copy command also we cant use.then how should i do?

begin
for i in c1 loop
v_tab_name:='abc'||i
v_QUERY :='COPY FROM d2/d2@d2 to d1/d1@d1 CREATE'||' '|| V_TAB_NAME||' '||' USING SELECT * FROM '||' '||V_TAB_NAME;
EXECUTE IMMEDIATE v_QUERY
end;
Re: DDL Operation On Remote database [message #255667 is a reply to message #255665] Wed, 01 August 2007 07:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I would use export and import. But that's just me of course.

MHE
Re: DDL Operation On Remote database [message #255676 is a reply to message #255667] Wed, 01 August 2007 08:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I reckon your best bet is to use DBMS_METADATA to get the DDL for the tables, modify it to work over the db link, then use DBMS_UTILITY.EXECUTE_DDL to execute the DDL remotely.

Then just use Insert to populate the new tables with data.
Re: DDL Operation On Remote database [message #255687 is a reply to message #255676] Wed, 01 August 2007 08:51 Go to previous messageGo to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
Hi JROWBOTTOM,

i hope it will correct solution but can u tell me please how to use this dbms_metadata package?
can u giv me just few line example please .so that i can understand how to use these both package (dbms_metadata,dbms_utility.execute_ddl)
please tell me once.

Thanks in advance
Re: DDL Operation On Remote database [message #255693 is a reply to message #255687] Wed, 01 August 2007 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now that we have give many ideas, you may work on them and come back if you are stuck instead of asking for being spoon fed.

Regards
Michel
Re: DDL Operation On Remote database [message #255700 is a reply to message #255687] Wed, 01 August 2007 09:11 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is it with people and not wanting to read the documentation

You've been shown Dbms_utility.exec_ddl_statement further up.

Here's a 3 line dbms_metadata example.
create table metadata_test (col_1  number, col_2 date);

select dbms_metadata.get_ddl('TABLE','METADATA_TEST') from dual;
  
"
  CREATE TABLE "DEV"."METADATA_TEST" 
   (	"COL_1" NUMBER, 
	"COL_2" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TS_USERS" 
 "


Previous Topic: trigger vs procedure
Next Topic: Query Optimization question
Goto Forum:
  


Current Time: Sat Dec 10 22:31:02 CST 2016

Total time taken to generate the page: 0.08108 seconds