A reader, July 08, 2008 - 2:36 pm UTC
Thanks Tom...
Limitation of Queries on remote DB
Snehasish Das, October 29, 2010 - 1:13 pm UTC
Hi Tom,
I was querying a remote DB table with partition name as below.
select * from tab1 partition (Y_FLG) ;
But strangely it gave data of all partitions . Our database version is 10.2.0.2.
Also it would be a great help if you can let me know the site or link where i can get the details as to the behavior of different DDL and DML statements over the remote DB, like how insert on local DB differs from insert on remote db.
Regards,
Snehasish Das,
Tech Assuarance.
Using GTT?
Jason, February 22, 2016 - 2:41 am UTC
I am still seeing that there are errors limiting the insert to the remote table when using global temp table as well?
Am I missing something with your reply Tom about putting the data into a global temp and inserting from there?
Thanks...
February 22, 2016 - 3:27 am UTC
Can you elaborate on what's stopping you ? Here's a demo
mcdonac@db11
SQL> create table I_AM_ON_DB11 (x int, y int );
Table created.
mcdonac@np12
SQL> create database link db11 using 'db11';
mcdonac@np12
SQL> create global temporary table GTT ( x1 int, y1 int )
2 on commit preserve rows;
Table created.
mcdonac@np12
SQL> insert into GTT
2 select rownum, rownum
3 from dual connect by level <= 1000;
1000 rows created.
mcdonac@np12
SQL>
mcdonac@np12
SQL> commit;
Commit complete.
mcdonac@np12
SQL>
mcdonac@np12
SQL> insert into I_AM_ON_DB11@db11
2 select * from GTT;
1000 rows created.
GTT Vs Normal Table
Rajasekhar, July 16, 2020 - 6:50 am UTC
Hello Connor/chris,
Can you explain, how GTT can (perform/support) bulk operations over dblink ? why not normal tables?
Thanks in advance.
July 17, 2020 - 5:19 am UTC
Normal tables are fine, but the question was about using bulk collect/forall across a db link. For *those* circumstance, if you encounter limitations, then a GTT is a workable alternative.
GTT Table
Rajasekhar, July 20, 2020 - 5:09 am UTC
hello Connor,
but the question was about using bulk collect/forall across a db link. For *those* circumstance, if you encounter limitations, then a GTT is a workable alternative.
As per my understanding, data transfer over DBlink is serialized. can you help me in understanding more specificly about bulk operations across DBlink with GTT ?
Thanks in advance.
July 22, 2020 - 5:18 am UTC
SQL> create table t as
2 select rownum x1, rownum x2, rownum x3
3 from dual connect by level <= 10;
Table created.
SQL>
SQL> create database link db19_pdb1 using 'db19_pdb1';
Database link created.
SQL>
SQL>
SQL> select * from t@db19_pdb1;
X1 X2 X3
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10
10 rows selected.
SQL>
SQL> declare
2 type tlist is table of t%rowtype;
3 r tlist;
4 begin
5 select * bulk collect into r from t;
6
7 forall i in 1 .. r.count
8 insert into t@db19_pdb1 values r(i);
9 end;
10 /
forall i in 1 .. r.count
*
ERROR at line 7:
ORA-06550: line 7, column 3:
PLS-00739: FORALL INSERT/UPDATE/DELETE not supported on remote tables
Thanks
Rajasekhar, July 22, 2020 - 6:33 am UTC
Hello Connor,
Thank you so much