Home » SQL & PL/SQL » SQL & PL/SQL » Exchange partition from table at remote database
Exchange partition from table at remote database [message #616647] Thu, 19 June 2014 05:12 Go to next message
purnima1
Messages: 79
Registered: June 2014
Member
I have one table at one database and other at other.how can i exchange the partition of one table with the data of table in other database.
Tried

Report message to a moderator

Re: Exchange partition from table at remote database [message #616649 is a reply to message #616647] Thu, 19 June 2014 05:19 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Try TKs suggestion
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6050339907954

Report message to a moderator

Re: Exchange partition from table at remote database [message #616879 is a reply to message #616649] Sun, 22 June 2014 02:31 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Hi Pablolee
I don think they are doing what i actually wanted.Sad

Report message to a moderator

Re: Exchange partition from table at remote database [message #616880 is a reply to message #616879] Sun, 22 June 2014 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So specify in details what you want, with an example.

Report message to a moderator

Re: Exchange partition from table at remote database [message #616887 is a reply to message #616879] Sun, 22 June 2014 04:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
purnima1 wrote on Sun, 22 June 2014 13:01

I don think they are doing what i actually wanted.Sad


But I think Tom's first example is what you need. See the 2nd part where he says "get the data from BIG_TABLE to another database". He did not do it directly, since you cannot actually do that over dblink. So Tom does an alternate way of exchanging partition after the partition is ready with data in the same database. But that example was shown way back with Oracle version 8.1.7.

Report message to a moderator

Re: Exchange partition from table at remote database [message #616912 is a reply to message #616887] Sun, 22 June 2014 23:03 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
I have one table T1 in db1 which is partitioned table. And T2 in db2 which is non partitioned table.dblink is created at db1 and i am able to execute below mentioned query
select * from t2@dblink;

Alter table t1 exchange partition part_name
WITH TABLE T2@dblink
WITHOUT VALIDATION;
This is giving an error that exchange partition cannot be done over dblink.
I have googled and found the solution as

exec dbms_utility.exec_ddl_statement@db_link('your statment');

but i am not getting how to fit this query for my scenario.


begin
dbms_utility.exec_ddl_statement@dblink('alter table T1 exchange partition part_name
with table T2@DBLINK
WITHOUT VALIDATION ');

end ;
getting this error
ORA-02149: Specified partition does not exist
ORA-06512: at "SYS.DBMS_UTILITY", line 574


Currently im executin normal insert and it is taking time.
need to do this activity on daliy basis ,so please help.

I have also gove through link which was shared, but i m not getting it Sad

[Updated on: Sun, 22 June 2014 23:06]

Report message to a moderator

Re: Exchange partition from table at remote database [message #616913 is a reply to message #616912] Sun, 22 June 2014 23:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Can you mention your Oracle version. I think Datapump would do that for you. Have a look at Single Partition Transportable for Oracle Data Pump.

[Updated on: Sun, 22 June 2014 23:26]

Report message to a moderator

Re: Exchange partition from table at remote database [message #616914 is a reply to message #616913] Sun, 22 June 2014 23:55 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
its Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Report message to a moderator

Re: Exchange partition from table at remote database [message #616915 is a reply to message #616914] Mon, 23 June 2014 00:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
So you can certainly use Datapump. Check out the link above.

Report message to a moderator

Re: Exchange partition from table at remote database [message #616918 is a reply to message #616915] Mon, 23 June 2014 00:37 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
But in my scenario all partitions are in one tablespace

Report message to a moderator

Re: Exchange partition from table at remote database [message #616920 is a reply to message #616918] Mon, 23 June 2014 00:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
please post test case showing DDL across DBLINK succeeds.

Report message to a moderator

Re: Exchange partition from table at remote database [message #616922 is a reply to message #616920] Mon, 23 June 2014 00:59 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
after creating dblink i have executed below mentioned query

select * from t2@dblink;
and it works fine

Report message to a moderator

Re: Exchange partition from table at remote database [message #616943 is a reply to message #616922] Mon, 23 June 2014 07:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
purnima1 wrote on Sun, 22 June 2014 22:59
after creating dblink i have executed below mentioned query

select * from t2@dblink;
and it works fine


It appears that you do not understand that SELECT is not considered to be DDL

http://en.wikipedia.org/wiki/DDL

as for example executing a partition swap

SQL> create table foobar1@orcl as select * from fubar;
create table foobar1@orcl as select * from fubar
                    *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

[Updated on: Mon, 23 June 2014 07:49]

Report message to a moderator

Re: Exchange partition from table at remote database [message #616987 is a reply to message #616943] Mon, 23 June 2014 22:45 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Hi,
I know we can excute DMl but not DDL. I was sharing that query showing that dblink creation is sucessful.
My question is how can excute DDL over DB links

Report message to a moderator

Re: Exchange partition from table at remote database [message #616988 is a reply to message #616987] Mon, 23 June 2014 22:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>My question is how can excute DDL over DB links

which part of below do you NOT understand????????

ORA-02021: DDL operations are not allowed on a remote database

Report message to a moderator

Re: Exchange partition from table at remote database [message #616989 is a reply to message #616988] Mon, 23 June 2014 23:53 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
I understand this part thats why i tried another method
exec dbms_utility.exec_ddl_statement@db_link('your statment');

but i am not getting how to fit this query for my scenario.


begin
dbms_utility.exec_ddl_statement@dblink('alter table T1 exchange partition part_name
with table T2@DBLINK
WITHOUT VALIDATION ');

end ;
getting this error
ORA-02149: Specified partition does not exist
ORA-06512: at "SYS.DBMS_UTILITY", line 574


but that is also giving error

Report message to a moderator

Re: Exchange partition from table at remote database [message #616990 is a reply to message #616989] Tue, 24 June 2014 00:02 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
You cannot do that. Already been said, DDL operations are not allowed on a remote DB. See here, https://community.oracle.com/message/3586393 someone posted a similar requirement and did similar things and faced the same issue.

Report message to a moderator

Previous Topic: convert char to date
Next Topic: Help Required in SQL Analytic Function.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 16:08:01 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.