Help insert with a select using a DB Link - ORA-03106: fatal two-task communication protocol error
Don
result of a SELECT statement. This select statement uses two views
(outer joined) through a databas link.
I get the following errors when I execute this SQL:
ORA-03106: fatal two-task communication protocol error
ORA-02063: preceding line from DBLINK1
ORA-02063: preceding 2 lines from DBLINK1
The insert statement has 21 columns. One of these columns uses 2
concate functions in the select portion (ex. CONCATE((CONCATE(a,b),c))
.
For brevity here is an example of what I am doind. Only the field
names are different.
INSERT INTO table (
columns ...)
(SELECT columns...
FROM table1@dbLink1, table2@dblink1
WHERE table1.field = table2.field(+)
);
When I run the select portion by itself it works fine. It will return
about 2.5 million rows so I know the insert will be slow.
To get the data there are a series of DB links that span 4 machines
(counting my server).
The views used by this select statement are based on views on another
server (using a DB Link). These source views are based on Tables from
another server (using a DBLink). Should this matter? It's still a
view to my system - how it is populated should not matter. All my
queires on these views work.
Any idea what causes these errors? I have search newsgroup archives
and the few references to these errors are only question - no answers.
Thanks for any help,
Don
Telemachus
put the session in SQL_TRACE and see where it is happening.
Then you will have to put SQL*NET into trace and see which host or protocol
layer it's happening in.
Does it work as CTAS ?
"Don" <an...@someaddress.com> wrote in message
news:qumeju456951u6053...@4ax.com...
Sybrand Bakker
This must be a lousy network, and I don't think anyone even wants to build
recursively distributed solutions. Think about it: you have a distributed
transaction between server a and server b and server b and server c.
Any glitch and you are toast.
--
Sybrand Bakker
Senior Oracle DBA
to reply remove '-verwijderdit' from my e-mail address
Don
governement project so sanity does not apply.
We cannot get access to the source server (server A) for political
reasons.
Another group created the views of server A on Server B. The people
at A know that we use them but for some reason will not give us direct
access.
For politcal reasons we cannot directly access Server B from our
unsecure facility. We set up a server (server C) in a secure
environment and created views based on the views of server B.
Now, from our server D in the unsecured office we can link to server
C. C links to B and B to A.
Everyone knows whats going on. No one is being decieved. This is the
kind of shit I have to put up with. I'll never understand how the
government works but I know technical people are not allowed input in
these decision. The decision are made by some of the stupidest people
I have ever meet.
I am still trying to make this work. Can u offer any help?
Enkidu Utnapishtim
As a former civil-service DBA with DoD (8 years), I know what you're
going thru. I can empathize with you. It is truly a wonder that the
gov't can get anything done. However, knowing that doesn't help you
solve your problem. By the way, the problem you're having is that Oracle
sometimes loses track of where it's supposed to be doing the work when
you have so many intermediary dblinks ... or running out of resources on
one of the intermediary sites as it's building local temporary tables
(at which point it just throws up its hands).
One thing I might offer (though I don't have enuf info to know if it
will help in your situation ... not to mention that at that time I was a
DBA on Oracle 5/6/7) - when we tried to "gather" data from multiple
sites and roll it up to our HQ database, we had a devil of a time
getting it to work. After much time spent on the phone to Oracle tech
support (well, mostly listening to the elevator music while on hold),
they suggested we replace the "INSERT ... SELECT FROM remote(s)" with a
"COPY FROM remote ... SELECT ... FROM remote(s)" statement (don't recall
the exact syntax). We didn't think it would help (after all, the goal in
life for the COPY command is to copy stuff from a non-Oracle database).
But there must be something in the COPY command that doesn't stumble
over requests like the INSERT statement does.
Of course, from the info you gave, it might not be a viable solution for
you. However, when faced with absolute "deliverables" from the gov't,
you might be able to jury-rig something.
HTH,
Roger Crowley - DBA - LearningFramework
Sybrand Bakker
<utnapisht...@hotmail.com> wrote:
>they suggested we replace the "INSERT ... SELECT FROM remote(s)" with a
>"COPY FROM remote ... SELECT ... FROM remote(s)" statement (don't recall
>the exact syntax). We didn't think it would help (after all, the goal in
>life for the COPY command is to copy stuff from a non-Oracle database).
>But there must be something in the COPY command that doesn't stumble
>over requests like the INSERT statement does.
COPY is a SQL*PLUS statement, it is NOT a SQL statement.
Hence the syntax can be found in the sql*plus reference manual.
Your assertion about the goal of the COPY command being designed to
transfer data from non-Oracle databases is nonsense.
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Enkidu
in the Oracle doc set, where they tell you that it was designed for
non-Oracle databases. Or do you just pull all your info out of your feeble
brain?
I wonder, after reading a lot of your posts, if anyone at your office ever
comes to you for help. If your attitude at the office is anything like it is
in this NG, you must be the lonliest DBA on the planet.
Chill out Sybrand. Go outside, take a deep breath. Then come back inside and
answer people's questions without all the sarcasm. OK.
Have a nice life!
Roger Crowley - DBA - LearningFramework
"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote in message
news:8n8ijuo0oshf2m00r...@4ax.com...
Rauf Sarwar
> governement project so sanity does not apply.
>
> We cannot get access to the source server (server A) for political
> reasons.
>
> Another group created the views of server A on Server B. The people
> at A know that we use them but for some reason will not give us direct
> access.
>
> For politcal reasons we cannot directly access Server B from our
> unsecure facility. We set up a server (server C) in a secure
> environment and created views based on the views of server B.
>
> Now, from our server D in the unsecured office we can link to server
> C. C links to B and B to A.
>
> Everyone knows whats going on. No one is being decieved. This is the
> kind of shit I have to put up with. I'll never understand how the
> government works but I know technical people are not allowed input in
> these decision. The decision are made by some of the stupidest people
> I have ever meet.
>
> I am still trying to make this work. Can u offer any help?
Don,
I understand your frustration with this scenario. I am currently
involved with a project where the IT policy in place says..."No
dblinks between distributed databases", even though everything is
sitting inside the firewall. One of the requirements in the project is
to collect information from 8 different databases. We are pushing for
a change in IT policy to accomodate business requirement but it looks
like we have to resort to some other solution.
One of the solutions we are working on is using Informatica and IBM
Websphere (MQ Series) to transfer data from other databases. Good
thing is that customer already has IBM Websphere in place so it would
not be a major task. However, looks like you are stuck with trying to
make this work as it is since government policies in these kind of
scenarios are *very hard* to change.
Have you checked the NLS_LANG parameter and any mismatch between your
client and server D thru A? This is reported (in Metalink) to be one
of the more common problems associated with ORA_03106.
Regards
//Rauf Sarwar
Niall Litchfield
> I agree this is not the ideal situation. Unfortunalty this is a
> governement project so sanity does not apply.
>
> We cannot get access to the source server (server A) for political
> reasons.
>
> Another group created the views of server A on Server B. The people
> at A know that we use them but for some reason will not give us direct
> access.
>
> For politcal reasons we cannot directly access Server B from our
> unsecure facility. We set up a server (server C) in a secure
> environment and created views based on the views of server B.
>
> Now, from our server D in the unsecured office we can link to server
> C. C links to B and B to A.
>
> Everyone knows whats going on. No one is being decieved. This is the
> kind of shit I have to put up with. I'll never understand how the
> government works but I know technical people are not allowed input in
> these decision. The decision are made by some of the stupidest people
> I have ever meet.
Others have given you some technical advice. I'd say to the people who
commissioned the project. 'This is not possible without either the following
permissions on database A, or a change in business process so that the folk
who enter data to server A also enter it to my server".
I know you say that no-one is being decieved but the fact of the matter is
that you *are* accessing data from server A (and maybe B). Either this is
allowed or it isn't (you've discovered a security hole). If it is allowed
then direct access should be allowed. if it isn't the business has to find
some other way of getting you the data.
P.S. I'd also be willing to bet that the folk making the decisions are not
stupid, but merely non technical. Marketing, Management and Accounting are
all disciplines that attract bright able people - they just think
differently. If they pick up that you think they are stupid though you could
be in big trouble.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************