Home » SQL & PL/SQL » SQL & PL/SQL » insert append over dblink
insert append over dblink [message #304242] Tue, 04 March 2008 11:08 Go to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I don't know whether this is the way it should work. But I thought let me ask the experts if this is documented anywhere.

Source Database Version :
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Remote Database version :

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for Compaq Tru64 UNIX: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Logged on to remote database (using different session)

SQL> create table test_tab (sno number);

Table created.

Logged on to source database (using different session)

SQL> insert /*+ append */ into test_tab@remote_database (sno)
  2  select l from (select level l from dual connect by level <= 10);

10 rows created.

SQL> select * from test_tab@remote_Database;

       SNO
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Logged on to remote database (using different session)

SQL> select * from test_tab;

no rows selected

Logged on to source database (using different session)

SQL> rollback;

Now creating another table in the source database and inserting using append hint.

SQL> create table source_test_tab(sno number);

Table created.

SQL> insert /*+ append */ into source_test_tab (sno) select level from dual connect by level <= 10;

10 rows created.

SQL> select * from source_test_tab;
select * from source_test_tab
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> drop table source_test_tab purge;



Now my question can't we use append hint when inserting over dblink. The reason I want to use append hint is we are inserting millions and this table gets refreshed every month and the volume of the data will be more or less the same.

You can ask me why you are pushing data rather than pulling it but it is one of those weird case where there is a necessity to push and pull data over dblink. Workaround I have adopted is to pull the data (i.e) execute the same process from the other side. But I would like to know is append hint a valid hint in inserting data over dblink.

Your thoughts on this are welcome.

http://www.orafaq.com/forum/m/274698/94420/?srch=insert+append+dblink#msg_274698

I could see the same discussion happened in the above mentioned url but I am not able to come to a conclusion whether a direct path insert is possible over the dblink or not.

I think this is the reason why Direct path is not happening.
Quote:
A transaction containing a direct-path INSERT statement cannot be or become distributed.


Apologies for being lazy not reading the manual before posting it here.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:549493700346053658

I have inserted the above url to ascertain that it is not feasible.

Moderators if you don't mind could you please lock the thread.

Regards

Raj

[Updated on: Tue, 04 March 2008 11:59]

Report message to a moderator

Re: insert append over dblink [message #304283 is a reply to message #304242] Tue, 04 March 2008 13:38 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
run a commit and you can see the rows.

also, create the table with the noparallel clause, and see what happens.

I suspect you have parallel as a default at the tablespace/database level, and you are creating a parallel table by accident.

If you insert into a table in parallel, you cannot see the results until commit;

[Updated on: Tue, 04 March 2008 13:39]

Report message to a moderator

Re: insert append over dblink [message #304285 is a reply to message #304283] Tue, 04 March 2008 13:43 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
@Coleing,

I think you didn't get my point. I am not saying anywhere in my post that I cannot see my data. All I am trying to say/infer is append hint (in other direct path insert) is been ignored/not possible when you insert into a table via dblink.

Regards

Raj

[Updated on: Tue, 04 March 2008 13:44]

Report message to a moderator

Re: insert append over dblink [message #304286 is a reply to message #304242] Tue, 04 March 2008 14:04 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Ahh, didnt get that bit...

From what you have posted, what makes you think that the append hint is not working?

Another way to tell will be to test with and without append hint for a million rows, and checkout the redo log sizes on the target.


Make sure nothing else is running.
Re: insert append over dblink [message #304287 is a reply to message #304286] Tue, 04 March 2008 14:11 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
A transaction containing a direct-path INSERT statement cannot be or become distributed.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:549493700346053658


Regards

Raj
Previous Topic: no data found !!
Next Topic: Partitioning strategy for over time transactions
Goto Forum:
  


Current Time: Wed Feb 19 06:28:11 CST 2025