tnsnames
March 10, 2002 - 4am Central time zone
Reviewer: mohammad toaha from Dubai, UAE
My problem description is detailed below:
SQL> CONNECT DEWADBA/DEWADBA -- Connected to local database
Connected.
SQL> CREATE TABLE T12 AS SELECT * FROM W_NODE@DISPDC;
CREATE TABLE T12 AS SELECT * FROM W_NODE@DISPDC
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
SQL> CREATE TABLE T12 AS SELECT * FROM W_NODE@DISPDC;
CREATE TABLE T12 AS SELECT * FROM W_NODE@DISPDC
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
SQL> CONNECT DEWADBA/DEWADBA@DISPDC -- connected to remote database
Connected.
Then,as an alternative, I tried to cretae a database link as below:
SQL> create database link dispdc1
2 connect to dewadba identified by dewadba98
3 using 'dispdc';
Database link created.
SQL> connect dewadba/dewadba
Connected.
SQL> create table t12 as select * from w_node@dispdc1;
create table t12 as select * from w_node@dispdc1
*
ERROR at line 1:
ORA-02085: database link DISPDC1.world connects to ORACLE.WORLD
- I worked around the tnsnames.ora file in various ways but could not succeed.
Followup March 10, 2002 - 6pm Central time zone:
search for ora-02085 on this site and you'll see what needs be done for that.
For the first problem, the ora-02019 - you don't have a DATABASE LINK yet, so I would not expect
that to work.
How to create synonym for a database link
September 7, 2002 - 2am Central time zone
Reviewer: Suresh&kanagaraj from Chennai
Hi Tom,
I have created a dblink as follows
create PUBLIC database link ORACLE.US.ACME.COM
connect to scott
identified by tiger
using 'VINSERVER'
select * from emp@ORACLE.US.ACME.COM
/
EMPNO ENAME JOB MGR HIREDATE SAL COMM
------ ---------- --------- ---------- --------- ---------- ----------
DEPTNO
------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
Then i have created a synonym for this database link as follows
1* create PUBLIC synonym syn_oracle for emp@ORACLE.US.ACME.COM
SQL> /
Synonym created.
Now when i describe the synonym i get the following error
SQL> DESC SYN_ORACLE;
ERROR:
ORA-02019: connection description for remote database not found
Can you pls help
Thanks
Suresh&Kanagaraj
Followup September 7, 2002 - 10am Central time zone:
describe doesn't always work over a DBLINK.
My *preferred* method for doing this is:
create view V as select * from emp@whereever;
That has the added benefit that the metadata for V is pulled into our local database (eg: you can
select * from user_tab_columns where table_name = 'V' for example). More applications -- specially
ones that rely on the data dictionary -- can use V this way then if V was a public synonym (and I'm
not very fond of public synonyms in GENERAL -- they have performance implications and more
importantly -- they polute the namespace, leading to issues with more then one application in a
database. What happens when app1 has an EMP table ana so does app2? Who gets the public synonym?
I rarely use them)
time out tns
February 4, 2003 - 11am Central time zone
Reviewer: Marcio from Brazil
Tom, What is wrong?
sre_d@SISNUM8I> exec print_table ( 'select * from user_db_links' );
DB_LINK : MRP.AQUARIUS.CPQD.COM.BR
USERNAME : SCOTT
PASSWORD : TIGER
HOST : mrp
CREATED : 04-feb-2003 13:50:59
-----------------
PL/SQL procedure successfully completed.
sre_d@SISNUM8I> select * from global_name;
GLOBAL_NAME
----------------------------------------------------------------------
SISNUM8I.AQUARIUS.CPQD.COM.BR
sre_d@SISNUM8I> select * from dual@mrp;
select * from dual@mrp
*
ERROR at line 1:
ORA-12535: TNS:operation timed out
sre_d@SISNUM8I> alter session set global_names = false;
Session altered.
sre_d@SISNUM8I> select * from dual@mrp;
select * from dual@mrp
*
ERROR at line 1:
ORA-12535: TNS:operation timed out
sre_d@SISNUM8I> alter session set global_names = true;
Session altered.
sre_d@SISNUM8I> select * from dual@mrp;
select * from dual@mrp
*
ERROR at line 1:
ORA-12535: TNS:operation timed out
sre_d@SISNUM8I>
sre_d@SISNUM8I> @conn scott/tiger@mrp
scott@MRP816>
C:\Oracle\Ora81\network\ADMIN>type sqlnet.ora
# SQLNET.ORA Network Configuration File: c:\Oracle\Ora81\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# NAMES.DEFAULT_DOMAIN = aquarius.cpqd.com.br
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
C:\Oracle\Ora81\network\ADMIN>type tnsnames.ora
# TNSNAMES.ORA Network Configuration File: c:\Oracle\Ora81\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
EXTPROC_CONNECTION_DATA.AQUARIUS.CPQD.COM.BR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
mrp =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = CPQD020496)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mrp816)
)
)
sre =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = CPQD050068)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SISNUM8I)
)
)
C:\Oracle\Ora81\network\ADMIN>type listener.ora
# LISTENER.ORA Network Configuration File: c:\Oracle\Ora81\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = CPQD020496)(PORT = 1521))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = CPQD020496)(PORT = 2481))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\Oracle\Ora81)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = mrp816)
(ORACLE_HOME = c:\Oracle\Ora81)
(SID_NAME = mrp816)
)
)
Followup February 4, 2003 - 12pm Central time zone:
well, the mrp in a database link has no connection, relationship or anything to mrp used in
"sqlplus scott/tiger@mrp"
the mrp "host" in the dblink is resolved using the SERVERS tnsnames.ora -- not yours.
the mrp in the connect string scott/tiger@mrp is resolved using your tnsnames.ora.
the two most likely have nothing to do with eachother. You need to look at the tnsnames.ora file
on the server, figure out what MRP means there and then you can determine why that server (which
may will be different then your mrp server) is not responding.
Thank you
February 5, 2003 - 6am Central time zone
Reviewer: Marcio from Brazil
ALWAYS -- You right.
Fake DB Links and connections
February 18, 2003 - 8am Central time zone
Reviewer: C. Parmentier from France
Hi Tom,
I have a few more questions about DB Links, how will Oracle8i behaves when he sees a such SQL
Request
(MYDB is NOT a DB Link, it's the name of the DB and no Loopback Links has been created for it)
SQLPLUS /@MYDB
SELECT * FROM MY_TABLE@MYDB
IMHO It runs like :
1) Read the database link tables to see if it is a dblink if so make a connection using that FALSE
2) Look at the order of the sqlnet.ora file to see where it is picking it's tnsname aliases from.
In this case the format is TNSNAMES,ONAMES
3) Read tnsnames.ora to find out where MYDB is TRUE
4) Go out of the database and create a new oracle connection into the server and database - TRUE -
resource used.
How can I really point this out using V$...
Thanks a lot for the help!
PS: You would say "Why ?, just remove the @MYDB of your request!" and you would be right ;) But,
our process has to work on a single DB or on 3 DB for each areas...
PPS: You're really impossible to be reached at GMT+1 :) I should be working at night!
Followup February 18, 2003 - 9am Central time zone:
what is a "fake dblink"??
If your process has to work on a single db or 3 db's -- you should use VIEWS to hide that fact.
You setup each database so the objects actually exist. Views can be used for location transparency
very nicely.
No views
February 18, 2003 - 9am Central time zone
Reviewer: C. Parmentier from France
Well, i'll keep the views in mind, would you please explain the case I proposed:
how does oracle behaves when Selecting from table@<localdbname>, will a new connection be created
or will Oracle check that localdbname is not a Database Link and thus, won't look out the TNS and
so on.
Thanks and Regards,
Followup February 18, 2003 - 6pm Central time zone:
<localdbname> WILL BE a database link. I don't know what you mean by "a fake database link".
March 2, 2003 - 11pm Central time zone
Reviewer: green from China
Hi Tom,
I have database A,B,C.
I only have accounts for A and B, but in A I can use a public dblink to access the table in C,
right now I want to access the table in C from the database B. Is it possible ?
Thanks!
Followup March 3, 2003 - 6am Central time zone:
if you create the same public dblink in B sure, else B will have to query a view on A that queries
the table on C which would probably be about as slow as you could make it go.
March 3, 2003 - 11am Central time zone
Reviewer: green from China
If I have not priviledges to create a view in A which is a Production database ( I only have SELECT
priviledges)and no public link from B to C, so is it possible ?
Thanks!
Followup March 3, 2003 - 12pm Central time zone:
You'll need it -- or create synonym privilege.
there is no way to say "select * from (t@some_other_site)@some_site"
you would need a view or a synonym at some_site so you can query
select * from v@some_site
select * from s@some_site.
Global Name
March 4, 2003 - 7am Central time zone
Reviewer: Abubaker Khered from Jeddah, Soudi Arabia
Hi Tom,
Is there any way to change global_name in DB 8.0.5., other then updating global_name table using
sqlplus?
Thanks,
Abubaker
Followup March 4, 2003 - 6pm Central time zone:
umm, you cannot do that.
you can however use the ALTER DATABASE command.
March 4, 2003 - 12pm Central time zone
Reviewer: jens
hello tom,
is there any way to set/get values of package variables on the remote database through a database
link (our application uses such variables as an "environment" used within views).
Followup March 4, 2003 - 6pm Central time zone:
you need getter/setter routines.
You MUST have "get" routines -- that is what the views would use as a view cannot just reference a
PLSQL variable directly.
You should add "set" routines to set the values.
short of that, no not really:
ops$tkyte@ORA815> create synonym remote_pkg for remote_pkg@ora817dev.us.oracle.com;
Synonym created.
ops$tkyte@ORA815> exec remote_pkg.variable := 55;
BEGIN remote_pkg.variable := 55; END;
*
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00512: Implementation Restriction: 'REMOTE_PKG.VARIABLE': Cannot directly access remote package
variable or cursor
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
(well, you could use DBMS_SQL@remote to dynamically execute a remote block but that would be
painful)
March 5, 2003 - 2pm Central time zone
Reviewer: jens
is there any way to use packages variables on REMOTE site through set/get routines (package states
are LOCAL to a session)?
Followup March 5, 2003 - 6pm Central time zone:
yes, if you have a package with getter/setter routines on the remote site, it works as normal (you
HAVE a session on the remote site)
Loopback dblink
March 25, 2003 - 3pm Central time zone
Reviewer: Dave from Prakash, India
Tom, could you please clarify what is a loopback db link and how it is useful? Thanks as always.
Followup March 25, 2003 - 8pm Central time zone:
it is what it sounds like, a dblink that connects back to the SAME database.
It is useful for testing...
It is useful for connecting as another user in your current session (eg: the loopback link can
connect as scott/tiger whilst you are logged in as bob)...
example please
March 25, 2003 - 9pm Central time zone
Reviewer: Dave
Tom, could you give an example? Thanks.
Followup March 26, 2003 - 7am Central time zone:
example of what? a create database link statement??
ops$tkyte@ORA920> create database link ora920@loopback
2 connect to scott
3 identified by tiger
4 using 'ora920';
Database link created.
ops$tkyte@ORA920> select * from dual@ora920@loopback;
D
-
X
ops$tkyte@ORA920> select * from emp;
no rows selected
ops$tkyte@ORA920> select count(*) from emp@ora920@loopback;
COUNT(*)
----------
14
Beautiful Information
March 26, 2003 - 3am Central time zone
Reviewer: George Kennedy (GK) from USA
I've 4 databases A,B,C an D which are connected through one another through database links. A
single schema can have multiple links which connect to different schemas on the remote database.
All the links were created by using the option 'connect to <username> identified by <password>'.
Now if I change the password of the user on database D which is referred by the links on A,B and C.
Can you please tell me
1. Do the links on A,B, and C become "INVALID" which access the particular schema in database D.
2. Do I've to re-create the DB links again on A,B, and C ? or is there any other way to recompile
the links ?
3. How to identify the links which are affected on A,B and C by the password change ?
Followup March 26, 2003 - 7am Central time zone:
1) no, they just stop working.
2) yes, you need to recreate them.
3) select db_link, username from all_db_links;
Database Link and FGAC
March 28, 2003 - 1pm Central time zone
Reviewer: Ray from Ottawa,On
I need to pull data from Server A to another server B periodically. Server A has data protected via
FGAC and application context. What has to be done to access the data on server A?
tia for your help.
Followup March 28, 2003 - 4pm Central time zone:
just query it? FGAC is designed to be transparent
database links
April 23, 2003 - 11am Central time zone
Reviewer: Rich from NJ
I was asked by my management to quantify the number of times a database link was used to connect to
a remote database. Is there a way?
Thanks.
Followup April 23, 2003 - 7pm Central time zone:
easiest way I can think is you hide the links with views (lots of good reasons for doing that in
any case) and audit selects on the views. That'll tell you pretty much "exactly"
database link and NTS
May 14, 2003 - 8am Central time zone
Reviewer: Oracle-Leo from India
Hi,
I just faced a peculiar problem while using database links.
I have two databases on one Win2K server and wanted one DB to be accessed by another DB using
database links. After creating the db link, when i tried to access the table in second DB using the
command 'select * from url@abc', it gave me the error 'ORA-12640 Authentication adapter
initialization failed'.
I found a note on web which stated that this could be resolved by commenting/removing the
sqlnet.authenication_services='NTS' in SQLNET.ora file. It did solve my error, but was curious why
did it matter. And how could I make it work with sqlnet.authentication_service=NTS.
Thanks
Followup May 14, 2003 - 3pm Central time zone:
you would need windows "security stuff" like domains and such such up. See the windows Oracle
admin guide (part of your doc set). I run no windows, never set it up myself. It is security
specific to that OS.
hide the link?????
May 14, 2003 - 5pm Central time zone
Reviewer: Reader from Salt Lake City, UT
"easiest way I can think is you hide the links with views (lots of good reasons for doing that in
any case) and audit selects on the views. That'll tell you
pretty much "exactly"".
Tom, what do you mean by hide the links with views? can you please clarify? I am going blank on
this idea. any example would be appreciated. Thanks.
Followup May 14, 2003 - 11pm Central time zone:
create view v as select * from t@dblink;
grant select on v to ....;
audit the selects on v.
people don't use the dblink, they use a view.
performance implications?
May 14, 2003 - 9pm Central time zone
Reviewer: Kamal Kishore from New Jersey, USA
Hi Tom,
Can't "count" how many times I have been benefited by the discussions and explanations here. Really
learned a lot. This count query never returns - it's still counting - no matter how much we tune
it...
As for the question:
In your earlier reply to this thread you said this:
"(and I'm not very fond of
public synonyms in GENERAL -- they have performance implications"
Just curious about the performance issues, if any, of using synonyms as compared to views? Apart
from the maintenance issues and global confusion, Should we stay away from synonyms (to remote
objects) and just use views? What performance issues should we be aware of?
Thanks,
Followup May 14, 2003 - 11pm Central time zone:
search for
steve adams public synonyms
on this site.
db links on other database platforms.
May 15, 2003 - 2am Central time zone
Reviewer: Alvin from Philippines.
Are DB links Oracle only ?
can i create a dblink to another db platform say
oracle ->mySql
oracle ->sqlserver
oracle -> (new database platform)
do you have a link that some docs on 'DATABASE STANDARDS'.]
Thanks
Followup May 15, 2003 - 9am Central time zone:
a dblink is an oracle "device" if you will.
Others have similar things, but different. Many other databases cannot do distributed queries
naturally -- they won't have dblinks or anything remotely similar.
Yes, you can create dblinks to other databases from Oracle - either via gateways (part of iAS) or
via generic connectivity
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206
links
July 31, 2003 - 8pm Central time zone
Reviewer: mo
Tom:
I copied a database to a laptop. I created two databases because it had links. When I exported
tables from the second database PAP and treid to import it on the laptop it said it need DBA
privileges so I imported them as system manager rather than PAPADMIN account.
I went to the first database INS and created a database link to PAP using system/manager since all
tables are under that schema in the 2nd database.
When I tried to use it it gacve me an error:
ORA-12640: Authentication adapter initialization failed.
On another try with a different login I got
ORA-02019: Connection description from remore database not found. tnsnames.ora did have the service
name.
Do you know what is causing these errors and what i have to do to fix it?
Thank you,
links
October 16, 2003 - 6pm Central time zone
Reviewer: mo
Tom:
I have two servers A and B. One server has oracle 8i and the other has 9i. When I try to create a
link I get an erros:
I execute this on server A (9i) in SQL*PLUS for windows.
SQL> create database link test
connect to insadmin identified by insadmin
using 'insd';
Database link created.
SQL> desc intreq@test;
ERROR:
ORA-12154: TNS:could not resolve service name
1. Does this mean that service name 'insd' does not exist in the tnsnames.ora on the server.
2. When you create a link that goes from one server to another is the definition in the tns entry
where you key in the ip or the name of the other server.
Thanks
Followup October 16, 2003 - 7pm Central time zone:
1) correct
2) the server the database you created the link is the one that needs to be able to resolve the
link
ora-02019
November 17, 2003 - 9pm Central time zone
Reviewer: dash
I created two DBLINK db1,db2.
(db1,db2 is right tnsname)
select * from test1@db1 is good.
select * from test2@db2 is good.
insert into test1@db1 values('..') is good.
insert into test2@db2 values('..') is good.
but
insert into test1@db1 (select * test2@db2) is error.
ORA-02019: connection description for remote database not found
ORA-02063: preceding stringstring from db(this db sid)
ORA-02063: 2 lines...(db1로 ..)
how do I solve this problem?
Followup November 21, 2003 - 7am Central time zone:
let's see a sqlplus cut and paste of this.
Why display password?
November 19, 2003 - 2am Central time zone
Reviewer: Praveen from India
Hi Tom,
I found out from all the three dblinks view that ALL_DB_LINKS and DBA_DB_LINKS doesn't display
the password in literal text format. But USER_DB_LINKS displays the password as text. Why is it so
and is there any workaround for hiding this password.
Thanks,
Praveen.
Followup November 21, 2003 - 11am Central time zone:
umm, you own the link -- you can only see your OWN private links. you already know the password.
November 21, 2003 - 4pm Central time zone
Reviewer: Akhil from NJ
Hi Tom,
When the remote database password changes, how do we synch all the dblinks that use this password.
We have different groups handling different instances which has dblinks pointing across each other.
Our password policy requires frequent change of passwords for all database accounts. How can we
automate this? Do you have any script / suggestions which will automatically synch the dblinks
after the remote db password change? Thanks verymuch for your time.
Regards,
Akhil
Followup November 21, 2003 - 5pm Central time zone:
if you use GLOBAL USERS, then accounts are managed centrally - no need for passwords in each
database.
if you use plain old user/passwords -- you are responsible for syncing them up. There is no way we
can (we don't know what databases have a dblink that point to "us" -- we cannot sync them up)
Similar situation as dash above
December 4, 2003 - 9am Central time zone
Reviewer: A reader from NJ
Hi Tom,
We had a similar situation where the inserts with a subquery over a DB link are not working. The
individual SELECT queries are okay.
We had encountered it in the past, but dismissed it as something unusual since we were selecting on
a table over DBLink1 which is actually a synonym for another table on DBLink2 as defined in
DBLink1, and since it was new development, we just used other options available.
This time however, we encountered it on existing code after migrating our DEV environment from
8.1.7 to 9i.
Here's our environment information:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
We have created a workaround by prestoring the results of the subquery in a local table, but was
wondering why we have had to do this. The problematic query was working in 8.1.7 before.
Thanks a lot for any insight you can provide on this matter. I have attached a copy of the log file
for the sample SQLPlus session, with the INSERT statement in question added at the end.
SQL>
SQL> set timing on;
SQL> set time on;
08:40:08 SQL> set trimspool on;
08:40:08 SQL>
08:40:08 SQL> ------------------------------------------
08:40:08 SQL> -- create test table
08:40:08 SQL> ------------------------------------------
08:40:08 SQL> drop table test_db_link;
Table dropped.
Elapsed: 00:00:00.01
08:40:09 SQL>
08:40:09 SQL> create table test_db_link
08:40:09 2 as
08:40:09 3 select tracknum, status, transdate, insseq
08:40:09 4 from workhistory@wkl
08:40:09 5 where rowid = '';
Table created.
Elapsed: 00:00:00.00
08:40:09 SQL>
08:40:09 SQL> desc test_db_link;
Name Null? Type
----------------------------------------- -------- ----------------------------
TRACKNUM NOT NULL VARCHAR2(18)
STATUS CHAR(1)
TRANSDATE DATE
INSSEQ NUMBER
08:40:09 SQL>
08:40:09 SQL> select * from test_db_link;
no rows selected
Elapsed: 00:00:00.00
08:40:09 SQL>
08:40:09 SQL> ------------------------------------------
08:40:09 SQL> -- test subquery -- workhistory@wkl is indexed on insseq and tracknum
08:40:09 SQL> -- with updated statistics
08:40:09 SQL> ------------------------------------------
08:40:09 SQL> select distinct tracknum from workhistory@wkl where insseq = 142;
TRACKNUM
------------------
010600BAT51419PI
Elapsed: 00:00:00.00
08:40:09 SQL>
08:40:09 SQL> ------------------------------------------
08:40:09 SQL> -- test select with subquery
08:40:09 SQL> ------------------------------------------
08:40:09 SQL> select tracknum, status, transdate, insseq
08:40:09 2 from workhistory@wkl
08:40:09 3 where tracknum in (select distinct tracknum from workhistory@wkl where insseq =
142);
TRACKNUM S TRANSDATE INSSEQ
------------------ - --------- ----------
010600BAT51419PI 2 06-JAN-00 142
010600BAT51419PI 2 07-JAN-00 143
010600BAT51419PI 3 07-JAN-00 144
010600BAT51419PI 5 07-JAN-00 145
Elapsed: 00:00:00.00
08:40:09 SQL>
08:40:09 SQL> ------------------------------------------
08:40:09 SQL> -- test insert with select WITHOUT subquery
08:40:09 SQL> ------------------------------------------
08:40:09 SQL> insert into test_db_link
08:40:09 2 select tracknum, status, transdate, insseq
08:40:09 3 from workhistory@wkl
08:40:09 4 where tracknum = '010600BAT51419PI'
08:40:09 5 ;
4 rows created.
Elapsed: 00:00:00.00
08:40:09 SQL>
08:40:09 SQL> spool off;
------------------------------------------
-- test insert with select WITH subquery
-- WE HAD TO CANCEL THIS QUERY SINCE IT
-- DOESN'T SEEM TO FINISH AT ALL, EVEN
-- AFTER LEAVING IT RUNNING FOR SEVERAL
-- MINUTES.
------------------------------------------
insert into test_db_link
select tracknum, status, transdate, insseq
from workhistory@wkl
where tracknum in (select distinct tracknum from workhistory@wkl where insseq = 142);
Followup December 4, 2003 - 9am Central time zone:
look at the query plans.
Here's the Explain Plan
December 4, 2003 - 10am Central time zone
Reviewer: A reader from NJ
Hi Tom,
We decided to wait for the INSERT to finish. It took more than 15 minutes with the filter "insseq =
142". The actuak filter is "insseq >= :var".
Thanks a lot for your help. I will be sending in the TKPROF results as well.
SQL>
SQL> set autotrace traceonly explain;
SQL>
SQL> set timing on;
SQL> set time on;
10:08:57 SQL> set trimspool on;
10:08:57 SQL> ----------------------------------------------------------------------
10:09:03 SQL> -- test subquery
10:09:03 SQL> ----------------------------------------------------------------------
10:09:03 SQL> select distinct tracknum from workhistory@wkl where insseq = 142;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=4 Card=1 By
tes=21)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'WORKHISTORY' (Cost=2 Car ORANETRA
d=1 Bytes=21)
2 1 INDEX (UNIQUE SCAN) OF 'WKLHINSSEQ' (UNIQUE) (Cost=2 Car ORANETRA
d=1140186)
10:09:03 SQL>
10:09:03 SQL> ----------------------------------------------------------------------
10:09:14 SQL> -- test select with subquery
10:09:14 SQL> ----------------------------------------------------------------------
10:09:14 SQL> select tracknum, status, transdate, insseq
10:09:14 2 from workhistory@wkl
10:09:14 3 where tracknum in (select distinct tracknum from workhistory@wkl where insseq = 142);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=4 Card=1 By
tes=50)
1 0 NESTED LOOPS (Cost=4 Card=1 Bytes=50)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'WORKHISTORY' (Cost=2 C ORANETRA
ard=1 Bytes=21)
3 2 INDEX (UNIQUE SCAN) OF 'WKLHINSSEQ' (UNIQUE) (Cost=2 C ORANETRA
ard=1140186)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'WORKHISTORY' (Cost=2 C ORANETRA
ard=1 Bytes=29)
5 4 INDEX (RANGE SCAN) OF 'WKHTRACKNUMIDX' (NON-UNIQUE) ORANETRA
10:09:14 SQL>
10:09:14 SQL> ----------------------------------------------------------------------
10:09:14 SQL> -- test insert with select WITHOUT subquery
10:09:14 SQL> ----------------------------------------------------------------------
10:09:14 SQL> insert into test_db_link
10:09:14 2 select tracknum, status, transdate, insseq
10:09:14 3 from workhistory@wkl
10:09:14 4 where tracknum = '010600BAT51419PI'
10:09:14 5 ;
4 rows created.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=36)
1 0 REMOTE* (Cost=4 Card=1 Bytes=36) WKL
1 SERIAL_FROM_REMOTE SELECT "TRACKNUM","STATUS","TRANSDATE","INSS
EQ" FROM "WORKHISTORY" "WORKHISTORY"
10:09:14 SQL>
10:09:15 SQL> ----------------------------------------------------------------------
10:09:24 SQL> -- test insert with select WITH subquery
10:09:24 SQL> ----------------------------------------------------------------------
10:09:24 SQL> insert into test_db_link
10:09:24 2 select tracknum, status, transdate, insseq
10:09:24 3 from workhistory@wkl
10:09:24 4 where tracknum in (select distinct tracknum from workhistory@wkl where insseq = 142);
4 rows created.
Elapsed: 00:15:36.00
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=172346 Card=57009 By
tes=2052324)
1 0 FILTER
2 1 REMOTE* (Cost=1319 Card=57009 Bytes=2052324) WKL
3 1 REMOTE* (Cost=3 Card=1 Bytes=24) WKL
2 SERIAL_FROM_REMOTE SELECT "TRACKNUM","STATUS","TRANSDATE","INSS
EQ" FROM "WORKHISTORY" "SYS_ALIAS_1"
3 SERIAL_FROM_REMOTE SELECT "TRACKNUM","INSSEQ" FROM "WORKHISTORY
" "WORKHISTORY" WHERE "TRACKNUM"=:1
10:25:00 SQL>
10:25:00 SQL> spool off;
Followup December 4, 2003 - 11am Central time zone:
so it is pulling both back and then processing.
if you use driving_site and push to the remote does it affect the plan (just use autotrace
traceonly explain, no need to run it)
And here's the TKProf output
December 4, 2003 - 10am Central time zone
Reviewer: A reader from NJ
Hi Tom,
Here's what we got from TKPROF.
Thanks.
TKPROF: Release 9.2.0.3.0 - Production on Thu Dec 4 10:21:54 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: metrics_ora_12441_TESTDBLINK.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set events '10046 trace name context forever, level 8'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 43
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.02 0.02
SQL*Net message to dblink 11 0.00 0.00
SQL*Net message from dblink 11 0.02 0.05
********************************************************************************
insert into test_db_link
select tracknum, status, transdate, insseq
from workhistory@wkl
where tracknum in (select distinct tracknum from workhistory@wkl where insseq = 142)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.15 0 4 1 0
Execute 1 617.98 1083.94 0 1 3 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 618.01 1084.10 0 5 4 4
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
4 FILTER (cr=0 r=0 w=0 time=2977732 us)
1139808 REMOTE (cr=0 r=0 w=0 time=90516637 us)
1 REMOTE (cr=0 r=0 w=0 time=62446423 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 868838 0.00 3.02
SQL*Net message from dblink 868838 0.64 641.27
SQL*Net more data from dblink 16095 0.02 2.57
latch free 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.05 0.05
********************************************************************************
alter session set events '10046 trace name context off'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 43
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.03 0.15 0 4 1 0
Execute 3 617.98 1083.94 0 1 3 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 618.01 1084.10 0 5 4 4
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.05 0.07
SQL*Net message to dblink 868849 0.00 3.02
SQL*Net message from dblink 868849 0.64 641.32
SQL*Net more data from dblink 16095 0.02 2.57
latch free 1 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 4 0 1
Misses in library cache during parse: 0
3 user SQL statements in session.
1 internal SQL statements in session.
4 SQL statements in session.
********************************************************************************
Trace file: metrics_ora_12441_TESTDBLINK.trc
Trace file compatibility: 9.00.01
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
1 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
1753850 lines in trace file.
Re-written Query
December 4, 2003 - 12pm Central time zone
Reviewer: A reader from NJ
Hi Tom,
We are not quite sure how to "use driving_site and push to the remote".
That did prod us to rethink our query, though, and we just combined the subquery with the other
one, eliminating both the temporary table and the subquery. We just re-wrote the query to use
SELECT DISTINCT and joins over the DBLink.
While we still don't quite understand what was wrong with the INSERT over a DBLINK using a SELECT
with a SUBQUERY, we are quite satisfied with the new workaround. We just hope that we don't
encounter this problem again later, as this is the second time that we did.
Thanks again.
11:52:51 SQL>
11:52:51 SQL> set autotrace traceonly explain;
11:52:51 SQL>
11:52:51 SQL> set timing on;
11:52:51 SQL> set time on;
11:52:51 SQL> set trimspool on;
11:52:51 SQL>
11:52:51 SQL> ----------------------------------------------------------------------
11:52:51 SQL> -- test insert with JOINS on DB LINKS (exact INSSEQ)
11:52:51 SQL> ----------------------------------------------------------------------
11:52:51 SQL> insert into test_db_link
11:52:51 2 select DISTINCT a.tracknum, a.status, a.transdate, a.insseq
11:52:51 3 from workhistory@wkl a, workhistory@wkl b
11:52:51 4 where a.tracknum = b.tracknum
11:52:51 5 and b.insseq=142
11:52:51 6 ;
4 rows created.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=60)
1 0 SORT (UNIQUE) (Cost=12 Card=1 Bytes=60)
2 1 NESTED LOOPS (Cost=8 Card=1 Bytes=60)
3 2 REMOTE* (Cost=3 Card=1 Bytes=24) WKL
4 2 REMOTE* WKL
3 SERIAL_FROM_REMOTE SELECT "TRACKNUM","INSSEQ" FROM "WORKHISTORY
" "B" WHERE "INSSEQ"=142
4 SERIAL_FROM_REMOTE SELECT "TRACKNUM","STATUS","TRANSDATE","INSS
EQ" FROM "WORKHISTORY" "A" WHERE "TR
11:52:51 SQL>
11:52:51 SQL> ----------------------------------------------------------------------
11:52:51 SQL> -- test insert with JOINS on DB LINKS (from last INSSEQ)
11:52:51 SQL> ----------------------------------------------------------------------
11:52:51 SQL> insert into test_db_link
11:52:51 2 select DISTINCT a.tracknum, a.status, a.transdate, a.insseq
11:52:51 3 from workhistory@wkl a, workhistory@wkl b
11:52:51 4 where a.tracknum = b.tracknum
11:52:51 5 and b.insseq >= 14500000
11:52:51 6 ;
29861 rows created.
Elapsed: 00:00:11.05
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=1402 Card=1139 Bytes
=68340)
1 0 SORT (UNIQUE) (Cost=1402 Card=1139 Bytes=68340)
2 1 REMOTE* (Cost=1319 Card=1140186 Bytes=41046696) WKL
2 SERIAL_FROM_REMOTE SELECT "A1"."TRACKNUM","A1"."STATUS","A1"."T
RANSDATE","A1"."INSSEQ","A1"."TRACKN
11:53:03 SQL>
11:53:03 SQL> spool off;
December 11, 2003 - 10am Central time zone
Reviewer: Kashif from Reston, VA
Hi Tom,
You mentioned in one of your responses:
"so it is pulling both back and then processing."
which I understood to mean that instead of executing the query remotely and bringing down the
result data, the statement brought the data down to the local db and then did the work. Is that
correct?
Secondly, how were you able to tell that that is what happened?
Finally, what does the term "SERIAL_FROM_REMOTE" in the Execution Plan mean? I looked the term up
in the Performance Tuning Guide, and here's what it said: 'Serial execution at a remote site'.
Which I understand to mean that the query was executed remotely, and then the results brought down
to the local db. But that does not sound like what you had concluded for that query.
Any feedback would be helpful. Thanks.
Kashif
Followup December 11, 2003 - 12pm Central time zone:
the query plan showed us that -- two remote operations followed by a filter. gives us the remote
query that was executed and all.
it is basically taking:
2 select tracknum, status, transdate, insseq
10:09:24 3 from workhistory@wkl
10:09:24 4 where tracknum in (select distinct tracknum from workhistory@wkl
where insseq = 142);
and executing it like this:
for x in ( select * from remote_table )
loop
query remote table again by tracknum to get tracknum and insseq
if one of the the returned insseq's is 142
then
output record
end if
end loop
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=172346 Card=57009 By
tes=2052324)
1 0 FILTER
2 1 REMOTE* (Cost=1319 Card=57009 Bytes=2052324) WKL
3 1 REMOTE* (Cost=3 Card=1 Bytes=24) WKL
2 SERIAL_FROM_REMOTE SELECT "TRACKNUM","STATUS","TRANSDATE","INSS
EQ" FROM "WORKHISTORY" "SYS_ALIAS_1"
3 SERIAL_FROM_REMOTE SELECT "TRACKNUM","INSSEQ" FROM "WORKHISTORY
" "WORKHISTORY" WHERE "TRACKNUM"=:1
confusing on the db links
January 7, 2004 - 10pm Central time zone
Reviewer: mike
Tom,
I have a database DB_X on server X. I want to create the database links to two remote databases
DB_Y1/Y2 on two
different server Y1/Y2.
The tnsnames.ora:
DB_Y1 = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=serv_y1)(PORT=1526))
(CONNECT_DATA=(SID=DB_Y1))
)
DB_Y2 = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=serv_y2)(PORT=1531))
(CONNECT_DATA=(SID=DB_Y2))
)
To create the database links, for DBY1 the following works:
create database link DBY1 connect to user1 identified by user1 using 'DB_Y1'
/
However the db link for DBY2 does not work if the format of creation used as above.
I have to create it as :
create database link DBY2 connect to user1 identified by user1
using '(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=serv_y2)(PORT=1531))(CONNECT_DATA=(SID=DB_Y2)))'
/
Question: for what reasons the full description of tnsname has to be used for the DB_Y2/serv_y2?
Followup January 8, 2004 - 12pm Central time zone:
are you showing me the tnsnames.ora entry FROM THE DATABASE SERVER
or are you showing me YOUR tnsnames.ora entry, and the one on the server only really has db_y1 in
it.
the dblink cannot see "your" tnsnames.ora, it sees the one on the database server.
sorry for was not very clear
January 8, 2004 - 1pm Central time zone
Reviewer: A reader
The tnsnames.ora entry shown is on server X. DB_X is on server X.
Both DB_Y1 and DB_Y2 are on remote servers (two different servers or machines).
The db link in question is on DB_X to DB_Y2 on server Y2
Mike
Followup January 8, 2004 - 3pm Central time zone:
i don't see the "error" but tell you what. log DIRECTLY INTO the server with DB_X
show us a CUT AND PASTE of
$ tnsping db_y1
$ tnsping db_y2
$ sqlplus u/p
create database link to y1
select * from dual@y1
create database link to y2
select * from dual@y2
CUT AND PASTE it, no editing
synonym for remote stored function doesn't work
February 5, 2004 - 5pm Central time zone
Reviewer: James Su from Toronto CA
SQL> create or replace function ff return number is begin return 200; end;
2 /
Function created.
SQL> conn XXXX/XXXX@dev32
Connected.
SQL> create database link dbl_rep connect to XXXXXXX identified by XXXXXXXX using 'dev2';
Database link created.
SQL> select * from dual@dbl_rep;
D
-
X
SQL> select ff@dbl_rep from dual;
FF
----------
200
SQL> create or replace synonym sy_test for ff@dbl_rep;
Synonym created.
SQL> select sy_test from dual;
select sy_test from dual
*
ERROR at line 1:
ORA-00904: "SY_TEST": invalid identifier
Followup February 6, 2004 - 8am Central time zone:
should work fine:
ops$tkyte@ORA9IR2> create or replace function ff return number
2 as
3 begin
4 return 55;
5 end;
6 /
Function created.
ops$tkyte@ORA9IR2> select ff@ora9ir2@loopback from dual;
FF
----------
55
ops$tkyte@ORA9IR2> create or replace synonym sy_test for ff@ora9ir2@loopback;
Synonym created.
ops$tkyte@ORA9IR2> select sy_test from dual;
SY_TEST
----------
55
ops$tkyte@ORA9IR2>
Please contact support for this one.
Incorrect Results on DB Link Query
April 1, 2004 - 12pm Central time zone
Reviewer: A reader from NJ
Hi Tom,
I was wondering whether you could explain this behavior to us. We already have a correct, working
query, but took us quite sometime figuring out what was wrong with the initial query in the first
place. We know we should always compare strings to strings, numbers to numbers, but we were
wondering why the first query below was not returning any results, whereas a similar query with
more filters was returning some, but incorrect results:
-----------------------------------
-- w/base filter
-----------------------------------
-----------------------------------
-- Inner query produces MORE results
-----------------------------------
11:54:49 SQL>
11:54:49 SQL> /* Formatted on 2004/04/01 11:01 (Formatter Plus v4.8.0) */
11:54:49 SQL> SELECT ftsroamid
11:54:49 2 FROM getstatus
11:54:49 3 WHERE ordernum LIKE 'FTSSV20040401%'
11:54:49 4 ;
FTSROAMID
----------------
2004040100321000
2004040100321000
2004040100321000
2004040100322000
2004040100323000
2004040100324000
2004040100324000
2004040100325000
2004040100326000
2004040100327000
2004040100328000
2004040100329000
2004040100330000
2004040100331000
2004040100332000
2004040100333000
2004040100334000
2004040100335000
2004040100336000
2004040100337000
2004040100338000
2004040100339000
2004040100340000
2004040100341000
2004040100342000
2004040100343000
2004040100344000
2004040100345000
2004040100346000
29 rows selected.
Elapsed: 00:00:00.00
-----------------------------------
-- but final query DOES NOT RETURN ANY!
-----------------------------------
11:54:49 SQL>
11:54:49 SQL> SELECT TO_CHAR (time_key_stamp, '0000000000000000') time_key_stamp
11:54:49 2 FROM fts_address_info@lk_roamprod
11:54:49 3 WHERE time_key_stamp IN
11:54:49 4 (
11:54:49 5 SELECT ftsroamid
11:54:49 6 FROM getstatus
11:54:49 7 WHERE ordernum LIKE 'FTSSV20040401%'
11:54:49 8 )
11:54:49 9 ;
no rows selected
Elapsed: 00:00:03.02
-----------------------------------
-- w/additional filter
-----------------------------------
-----------------------------------
-- Inner query produces LESS results
-----------------------------------
11:54:52 SQL>
11:54:52 SQL> SELECT ftsroamid
11:54:52 2 FROM getstatus
11:54:52 3 WHERE ordernum LIKE 'FTSSV20040401%'
11:54:52 4 AND getstatus = 'R'
11:54:52 5 ;
FTSROAMID
----------------
2004040100321000
2004040100321000
2004040100321000
2004040100322000
2004040100323000
2004040100324000
2004040100324000
7 rows selected.
Elapsed: 00:00:00.00
-----------------------------------
-- but final query RETURNS SOME, BUT THE RESULTS ARE INCORRECT!
-----------------------------------
11:54:52 SQL>
11:54:52 SQL> SELECT TO_CHAR (time_key_stamp, '0000000000000000') time_key_stamp
11:54:52 2 FROM fts_address_info@lk_roamprod
11:54:52 3 WHERE time_key_stamp IN
11:54:52 4 (
11:54:52 5 SELECT ftsroamid
11:54:52 6 FROM getstatus
11:54:52 7 WHERE ordernum LIKE 'FTSSV20040401%'
11:54:52 8 AND getstatus = 'R'
11:54:52 9 )
11:54:52 10 ;
TIME_KEY_STAMP
----------------
2004040100324000
2004040100324000
2004040100324000
2004040100324000
2004040100324000
2004040100324000
2004040100324000
2004040100324000
8 rows selected.
Elapsed: 00:00:01.03
-----------------------------------
-- FINAL QUERY WITH EXPECTED RESULTS:
-----------------------------------
11:54:54 SQL>
11:54:54 SQL>
11:54:54 SQL> SELECT TRIM (TO_CHAR (time_key_stamp, '0000000000000000')) time_key_stamp
11:54:54 2 FROM fts_address_info@lk_roamprod
11:54:54 3 WHERE TRIM (TO_CHAR (time_key_stamp, '0000000000000000')) IN
11:54:54 4 (
11:54:54 5 SELECT ftsroamid
11:54:54 6 FROM getstatus
11:54:54 7 WHERE ordernum LIKE 'FTSSV20040401%'
11:54:54 8 )
11:54:54 9 ;
TIME_KEY_STAMP
----------------
2004040100321000
2004040100321000
2004040100322000
2004040100322000
2004040100323000
2004040100323000
2004040100324000
2004040100324000
8 rows selected.
Elapsed: 00:00:00.05
-----------------------------------
-- we also tried TO_NUMBER/NVL on the inner query, but still failed.
-----------------------------------
11:54:54 SQL>
11:54:54 SQL> SELECT time_key_stamp
11:54:54 2 FROM fts_address_info@lk_roamprod
11:54:54 3 WHERE time_key_stamp IN
11:54:54 4 (
11:54:54 5 SELECT TO_NUMBER(NVL(ftsroamid,0))
11:54:54 6 FROM getstatus
11:54:54 7 WHERE ordernum LIKE 'FTSSV20040401%'
11:54:54 8 )
11:54:54 9 ;
no rows selected
Elapsed: 00:00:06.00
-----------------------------------
-- as well as TO_NUMBER/IS NOT NULL on the inner query, and still failed.
-----------------------------------
11:55:00 SQL>
11:55:00 SQL> SELECT time_key_stamp
11:55:00 2 FROM fts_address_info@lk_roamprod
11:55:00 3 WHERE time_key_stamp IN
11:55:00 4 (
11:55:00 5 SELECT TO_NUMBER(ftsroamid)
11:55:00 6 FROM getstatus
11:55:00 7 WHERE ordernum LIKE 'FTSSV20040401%'
11:55:00 8 AND ftsroamid IS NOT NULL
11:55:00 9 )
11:55:00 10 ;
no rows selected
Here's our environment information:
LOCAL
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
REMOTE
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 Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
Thanks a lot in advance.
Followup April 1, 2004 - 1pm Central time zone:
this indicates "bug" - please contact support:
11:54:49 SQL> SELECT TO_CHAR (time_key_stamp, '0000000000000000')
time_key_stamp
11:54:49 2 FROM fts_address_info@lk_roamprod
11:54:49 3 WHERE time_key_stamp IN
11:54:49 4 (
11:54:49 5 SELECT ftsroamid
11:54:49 6 FROM getstatus
11:54:49 7 WHERE ordernum LIKE 'FTSSV20040401%'
11:54:49 8 )
11:54:49 9 ;
no rows selected
11:54:52 SQL>
11:54:52 SQL> SELECT TO_CHAR (time_key_stamp, '0000000000000000')
time_key_stamp
11:54:52 2 FROM fts_address_info@lk_roamprod
11:54:52 3 WHERE time_key_stamp IN
11:54:52 4 (
11:54:52 5 SELECT ftsroamid
11:54:52 6 FROM getstatus
11:54:52 7 WHERE ordernum LIKE 'FTSSV20040401%'
11:54:52 8 AND getstatus = 'R'
11:54:52 9 )
11:54:52 10 ;
TIME_KEY_STAMP
----------------
2004040100324000
2004040100324000
2004040100324000
2004040100324000
2004040100324000
2004040100324000
2004040100324000
2004040100324000
8 rows selected.
dead process caused by dblink
April 13, 2004 - 12pm Central time zone
Reviewer: John from Canada
Hi Tom,
I have PL/SQL procedure which copies data from remote database. Both databases are 9.0.1. Most of
time the procedure is running fine. However, when the remote database has some unexpected problem
(like shutdown, etc... but not all the shutdown cause the problem) it cause the procedure hang and
do nothing. My procedure looks like below:
Loop
begin
insert into table as select * from table@remote;
sleep 20 seconds;
exception
when others then
record error...
end;
end loop;
Since we cannot control the remote database, we cannot use materialize view with fast refresh. This
is the only approach we can make.
If the process is hang, we can only kill the process and re-start it. Even for killing the process,
the process was marked as killed, not totally killed right away.
The exception statement in the above code does catch some errors but not catch this kind of
errors.Is there any way to let the procedure detect the problem by itself and avoid the killing?
Many thanks.
Followup April 13, 2004 - 6pm Central time zone:
well, most likely this is your tcp-ip settings at the OS level. TCP-IP isn't timing out the dead
connection on the other end -- you'll wait till they do.
Talk to your system admins and see what they can do to help you out....
(why sleep for 20 seconds??)
April 14, 2004 - 12pm Central time zone
Reviewer: John from Canada
Hi Tom,
Thanks for your answer.
Do you mean TCP/IP on remote server should timeout the dead connection? How does the TCP/IP know
whether the oracle connection is dead? Is there any reference link in Oracle Doc? Since I recorded
errors, I did see some messages:
ORA-03113: end-of-file on communication channel
ORA-12535: TNS:operation timed out
Are these messages caused by TCP/IP?
The reason for sleeping 20 seconds is we want almost real-time data, quick response for the query
on our side and less effect on the remote side.
Thanks.
Followup April 14, 2004 - 2pm Central time zone:
tcp/ip on the local machine (the machine that is still alive) is what needs to time out.
that error message is caused by tcp/ip finally timing out the connection -- yes.
How can I access a Procedure throw a database link?
May 5, 2004 - 1pm Central time zone
Reviewer: Ricardo Patrocinio from Porto, Portugal
I tried exec my$procedure@mydblink;
But it didn't work!?
Thank you
Followup May 5, 2004 - 3pm Central time zone:
my car won't start either!?
ops$tkyte@ORA9IR2> exec my$procedure@ora9i;
PL/SQL procedure successfully completed.
care to share the error.
Time out error
June 2, 2004 - 10am Central time zone
Reviewer: Anil Pant
Hi,
As per your previous response to one of the query regarding the TNS:operation timed out, you said
it always takes SERVER tnsnames.ora
I referred to SERVER tnsnames.ora.
Service_Name in SERVER tnsnames.ora is same as name used in USING 'srini' of CREATE Database Link
The SERVER tnsnames.ora is
SRINI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.12.108)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = srini)
)
)
I created a database link
create database link tosrini2
connect to xyz
identified by xyz
using 'srini';
But when I give the select statement,
select * from cbbsp_payee@tosrini2;
I get the error,
ERROR at line 1:
ORA-12535: TNS:operation timed out
What could be the error ?
select * from cbbsp_payee@srini;
Followup June 2, 2004 - 11am Central time zone:
> oerr ora 12535
12535, 00000, "TNS:operation timed out"
// *Cause: The requested connection could not be completed within the timeout
// period specified by the CONNECT_TIMEOUT parameter in listener.ora. This
// error arises from the tnslsnr.
// *Action: Either reconfigure CONNECT_TIMEOUT to be 0, which means
// wait indefinitely, or reconfigure CONNECT_TIMEOUT to be some higher
// value. Or, if the timeout is unacceptably long, turn on tracing
// for further information.
Time out error ...Forgot to mention the version
June 2, 2004 - 10am Central time zone
Reviewer: Anil Pant
Forgot to mention the version. Im using Oracle 9.2 on NT.
Hi,
As per your previous response to one of the query regarding the TNS:operation
timed out, you said it always takes SERVER tnsnames.ora
I referred to SERVER tnsnames.ora.
Service_Name in SERVER tnsnames.ora is same as name used in USING 'srini' of
CREATE Database Link
The SERVER tnsnames.ora is
SRINI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.12.108)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = srini)
)
)
I created a database link
create database link tosrini2
connect to xyz
identified by xyz
using 'srini';
But when I give the select statement,
select * from cbbsp_payee@tosrini2;
I get the error,
ERROR at line 1:
ORA-12535: TNS:operation timed out
What could be the error ?
select * from cbbsp_payee@srini;
Database Link
July 5, 2004 - 2pm Central time zone
Reviewer: A reader
Hi Tom
I am using database links to pull data from production database into a reporting datamart. I am
being told that it is not a good practice to use database links to import data. The explanation is
- Ideally the source system should have control and make data available to other systems in form of
files. Is this right? I have worked on systems which would create snapshots using dblinks. Could
you please highlight the disadvantages of using dblinks.
Thanks
Followup July 5, 2004 - 2pm Central time zone:
Another way to put this is
"the source system should not recognize the investment in software that this company has made. The
source system should have programers just sitting around writing code to specification for anyone
that needs data from us. The source system should use technology the from 1960's (flat files,
sneaker net) in order to exchange information. The source system should lose all capabilities to
audit and secure information. the source system should be a thorn in the side of everyone."
or put shorter
"no, i disagree totally".
Maybe dblinks are not always the correct answer but to say "dump and load to files is the only way"
is somewhat short sighted.
Database links
July 6, 2004 - 4am Central time zone
Reviewer: A reader
Wonderful explanation, Thanks very much.
Dblink and Performance
July 7, 2004 - 2am Central time zone
Reviewer: Arul from India
Hello Sir,
-- I have two databases X and Y. X has empx table and Y has empy table.
-- Want to query empx(with million records) and insert selected (may be few rows) data into empy
table using dblink.
-- Which one is best out of below 2 from performance prospective and Why ?
1. From database X
insert into empy@Y select * from empx where...
OR
2. From database Y
insert into empy select * from empx@X where...
-- What will happen in above case internally ?
In case 1..
-- Will it select all million rows of empx and carry to database Y and filter there and then
insert few selected rows in empy table or else it will select and filter rows here in database X
and only carry few rows for insertion to empy table of Y database ?
-- How to figure out that from explain plan ?
Thanx in Advance...
Followup July 7, 2004 - 8am Central time zone:
I'd probably want to do #1.
the query is in need of the most optimization here, database X is best suited to perform the
optimization.
explain plan shows you the explain plan for distributed as well as single site queries.
Example please
July 8, 2004 - 12am Central time zone
Reviewer: Arul from India
Hello Sir,
-- I would really appreciate if you brief answer for #2 with an example.
Thanxn in Advance.
Followup July 8, 2004 - 8am Central time zone:
you already gave the example for #2? not sure what else you would want there.
you would use explain plan to see "what would happen"
Check the validity of db links..?
July 12, 2004 - 3am Central time zone
Reviewer: Naveen C from Bangalore
How do we check the validity for all the db-links the database..? Is there any automated process..?
Followup July 12, 2004 - 11am Central time zone:
define "validity", what if a server is just down for a moment.
they are all "valid", they might not be pointing to a server that exists -- but hopefully you would
know that? so, just query dba_db_links and look at the tns connect strings and verify they "ok"
dba_db_links
July 12, 2004 - 9pm Central time zone
Reviewer: Naveen C from Bangalore
Thanks.. very much..
Insertion via dblinks fails
July 13, 2004 - 8am Central time zone
Reviewer: Arul from India
Hello Sir,
I have very strange problem as described below.
Below statement runs fine.
SELECT SBCNO ,
SCURRENTBCNO ,
SGENERATIONCODE ,
SHEADBCNO ,
CACTIONFLAG ,
SGROUPID ,
to_date('20040712','yyyymmdd')
FROM test_table A
WHERE SBCNO IN (SELECT D.BATCHCARD_NO
FROM SCORDERD D,
SORDERM M
WHERE M.SO_NO=D.SO_NO
AND M.STATUS='CLOSE'
AND D.BATCHCARD_NO='T3027715.11'
AND M.CLOSE_DATE<=SYSDATE-174)
Optimizer goal: RULE
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
0 NESTED LOOPS
1 VIEW
1 SORT (UNIQUE)
1 NESTED LOOPS
1 TABLE ACCESS (BY ROWID) OF 'SCORDERD'
2 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'SCORDERD_N2' (NON-UNIQUE)
1 TABLE ACCESS (BY ROWID) OF 'SORDERM'
2 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'SORDERM_N1' (NON-UNIQUE)
0 TABLE ACCESS (BY ROWID) OF 'test_table'
1 INDEX (RANGE SCAN) OF 'test_table_N01' (NON-UNIQUE)
But below statement fails with ORA-01652
INSERT INTO test_table@CELL3
SELECT SBCNO ,
SCURRENTBCNO ,
SGENERATIONCODE ,
CACTIONFLAG ,
SGROUPID ,
to_date('20040712','yyyymmdd')
FROM test_table A
WHERE SBCNO IN (SELECT D.BATCHCARD_NO
FROM SORDERM D,
SCORDERD M
WHERE M.SO_NO=D.SO_NO
AND M.STATUS='CLOSE'
AND D.BATCHCARD_NO='T3027715.11'
AND M.CLOSE_DATE<=SYSDATE-174)
Optimizer goal: RULE
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: RULE (REMOTE)
0 MERGE JOIN
0 SORT (JOIN)
0 REMOTE [!]
SELECT "SBCNO","SCURRENTBCNO","SGENERATIONCODE","SHEADBCNO",
"SSTAGE","LMAINQTY","SPROG","STESTTIME","STESTTIMEUNIT",
"STEMP","CHEAD","SMOID","FMAINQTY","SEQPID","SHP_QTY",
"INODE","IHEADNODE","SPARENTBCNO","SAREA","SLOCATION",
"SRECIPEID","SCUSTOMER","SCUSTPRODUCT","STRACKINTIME",
"STRACKOUTTIME","LTRACKINQTY","SSTARTDATETIME","SDATETIME",
"SUSERID","CFLAG","SCOMMENT","CACTIONFLAG","SGROUPID" FROM
"WALTON"."test_table" "A2"
0 SORT (JOIN)
0 VIEW
0 SORT (UNIQUE)
0 MERGE JOIN
0 SORT (JOIN)
0 REMOTE [!]
SELECT "SO_NO","STATUS","CLOSE_DATE" FROM
"WALTON"."SCORDERD" "A3" WHERE "CLOSE_DATE"<=:1-:2
AND "STATUS"='CLOSE'
0 SORT (JOIN)
0 REMOTE [!]
SELECT "SO_NO","BATCHCARD_NO" FROM
"WALTON"."SORDERM" "A4" WHERE "BATCHCARD_NO"=
'T3027715.11'
.. . The query here is Why the explain plan shows Remote and to me it seems that its taking the
entire query to remote site and than executing it. Correct me if I am wrong and What can be done in
this scenario to execute the query locally and why the optimizer is not smart enough. The db is
running RULE based optimizer.
Followup July 13, 2004 - 11am Central time zone:
because you are using the rbo and the rbo is not very smart at all - it just uses hard coded rules.
it does not known anything about the numbers of rows in tables, data distribution nothing.
look up the driving_site hint (in the performance guide), you may be able to push the query over to
the remote site for optimization.
or use the cbo with good statistics in place.
access across DB link
July 19, 2004 - 2pm Central time zone
Reviewer: Rima Parikh from MN USA
Hello :
I'm trying to write a one time load procedure to read data from a remote table, perform some
calculations and store the calculated results in a local table.
The remote table name is dw_mly_funded_loan. It has about 58Million rows with a secondary index on
dw_funded_loan_id and primary index on the joined key (dw_funded_loan_date, dw_funded_loan_id). The
table has information for each loan_id, for multiple dates (1 to about 40) per loan.
create table DW_MLY_FUNDED_LOAN
(
DW_MLY_FUNDED_LOAN_DATE DATE not null,
DW_FUNDED_LOAN_ID NUMBER not null,
DELQ_REPORT_MBA_DAY NUMBER(6),
DELQ_REPORT_OTS_DAY NUMBER(6)
)
alter table DW_MLY_FUNDED_LOAN
add constraint DW_MLY_FUNDED_LOAN_PK primary key (DW_MLY_FUNDED_LOAN_DATE,DW_FUNDED_LOAN_ID);
create index DW_MLY_FUNDED_LOAN_S01 on DW_MLY_FUNDED_LOAN (DW_FUNDED_LOAN_ID);
I'm trying to write a batch procedure to load data for all loans in the dw_mly_funded_loan table in
pl/sql tables ( bulk load of 100 at a time), perform some calculations on each loan with output of
one row per loan which is to be stored in my local database table.
create or replace nbr_ty as table of number;
create synonym DW_MLY_FUNDED_LOAN
for DW.DW_MLY_FUNDED_LOAN@DWSH.RFC.COM;
For loading the data in internal pl/sql tables I use the following procedure :
-- tab_SeqIDs is loaded with loan_ids' (100 at a time) and the following procedure is called in a
loop
PROCEDURE sp_LoadDelinqData(tab_SeqIds IN NBR_TY, d_CutOffDate IN DATE) AS
BEGIN
SELECT D1.DW_FUNDED_LOAN_ID,
D1.DW_MLY_FUNDED_LOAN_DATE,
NVL(D1.DELQ_REPORT_MBA_DAY, 0),
NVL(D1.DELQ_REPORT_OTS_DAY, 0) bulk collect
into tab_DELQ_FUNDED_LOAN_ID,
tab_DELQ_MLY_FUNDED_LOAN_DATE,
tab_DELQ_REPORT_MBA_DAY,
tab_DELQ_REPORT_OTS_DAY
FROM DW_MLY_FUNDED_LOAN D1
WHERE D1.DW_FUNDED_LOAN_ID in ( select * from table(tab_SeqIds) ) and
(d1.DW_MLY_FUNDED_LOAN_DATE between ADD_MONTHS(d_CutOffDate,
-APTYPES_PKG.c_DELINQ_LOOKBACK) and --this is defined as 24
d_CutOffDate)
ORDER BY
D1.DW_FUNDED_LOAN_ID, d1.DW_MLY_FUNDED_LOAN_DATE;
END sp_LoadDelinqData;
I tried to run an explain plan on an equivalent query :
SELECT D1.DW_FUNDED_LOAN_ID,
D1.DW_MLY_FUNDED_LOAN_DATE,
NVL(D1.DELQ_REPORT_MBA_DAY, 0),
NVL(D1.DELQ_REPORT_OTS_DAY, 0)
FROM DW_MLY_FUNDED_LOAN D1
WHERE D1.DW_FUNDED_LOAN_ID in ( select * from table(cast(:tab_SeqIds as nbr_ty)) ) and
(d1.DW_MLY_FUNDED_LOAN_DATE between ADD_MONTHS(sysdate, :b1) and sysdate)
ORDER BY
D1.DW_FUNDED_LOAN_ID, d1.DW_MLY_FUNDED_LOAN_DATE;
SELECT STATEMENT, GOAL = CHOOSE
SORT ORDER BY
FILTER
HASH JOIN
SORT UNIQUE
COLLECTION ITERATOR PICKLER FETCH
REMOTE SELECT
"DW_MLY_FUNDED_LOAN_DATE","DW_FUNDED_LOAN_ID","DELQ_REPORT_MBA_DAY","DELQ_REPORT_OTS_DAY" FROM
"DW"."DW_MLY_FUNDED_LOAN" "D1" WHERE "DW_MLY_FUNDED_LOAN_DATE">=ADD_MONTHS(:1,TO_NUMBER(:2)) AND
"DW_MLY_FUNDED_LOAN_DATE"<=:3
Looks like the optimizer is not able to do the dw_funded_loan_id filtering over at the remote site.
So, it ends up filtering only by date and bringing back most of the table and then filtering by the
loan_ids. I would like it to ideally filter by the loan ids over at the remote site as in :
SELECT D1.DW_FUNDED_LOAN_ID,
D1.DW_MLY_FUNDED_LOAN_DATE,
NVL(D1.DELQ_REPORT_MBA_DAY, 0),
NVL(D1.DELQ_REPORT_OTS_DAY, 0)
FROM DW_MLY_FUNDED_LOAN D1
WHERE D1.DW_FUNDED_LOAN_ID in ( :l1, :l2, :l3, :l4, :l5 ) and
(d1.DW_MLY_FUNDED_LOAN_DATE between ADD_MONTHS(sysdate, :b1) and --this is defined as 24
sysdate)
ORDER BY
D1.DW_FUNDED_LOAN_ID, d1.DW_MLY_FUNDED_LOAN_DATE
SELECT STATEMENT REMOTE, GOAL = CHOOSE 20 1 150
SORT ORDER BY 20 1 150
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID 20 1 147 1 DW_MLY_FUNDED_LOAN DWSH.RFC.COM
INDEX RANGE SCAN 145 3 DW_MLY_FUNDED_LOAN_S01 DWSH.RFC.COM NON-UNIQUE
Any help would be greatly appreciated. I apologize if the information is not in the form you'd want
or is not sufficient. Please let me know and I will try to provide whatever else is needed. I do
not have access to tkprof type information for company policy reasons and just the fact that I
havent fought for it yet.
I have read up as much as I could find on your site about this. Tried the driving_site hint and
even tried giving it an index hint - no effect. I have a feeling the problem has to do with the
dynamic nature of the pl/sql table - tab_SeqIds that can only be determined at run time.
Thanks much for the wonderful help you provide via this site.
Followup July 19, 2004 - 2pm Central time zone:
are you allowed to create a global temporary table on the remote site? if so, I'd put the data
over there and query it up.
Else, since this is a one time process -- use an inlist. you are using 100 elements in that table
-- just code
where loan_id in ( array(1), array(2), array(3), ..... array(100) )
and if you have less than 100 elements in the array, pad it out with NULLs
access across db links
July 19, 2004 - 3pm Central time zone
Reviewer: Rima Parikh from MN USA
Thanks Tom. That was a quick response.
Not sure what you mean by putting data in a global temporary table.
I dont have any other access to the remote site except select. However I could ask for something to
be created for me. Are you saying I do the calculation at the remote site and store the calculated
columns over there as well?
The second suggestion is possible for the one time load. However I have some further questions on
that :
1. Is 100 at a time efficient for 58Million rows? Or would 1000 or 10000 be better? I know you
prefer smaller bulks, but I have in other places done bulk's of 1000 and even 10,000 with much
better performance than 100.
2. Although this is a one time load, I would like to use a similar procedure to do a monthly batch
to load changed/new loans. Also, the calculations are being done for one particular date (sysdate)
which is what the users pick 95% of the time. However, for the remaining 5% of the time, I also
need to do the calculations at run-time accessing the data across a link at run-time - with the
expectation that it will be slow but atleast be some reasonable time.
From one of the suggestions earlier in this chain, I tried the following that had a little better
plan, is this something worth pursuing ?
SELECT D1.DW_FUNDED_LOAN_ID,
D1.DW_MLY_FUNDED_LOAN_DATE,
NVL(D1.DELQ_REPORT_MBA_DAY, 0),
NVL(D1.DELQ_REPORT_OTS_DAY, 0)
FROM DW_MLY_FUNDED_LOAN D1, DW_MLY_FUNDED_LOAN D2
WHERE d1.dw_funded_loan_id=d2.dw_funded_loan_id and
D2.DW_FUNDED_LOAN_ID in (select * from table( cast(:tab_SeqIds as nbr_ty))) and
(d2.DW_MLY_FUNDED_LOAN_DATE between ADD_MONTHS(sysdate, :b1) and sysdate)
ORDER BY
D1.DW_FUNDED_LOAN_ID, d1.DW_MLY_FUNDED_LOAN_DATE
SELECT STATEMENT, GOAL = CHOOSE 3024 42 2492
SORT ORDER BY 3024 42 2492
FILTER
NESTED LOOPS 3024 42 2489
HASH JOIN SEMI 24 1 2360
REMOTE 64655822 2938901 829 DWSH.RFC.COM SELECT "DW_MLY_FUNDED_LOAN_DATE","DW_FUNDED_LOAN_ID"
FROM "DW"."DW_MLY_FUNDED_LOAN" "D2" WHERE "DW_MLY_FUNDED_LOAN_DATE">=ADD_MONTHS(:1,TO_NUMBER(:2))
AND "DW_MLY_FUNDED_LOAN_DATE"<=:3
COLLECTION ITERATOR PICKLER FETCH
REMOTE 1392 29 129 DWSH.RFC.COM SELECT
"DW_MLY_FUNDED_LOAN_DATE","DW_FUNDED_LOAN_ID","DELQ_REPORT_MBA_DAY","DELQ_REPORT_OTS_DAY" FROM
"DW"."DW_MLY_FUNDED_LOAN" "D1" WHERE "DW_FUNDED_LOAN_ID"=:1
Followup July 19, 2004 - 4pm Central time zone:
i'm saying insert:
select * from table( cast(:tab_SeqIds
as nbr_ty))
into the remote table and then query using that remote table instead of the object type.
make it "tunable" if you like -- 100, 500, whatever.
how is that plan 'better'??
access across db link
July 19, 2004 - 4pm Central time zone
Reviewer: rima parikh from mn usa
Thanks Tom. You're right, I wasnt thinking straight - that plan wasnt any better.
I didnt know about global temporary tables, hence the dumb question. Meantime, I read up a bit on
it and since I dont have access to the remote site, here's what I tried :
create global temporary table AP_DELQ_GT --in my local db
(
LOAN_ID NUMBER(10)
)
on commit delete rows;
forall i in 1..tab_SeqIds.count
insert into ap_delq_gt (loan_id) values ( tab_SeqIds(i));
SELECT /*+rule driving_site(d1)*/ D1.DW_FUNDED_LOAN_ID,
D1.DW_MLY_FUNDED_LOAN_DATE,
NVL(D1.DELQ_REPORT_MBA_DAY, 0),
NVL(D1.DELQ_REPORT_OTS_DAY, 0) bulk collect
into tab_DELQ_FUNDED_LOAN_ID,
tab_DELQ_MLY_FUNDED_LOAN_DATE,
tab_DELQ_REPORT_MBA_DAY,
tab_DELQ_REPORT_OTS_DAY
FROM DW_MLY_FUNDED_LOAN D1, ap_delq_gt gt
WHERE gt.LOAN_ID = d1.DW_FUNDED_LOAN_ID and
(d1.DW_MLY_FUNDED_LOAN_DATE between ADD_MONTHS(d_CutOffDate,
-APTYPES_PKG.c_DELINQ_LOOKBACK) and
d_CutOffDate)
ORDER BY
D1.DW_FUNDED_LOAN_ID, d1.DW_MLY_FUNDED_LOAN_DATE;
that gave the following plan and seems to be working, I'm running it right now :
SELECT STATEMENT REMOTE, GOAL = HINT: RULE
SORT ORDER BY
TABLE ACCESS BY INDEX ROWID DW_MLY_FUNDED_LOAN DWSH.RFC.COM
NESTED LOOPS
REMOTE ! SELECT "LOAN_ID" FROM "AP_DELQ_GT" "A1"
INDEX RANGE SCAN DW_MLY_FUNDED_LOAN_S01 DWSH.RFC.COM NON-UNIQUE
I dont like the use of the rule hint to get it to use the index. I know this is a very generic
question but, If I do get the temporary table created in the remote db, is it likely to use the
index without having to hint it?
Followup July 19, 2004 - 5pm Central time zone:
what you should try first is:
o use dbms_stats.set_table_stats to set the number of rows to 100 and the number of blocks to one
for this gtt.
o drop the rule hint
o keep the driving site hint
problem is the optimizer will default the number of rows to some very large number, dbms_stats can
be used to work around that.
access across db link
July 19, 2004 - 5pm Central time zone
Reviewer: Rima Parikh from mn usa
Cool! Thanks, that would be perfect.
I dont quite know how to use the dbms_stats, but will read up and try it out.
Thanks again.
Query fast but insert slow
August 16, 2004 - 10am Central time zone
Reviewer: A reader
I have the following fully remote query
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Inst |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | | | | |
| 1 | FILTER | | | | | |
| 2 | NESTED LOOPS OUTER | | | | | |
| 3 | NESTED LOOPS OUTER | | | | | |
| 4 | NESTED LOOPS OUTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| S_EVT_ACT | | | | PP704 |
| 6 | INDEX UNIQUE SCAN | S_EVT_ACT_P1 | | | | PP704 |
| 7 | TABLE ACCESS BY INDEX ROWID| S_ORG_GROUP | | | | PP704 |
| 8 | INDEX UNIQUE SCAN | S_ORG_GROUP_P1 | | | | PP704 |
| 9 | TABLE ACCESS BY INDEX ROWID | S_ASSET | | | | PP704 |
| 10 | INDEX UNIQUE SCAN | S_ASSET_P1 | | | | PP704 |
| 11 | TABLE ACCESS BY INDEX ROWID | S_EVT_ACT_X | | | | PP704 |
| 12 | INDEX RANGE SCAN | S_EVT_ACT_X_U1 | | | | PP704 |
--------------------------------------------------------------------------------------------
Note: fully remote operation, rule based optimization
(Yes, it uses the 6-letter application I know you dont like! :)
This query returns one row, it is very fast (it goes by the Siebel's internal row-id)
But when I do a
create table t as <that query>, the plan changes to
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Inst |IN-OUT|
--------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | | | |
| 1 | LOAD AS SELECT | | | | | | |
|* 2 | FILTER | | | | | | |
| 3 | MERGE JOIN OUTER | | | | | | |
| 4 | SORT JOIN | | | | | | |
| 5 | MERGE JOIN OUTER | | | | | | |
| 6 | SORT JOIN | | | | | | |
| 7 | MERGE JOIN OUTER| | | | | | |
| 8 | SORT JOIN | | | | | | |
| 9 | REMOTE | | | | | SIEBE~ | R->S |
|* 10 | SORT JOIN | | | | | | |
| 11 | REMOTE | | | | | SIEBE~ | R->S |
|* 12 | SORT JOIN | | | | | | |
| 13 | REMOTE | | | | | SIEBE~ | R->S |
|* 14 | SORT JOIN | | | | | | |
| 15 | REMOTE | | | | | SIEBE~ | R->S |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(1=2)
10 - access("A"."ROW_ID"="X"."PAR_ROW_ID"(+))
filter("A"."ROW_ID"="X"."PAR_ROW_ID"(+))
12 - access("A"."ORG_GROUP_ID"="G"."ROW_ID"(+))
filter("A"."ORG_GROUP_ID"="G"."ROW_ID"(+))
14 - access("A"."ASSET_ID"="B"."ROW_ID"(+))
filter("A"."ASSET_ID"="B"."ROW_ID"(+))
Note: rule based optimization
This is dog slow!
Why the dramatic change in plans between the two? Because of this, I am forced to resort to
slow-by-slow processing instead of set-based operations.
Help!
Thanks
Followup August 16, 2004 - 7pm Central time zone:
can you two step it.. the CTAS may be precluding the remote optimization as it must do a lot of
local processing to create that table.
ctas where 1=0;
insert into select ...;
Query fast but insert slow
August 16, 2004 - 8pm Central time zone
Reviewer: A reader
I already tried that, the INSERT is slow as well.
I finally did the following:
CTAS where 1=0
insert into
select /*+ driving_site(siebel.s_evt_act) */ ....
This works very fast. I guess the hint pushes the query optimization over to the remote site (which
is still RBO since Siebel doesnt support CBO yet) and then the results of the query are shipped
over the wire to be inserted.
Thanks
Db links!!!!!!!
August 18, 2004 - 11am Central time zone
Reviewer: A reader
Hi Tom,
Can we create a view in a test server which references a table in production.
For example a view like:
select * from table@production.
We tried it but after compilation of the view the link disappears.
Any suggestiosn as to how can this be achieved. I know we can go for MV's or replication of the
above-mentioned table.
But is it feasible using views with db links.
Thanks
Paola Andrea
Followup August 18, 2004 - 11am Central time zone:
"the link disappears"????
what do you mean by that.
yes, you can definitely create a view as select * from t@dblink, do it all of the time, works as
advertised. you'd need to supply an example here showing exactly what you mean.
Example
August 18, 2004 - 12pm Central time zone
Reviewer: A reader
Sorry Tom for putting and incomplete question.
The view is created as:
select * from tablename@production
But after it is compiled it changes to:
select * from tablename
and hence references the local table.
Thanks again
Followup August 18, 2004 - 12pm Central time zone:
nope, sorry - doesn't work that way.
cut and paste the entire example, just like this:
ops$tkyte@ORA9IR2> create database link loopback@ora9ir2
2 using 'ora9ir2';
Database link created.
ops$tkyte@ORA9IR2> create view v as select * from dual@loopback@ora9ir2;
View created.
ops$tkyte@ORA9IR2> select text from user_views;
TEXT
-------------------------------------------------------------------------------
select "DUMMY" from dual@loopback@ora9ir2
prove to me what you say is happening. show me step by step by step how to "reproduce"
Accessing without db links
September 2, 2004 - 11am Central time zone
Reviewer: oracleo from IN
Hi Tom,
There was a brief discussion at our place during which a guy suggested that we can access remote
database tables without using DB Links !!
Is it possible.
Please suggest
Thanks
Followup September 2, 2004 - 11am Central time zone:
not using "SQL" it isn't.
i could write a java stored procedure that used jdbc to open a new connection and ran a query...
i could use utl_http to run a url on a web server that connects to another database and get
information back....
there are thousands of ways perhaps, but none of the are "SQL"
so, you'd have to be more specific.
Database link problem
September 17, 2004 - 5pm Central time zone
Reviewer: venkat from NY,USA
I have below procedure in Schema A.
Process_id column of process_info is a primarykey.
create or replace procedure test_link(o_ref OUT sys_refcursor) is
begin
OPEN o_ref FOR
'Select x.process_id FROM process_info@tlink x,
pa_Exceptions e WHERE x.process_id=e.process_id';
end test_link;
then i give
grant execute on test_link to b;
In Schema B:
i create a snyonym for test_link as
create synonym test_link for a.test_link;
then when i execute the procedure in schema b, and trying to get data from the ref cursor , its
giving "connection description for the remote database found".
SQL> variable y refcursor
SQL> exec test_link(:y);
PL/SQL procedure successfully completed.
SQL> print y
ERROR:
ORA-02019: connection description for remote database not found
no rows selected
SQL>
whereas if the join in the procedure is changed its able to fetch the records .
Can you please clarify why its happening like that.
Followup September 17, 2004 - 8pm Central time zone:
changed how?
database link
September 18, 2004 - 10am Central time zone
Reviewer: VenkatRamanan from NY,USA
i changed the procedure using the detail table of "process_info" and its working from schema B.
create or replace procedure test_link(o_ref OUT sys_refcursor) is
begin
OPEN o_ref FOR
'Select distinct x.process_id FROM process_detail@tlink x,
pa_Exceptions e WHERE x.process_id=e.process_id';
end test_link;
Followup September 18, 2004 - 10am Central time zone:
create database link tlink .......
show me the entire thing from start to finish -- create or replace procedure p1, p2 (one you say
works, the other that doesn't), grant, connect as the other user, run them
CUT AND PASTE
NO EDITS ALLOWED...
September 20, 2004 - 10am Central time zone
Reviewer: VenkatRamanan from NY,USA
As asekd , i am sending you the full information tom.
In Schema ITIT
--------------
SQL> DESC PROCESS_INFO
Name Type Nullable Default Comments
------------------- ------------ -------- ------- --------
PROCESS_ID NUMBER
NT_LOGIN VARCHAR2(20) Y
APP_ID VARCHAR2(10) Y
PROCESS_TYPE VARCHAR2(30) Y
PROCESS_STATUS CHAR(1) Y
PROCESS_DESCRIPTION XMLTYPE Y
PROCESS_START_DATE DATE Y
PROCESS_END_DATE DATE Y
SQL> desc process_Detail
Name Type Nullable Default Comments
---------------- --------- -------- ------- --------
PROCESS_ID NUMBER Y
PROCESS_STEP NUMBER(2) Y
STEP_DESCRIPTION XMLTYPE Y
STEP_STATUS CHAR(1) Y
STEP_START_TIME DATE Y
STEP_END_TIME DATE Y
In Schema ITITDM
----------------
create database link sny.WORLD
connect to ITIT identified by ITIT
using 'sny.world';
SQL> desc pa_exceptions
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
ID_NO NUMBER(10) Y
PROJ_ID VARCHAR2(10) Y
RATE_CODE VARCHAR2(2) Y
EMP_ID VARCHAR2(10) Y
EMP_NAME VARCHAR2(40) Y
HRS_WORKED NUMBER(10,3) Y
PERSON_DAYS NUMBER(10,3) Y
WEEK_END_DATE DATE Y
PROCESS_ID NUMBER(10) Y
PERSON_TYPE CHAR(1) Y 'E'
EXCEPTION_NOTE VARCHAR2(500) Y
SQL> create or replace procedure test_link(o_ref OUT sys_refcursor) is
2 begin
3 OPEN o_ref FOR
4 'Select x.process_id FROM process_detail@sny.world x,
5 pa_Exceptions e WHERE x.process_id=e.process_id';
6 end test_link;
7 /
Procedure created.
SQL> VARIABLE Y REFCURSOR
SQL> EXEC TEST_LINK(:Y);
PL/SQL procedure successfully completed.
SQL> PRINT Y
PROCESS_ID
----------
43
I now grant the execute privilege to the user ITITDM_USR through the role ITITDM_EXEC_ROLE. This
role is already given to the ITITDM user.
SQL> Grant execute on test_link to ITITDM_EXEC_ROLE;
Grant succeeded.
IN ITITDM_USR
-------------
SQL> conn ititdm_usr/ititdm_usr@dnyitit2.world
Connected.
SQL> create synonym TEST_LINK
2 for ITITDM.TEST_LINK;
Synonym created
SQL> VARIABLE Y REFCURSOR
SQL> EXEC TEST_LINK(:Y);
SQL> print y
ERROR:
ORA-02019: connection description for remote database not found
no rows selected
SQL>
Sorry tom.. When i did with the detail table also , i am getting the error. Can you please clarify
why its happening like that.
Followup September 20, 2004 - 11am Central time zone:
and how did the other guy connect -- this is why I want a *cut and paste* from start to finish,
without edits.
did the other guy connect the same.
do this:
sql> connect as owner of procedure
sql> show procedure working
sql> connect as other guy
sql> show procedure not working
cut and paste the entire thing
How is the performance of DB links
October 14, 2004 - 5pm Central time zone
Reviewer: Raju
On a DW project that I am working on the source systems send data and sometimes it takes them more
than 10 hours to send 10K records. The reason given is that we join two databases - one in US and
the other in Europe. Both the database tables have similar number of records - one in Europe has
20K records and the other in US has 30K records. What the query is doing (as I am told) is picking
a Purchase order number from the database table in Europe and then picking up the associated
attribute from the database table in USA.
They are not ready to provide the trace/Explain plan and hence I cannot forward that to you. They
tell me that if both the databases were in th USA it would be faster. I do not agree but need an
experts opinion.
My questions are:
Are database links evil? Performance wise.
Should we create local views(materialized) and then use that instead of a remote table.
Is there a way that the query be written such that instead of getting one purchase order from
database table in Europe and then picking up the associated attribute from the database table in
USA, it does it more smartly like in bulk?
How to measure the performance? TKPROF?
I am not a database person so please help.
Thanks
Raju
Followup October 14, 2004 - 7pm Central time zone:
dblinks are neither evil, nor pure good.
they are -- a tool.
so, copy the UK table to the US over the dblink and see how that performs.
Just to clarify
October 14, 2004 - 8pm Central time zone
Reviewer: Raju
Tom,
Just wanted to know that you want me to run a command like
create table <in US database>
AS select * from <table in UK database>@dblink
and then run the current query without the database.
Do you think the WHERE clause in the current query is killing the performance?
Thanks
Raju
Followup October 15, 2004 - 10am Central time zone:
create a global temporary table, insert into it as select * from the other table, join to that.
If that is significantly "faster", perhaps that is the option you want to use.
if it is not significantly faster -- then well, it is taking a long time to get your data over your
link.
TEST CONNECTION
November 2, 2004 - 6pm Central time zone
Reviewer: Neeraj Nagpal from Los Angeles, CA
Tom,
How can I check the validity of a Oracle UID/PWD. This is just to check the validity only and not
to actually connect using the passed UID/PWD. This validity check needs to be called from the
PL/SQL, I have been thinking of using the datbase links to do this, but I don't know exactly how to
do it. or is there a function available to do this ?
Thanks Always for your Help.
Neeraj
Followup November 3, 2004 - 6am Central time zone:
search for
check_password
connecting from 9i to 7.3.4
November 5, 2004 - 2pm Central time zone
Reviewer: Eugene
Hi Tom,
I am trying to retrieve some data into 9i from 7.3 running the following query (that fails)
SELECT MAX(seq)+1 INTO sbatchid FROM intlog@LINK_TO_7X.US.UB.COM;
I contacted my DBA and she told me: There is no workaround as this is an unsupported combination.
Is there anything I can do to resolve it?
Thanks,
Followup November 5, 2004 - 5pm Central time zone:
9ir2 is explicity "not working against v7.3". It totally doesn't work pretty much. Many issues.
Typcast variables across the DB Link.
November 29, 2004 - 11am Central time zone
Reviewer: MSU from India
Dear Tom,
Assume the following situation.
I have a Database say DB_1 and I have another Database called DB_2.
I create a link for DB_1 and in DB_2 I run a script.
In the the declarative section of the script I declare as follows
Table_Name.<COLUMN NAME>%TYPE (Table_Name is a table in DB_1)
I wish to know whether typcast of variables across the DB Link is possible or not as mentioned
above.
Followup November 29, 2004 - 3pm Central time zone:
ops$tkyte@ORA9IR2> declare
2 l_x t.x@ora9ir2%type;
3 begin
4 null;
5 end;
6 /
PL/SQL procedure successfully completed.
I prefer to use VIEWS of T@ora9ir2 myself -- so my stored code is dependent on the view and when it
recompiles it doesn't need to access the dblink just to do that. It uses the dictionary
information about the view to recompile (makes recompilation lots faster)
re:connecting from 9i to 7.3.4
November 29, 2004 - 3pm Central time zone
Reviewer: Jeff from Oak Creek, WI USA
If you're REALLY desperate to link a 9ir2 database with a 7.3 database, if you have any 8i
databases lying around you could try to "hop" through an intermediate. 9i will talk to 8i, and 8i
will talk to 7.3. I'm certain performance would be "suboptimal" (to say the least!), but you could
probably make it work.
Db links from 9 to 7
November 29, 2004 - 4pm Central time zone
Reviewer: veera from rockville, MD
We did exactly the same. We created a synonym using a DB link from 9 to 8 (staging DB) and another
synonym (with the same name) from 8 to 7. It works fine most of the cases except in case of insert
into select * which gives Oracle error ORA-00600: internal error code, arguments: [qctstc2o1], [1],
[0], [1], [1], [30], [30], []. We did work around this by splitting.
use synonym for db link.
November 29, 2004 - 5pm Central time zone
Reviewer: Sean from NJ, USA
Hi Tom,
You mentioned using view instead of using db link in the code. We actually use synonym instead of
using db link directly. Which one is better in terms of performance and compilation. (Oracle
9204)
Thanks so much for your help.
Followup November 29, 2004 - 7pm Central time zone:
views
when you
create view t as select * from t@remote;
you populate your local data dictionary -- so a procedure like:
create procedure p
as
begin
for x in ( select * from t ) loop...
doesn't need to touch the database link to compile or anything. However, if you
create synonym t for t@remote;
it will have to open the database link just to compile. I like the views.
Performance over db links
December 9, 2004 - 2pm Central time zone
Reviewer: A Reader from BG
Tom,
Assuming i access my PROD database from a STAGING area over a db link - would that in any way
affect the functioning of my PROD database?
Thanks,
Followup December 9, 2004 - 3pm Central time zone:
not anymore than if you just logged into prod directly and did stuff.
ORA-00904 - view over db link
December 29, 2004 - 7am Central time zone
Reviewer: Thiyag from Chennai, India
Hi Tom,
I have a view (the exact SQL being a lengthy one) with db link retriving around 30 columns. When I
select only from this view it works fine and gives me the expected results, but when I try to join
this view with an existing table it pops up the error message invalid identifier
"ORA-00904:A3.col_30". I removed the rest 29 columns from the view and put in only the col_30 and
it works fine when I join with the table. (I was trying to simulate this error with emp and dept
tables so I can give you the cut paste code to reproduce but it works fine with those tables.
Trying to figure out some other way so that I can give you the cut paste code). Appreciate if you
could provide me with pointers or work around to get rid of this error.
Followup December 29, 2004 - 10am Central time zone:
You'll need to work with support on this one -- they can help you set some trace events to collect
more information.
Insert not using Driving_Site
December 31, 2004 - 12pm Central time zone
Reviewer: Jasbir Kular from Toronto, Canada
I have a query that has 3 remote tables and 2 local tables. I use the driving_site hint to push
the query to the remote database. It works great when the query runs by itself but when I add an
insert to the query (INSERT INTO ... SELECT FROM <remote tables>) the plan shows that the query is
not executed remotely. Why does this happen?
Here is the plan for the query without the insert:
SELECT STATEMENT REMOTE Optimizer Mode=CHOOSE 16 33188
NESTED LOOPS 16 3 K 33188
NESTED LOOPS 16 2 K 33156
NESTED LOOPS 1 110 5
NESTED LOOPS 1 96 3
TABLE ACCESS FULL GL.GL_SETS_OF_BOOKS 1 47 2 AFRG01D2.US.ORACLE.COM
REMOTE 1 49 1 ! SERIAL
REMOTE 4 56 2 ! SERIAL
TABLE ACCESS BY INDEX ROWID GL.GL_BALANCES 469 34 K 33151
AFRG01D2.US.ORACLE.COM
INDEX RANGE SCAN GL.GL_BALANCES_N2 297 K 1233 AFRG01D2.US.ORACLE.COM
TABLE ACCESS BY INDEX ROWID GL.GL_CODE_COMBINATIONS 1 66 2
AFRG01D2.US.ORACLE.COM
INDEX UNIQUE SCAN GL.GL_CODE_COMBINATIONS_U1 1 1 AFRG01D2.US.ORACLE.COM
Here is the plan for the query with the insert:
INSERT STATEMENT Optimizer Mode=CHOOSE 69 25893
LOAD AS SELECT
NESTED LOOPS 69 19 K 25893
NESTED LOOPS 69 12 K 25755
NESTED LOOPS 1 114 5
MERGE JOIN CARTESIAN 1 100 3
TABLE ACCESS BY INDEX ROWID XXTEMP.SYNC_CONTROL_T 1 49 1
INDEX RANGE SCAN XXTEMP.SOB_CONTROL_PK 1 2
BUFFER SORT 1 51 2
REMOTE 1 51 2 APPS.CLARICA.COM SERIAL
TABLE ACCESS FULL XXTEMP.SYNC_BALANCE_TYPE_RULES_T 4 56 2
REMOTE 2 K 151 K 25750 APPS.CLARICA.COM SERIAL
REMOTE 1 102 2 APPS.CLARICA.COM SERIAL
Thanks for your help.
Followup December 31, 2004 - 12pm Central time zone:
the insert will make this a local operation -- if you create a remote view that joins the tables
over there -- then the view will have to be optimized at the remote site and your query becomes:
insert into local-table select * from view_defined@remote_site;
Insert not using Driving_Site hint
December 31, 2004 - 12pm Central time zone
Reviewer: Jasbir Kular from Toronto, Ontario
Sorry, I forgot to add in that I am using version 9.2.0.5.
Thanks.
DBLINK from 7.3 to 9i
January 11, 2005 - 6am Central time zone
Reviewer: Jignesh from London
I have got Oracle Datawarehouse Builder 9i and I want to fetech the data from 7.3 (rightnow can not
upgrade to latest version due to some limitations).
I am trying to create dblink between two databases.. but as per my understanding I can not access
7.3 database using dblink in 9i.. is that right? what is the otherwayround for that?
Followup January 11, 2005 - 10am Central time zone:
9ir2 and above explicitly do not support connections to or from 7.3.
the only thing i've heard of is to use a man in the middle, put an 8i instance between you and 7.3.
Not sure how it would perform personally.
Just thought of something else -- haven't tried it, but hey:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206#18830681837358
if you can dig up a 7.3 odbc driver, you might be able to do that.
Interesting
January 13, 2005 - 9am Central time zone
Reviewer: jignesh from London
Your answer is really interesting... I need to convice my seniors for this. They are more
interested in linking 9i to 7.3 through 8i. Would definitely update this if connectivity through
DSN successful.
ora-12640
January 18, 2005 - 11am Central time zone
Reviewer: Nishant from india
Sir,
i have created the db link with
SQL>CREATE DATABASE LINK TOCMSS.HFCLCONNECT.COM CONNECT TO CALLCENTER IDENTIFIED BY <PWD> USING
'cmss.hfclconnect.com';
created successfully
SQL>select * from dual@tocmss;
ORA-12535: Operation Timed Out
i changed the
SQLNET.AUTHENTICATION_SERVICES= (NTS) to NONE
SQL>select * from dual@tocmss;
ERROR at line 1:
ORA-12640: Authentication adapter initialization failed
searched a lot but unable to find solid resolution
please suggest .. how to resolve it..
Thanks in advance
Followup January 18, 2005 - 12pm Central time zone:
comment out the authentication services.
ora-12640
January 19, 2005 - 5am Central time zone
Reviewer: nishant
Sir,
sorry cann't get you . can you please eleborate
Followup January 19, 2005 - 10am Central time zone:
#SQLNET.AUTHENTICATION_SERVICES= (NTS) to NONE
^^
comment it out
albert
January 19, 2005 - 10pm Central time zone
Reviewer: albert from china
Hi tom
you say you prefer to plsql rather than pro*C,
But I have a case in our Data Warehouse system as following:
1/ a source table "sales_source" with 30+ million records :
column
--------
prod_id
cust_id
date_id
sales_amount
2/ and two Dimension tables
2.1/ the "Customer" table with 2+ million records:
column
---------------------
cust_Surrogate_key
cust_id
cust_name
cust_age
2.2/ the "product" table with 2000 records:
column
--------------------
prod_Surrogate_key
prod_id
prod_name
prod_catalog
3/ the target fact table "cube_sales" is
column
--------------
cust_Surrogate_key
prod_Surrogate_key
date_id
sales_amount
We want to transfer sales data from the source table to the target one, meanwhile replacing the
cust_id with a Surrogate key such that we could resolve the "Slowly Changing Dimensions" problem.
Would you please show me a simple and effective PL/SQL solution to achive the that ? I wonder if
PL/SQL can be enough powerful to do faster lookup then pro*C ?
best regard
Followup January 20, 2005 - 10am Central time zone:
take your procedural c code and do the same thing in plsql. they are just procedural languages?
I would be tempted to not look for a PLSQL solution, but rather a SQL solution -- but that is up to
you. Use bulk collects and forall i processing where in c you would have used host arrays....
Timeout "timer" reset
February 10, 2005 - 2pm Central time zone
Reviewer: A reader from Florida
Tom,
I have a stored procedure that inserts data in a table selecting from a remote table using a cursor
loop. I modified the procedure to eliminate the cursor and just issue a single insert/select. When
I run the procedure it fails with an ORA-03113 end-of-file communication channel error.
The single insert/select takes about 20 minutes to run. The CONNECT_TIMEOUT parameter was set to 5
minutes and was changed to 30 minutes.
The old version of the stored procedure that used a cursor never ran in less than 5 minutes (ran in
about 25 minutes). My question is, how come we didn't hit the timeout error before? Did the
timeout "timer" get reset on every fetch?
Followup February 11, 2005 - 7pm Central time zone:
please contact support for 3113, 600 errors.
DB LINK SESSION
February 11, 2005 - 3pm Central time zone
Reviewer: Push.. from NJ, USA
Tom,
Is there a way to find out if a session in an instance
has opened a db link session in the remote instance.
For example,
In instance DB1, session 1 is querying a table in remote
instance DB2 through a database link. So there is a new
session created in DB2 on befalf this session 1 in DB1.
When there are multiple sessions created in DB2 through
the database link from DB1, Is there a way to map the sessions in DB1 to sessions in DB2.
SID 11 in DB2 is created by SID#1 in DB1
SID 12 in DB2 created by by SID#2 in DB1.
The OSUSER column in V$SESSION will be same in both the
instances for those corresponding sessions.
Is there anything else ?
Thanks
Push..
Followup February 12, 2005 - 12pm Central time zone:
not as far as I know, they are just sessions -- they could have been logged in via sqlplus (not as
a dblink) for all the remote database cares.
sid#1 in db1 would have to pass that sort of information over (and they can, by calling
dbms_application_info@remote_site and using the set_client_info routine.
February 16, 2005 - 12pm Central time zone
Reviewer: A reader
Hi Tom,
Consider my situation and advise accordingly.
I have a database db1 on machine M1 and database db2 on machine M2. Both these machines are in a
remote site and I cannot see the desktop of these 2 machines. But I can remotely see another
machine M3 which is on the same network as machines M1 and M2. Now assuming I know the tnsnames.ora
entry in M1 to connect to M2, can I login to
M3 and create a link from M1 to M2 or shud I execute the 'create database link' statement only
directly on M1. Pls help me on this.
Thanks.
Followup February 16, 2005 - 2pm Central time zone:
confusion on my part. when you are "on m3" what exactly would you be typing "create database link"
into?
You can log into m3, and assuming there is some client software -- say sqlplus installed on m3, you
can use that to connect to either of db1 and db2 and create database links to any other machine you
like.
Some more clarifications needed
February 17, 2005 - 1am Central time zone
Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka
Hi Tom,
I'm using 9iR2.
My database is PANDA.
I haven't created a loopback link "PANDA". (actually I can't.)
But still my select; "select * from global_name@panda;" works.
I couldn't find any explanations on this syntax on SQL reference doc. also.
Can please explain what happens here.
Thanks in advance.
Followup February 17, 2005 - 8am Central time zone:
it is just implied. It is there. You actually don't need database links at all if global names
are database names and everything uses "defaults", they are implicit if you have an account on the
other database (in your case, the same database in this instance)
Difference between explicit and implicit links
February 17, 2005 - 10am Central time zone
Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka
Hi Tom,
Thanks.
And what is the difference between explicit and implicit links ? Through implicit links we can
access only our own schema ?
They can not be make public ?
Thanks in advance.
Followup February 17, 2005 - 1pm Central time zone:
they are just like an explicit link created without a user/password (they are not a proxy link).
they use your identity.
Little more clarifications needed
February 17, 2005 - 11pm Central time zone
Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka
Hi Tom,
You say;
"You actually don't need database links at all
if global names are database names and everything uses "defaults", they are
implicit if you have an account on the other database (in your case, the same
database in this instance)".
What are the thing that should use defaults ? Listener name and port ?
And I have 2 databases "PANDA" (on HP-UX) and "TUSKER" (on RedHat Linux). In both databases there
is a user called "SAN" with same password.
Then I should be able to query "select * from tab@tusker" from SAN@PANDA without creating a
database link to TUSKER no ?
But it give ORA-02019. I also have add an entry in tnsnames.ora on PANDA server.
And can tell where this is explained in the documentation also ?
Thanks in advance.
Followup February 18, 2005 - 7am Central time zone:
I never use them, I explicitly create dblinks, that would be my suggestion.
create the links.
Q on db links
February 18, 2005 - 12pm Central time zone
Reviewer: A reader
In any of the earlier versions of Oracle, did Oracle ever execute a query that involves a table at
remote database
by making a local copy into the local database and
then executing it? I suspect not but wanted to ask if
there was anything similar to this in an earlier version
of Oracle..
Followup February 18, 2005 - 2pm Central time zone:
we do that today sometimes in effect. we might have to ship an entire table or result of a query
from server A to server B in order to finish the query on server B. We'd use temp space as needed
to stash the data. That has always been true.
Even today we'll create temporary tables if needed to process a query -- distributed or not.
thank you
February 18, 2005 - 2pm Central time zone
Reviewer: A reader
"Even today we'll create temporary tables if needed to process a query --
distributed or not."
Do you mean "global temporary tables"?
Followup February 18, 2005 - 3pm Central time zone:
no, they would be very similar but you won't necessarily seem them in the dictionary (you will for
somethings but not for a query)
February 19, 2005 - 4am Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
>Even today we'll create temporary tables if needed to process a query
Is it true that (temporary) indexes may get created on them "on the fly" also, or is that a myth ?
And this kind of temp tables are the result of the VIEW row source operation, correct ?
TIA (good old USENET jargoon, not IM speak - the "Fair Warning" doesn't apply ;)
Followup February 19, 2005 - 8am Central time zone:
well, they might be "hashed" or "sorted" and we generate bitmap indexes on the fly even for 'real'
tables.
not only the "view" row source operation. see http://www.dbazine.com/jlewis6.shtml for an example, look for transformation on that page.
(for the longest time, I thought TIA -- thanks in advance -- was like "ciao" or "ta-ta for now" or
"goodbye" in some other language :)
Trigger bidirectional
February 23, 2005 - 2pm Central time zone
Reviewer: David Pujol from Barcelona (Catalonia)
Hi Tom,
I have 2 databases (A and B). Database A has a table A1 and database B has the same table A1. I'd
like update these tables online (insert in database A must propagated to database B and the same
with updates and deletes). This propagation must be bidirectional (database A --> database B and
database B --> database A)
Can you show me ...., how can I implement this approach?, .. triggers? ...
A lot of thanks
Atentament
David Pujol
Followup February 24, 2005 - 5am Central time zone:
oh my, why have two databases at all here?????
they both must be up
they both must be available
this is an utterly bad idea.... I won't go into how to do it, but you can read the replication
guides, they talk of syncronous replication -- but it is the worst thing you could do.
seems like you really want a single database, having two would be a really bad idea.
Trigger bidirectional
February 24, 2005 - 12pm Central time zone
Reviewer: David Pujol from Barcelona (Catalonia)
Hi Tom,
I know that is a bad idea ..., we have performance problems caused for dblink waits. In future,
there will be a unique database, but now is not possible :-(.
I just want duplicate one table ...
Can you explain me how can I know if session procedence is a dblink?
A lot of thanks again.
Followup February 24, 2005 - 5pm Central time zone:
did you read the above? I pointed you to documentation.
read chapter 1 of the advanced replication guide. I cannot in good faith say much more because you
are doing something i totally do not believe in.
reader
March 15, 2005 - 7am Central time zone
Reviewer: A reader
At a local database, when a database link is used to
access a table from a remote database,
It seems that the SELECT query is seen in the
v$transaction and use rollback segments. However,
the SELECT statement does not show in the
v$sqlarea.sql_text. Is this normal or am I missing
something
Followup March 15, 2005 - 8am Central time zone:
you can see a sql query in v$transaction?
it is in v$sql/sqlarea -- if there is SQL running, it is there.
Reader
March 15, 2005 - 8am Central time zone
Reviewer: A reader
I saw sessions in the v$transaction that was consuming
RBS. Could not find SQL from v$sqlarea mapping
taddr, sql_address etc.
I would like to know if database link transactions,
even if it is a SELECT query, use RBS and consume
RBS
Followup March 15, 2005 - 8am Central time zone:
perhaps that session just wasn't active at the point in time you looked.
Session on remote DB
March 18, 2005 - 11am Central time zone
Reviewer: Naresh from Cpyrus
Hi Tom,
One of the posts above mentions a session created on the remote DB when a remote object is
accessed.
How long does the remote session remain in effect. Is it as long as the local session is active or
is there some timeout for the remote session?
Thanks.
naresh
Followup March 18, 2005 - 11am Central time zone:
until you either
a) close the database link (alter session)
b) exit your session
script for db links
April 5, 2005 - 10am Central time zone
Reviewer: Pravesh Karthik, india
Tom,
Is there a way to get the db link script generated from database for the existing dblinks ? ..
we have 150 database and each would have atleast 5 to 10 db links.I use dbms_metadata for tables or
procedures. can you point to any link or a method how i can go about getting the script generated.
Thanks for your time and consideration,
Pravesh Karthik
Followup April 5, 2005 - 12pm Central time zone:
the view dba_db_links has most of what you need, sys.link$ everything if you need the password.
should be fairly easy to write the sql.
Using insert over DB link from MS Access to Oracle is slow
April 11, 2005 - 10am Central time zone
Reviewer: Gu Wang from Canada
Hi Tom,
A DB link oem_to_access is created, which links to a MS access database. When running
insert into gis_transf.MAGENTA_PI_NAME_MASTER (
ROLL, SEQUENCE, NAME, IDENTIFIER, BIRTH_YEAR, BIRTH_MONTH, OCCUPANCY_STATUS, RESIDENCY_CODE)
select ROLL, SEQUENCE, NAME, IDENTIFIER, BIRTH_YEAR, BIRTH_MONTH, OCCUPANCY_STATUS,
RESIDENCY_CODE
from PI_NAME_MASTER@oem_to_access;
it needs about one hour to finish 185266 rows. My question is how can make the insert faster?
Oracle database is on version 9.2.0.1 on Windows NT SP6. MS access(97) is on the same machine.
Followup April 11, 2005 - 10am Central time zone:
guess I would first turn on the 10046 level 12 trace, and run it with "where rownum <= 1000" just
to see what the major wait events could be.
Gu Wang
April 11, 2005 - 2pm Central time zone
Reviewer: denni50 from na
Make sure you have set up a primary key on the Access table.
the below link should help you with optimization across
db links.
http://www.cs.umb.edu/cs634/ora9idocs/server.920/a96544/trouble.htm#1004640
Insert slow
May 19, 2005 - 2pm Central time zone
Reviewer: A reader
I have a fully remote query
from tab1@dblink,tab2@dblink, ...
All the tables are partitioned tables and the query just goes to 1 partition.
The SELECT returns 10 rows, is very fast.
But when I do
insert into local_table
select ...
it doesnt return even after 10 minutes.
Help? Thanks
Followup May 19, 2005 - 2pm Central time zone:
if you can, create a remote view that joins all of the remote tables and simply select from that
(optimization takes place on remote site)
if not, this would be a candidate for going to a tiny bit of procedural code (rare, but does
happen). fetch rows, insert rows.
automate,
May 20, 2005 - 10am Central time zone
Reviewer: sns from austin,tx
We have 20 oracle instances in our warehouse environment. The management is planning to change the
password of all the schemas in every database once a month.
However, we have db links in each database that points to other databases. In fact we have tens
and hundereds of database links.
If the password is changed, then the db link doesn't work. We have to manually recreate the db
link which is very tedious process.
Is there a way to automate this process or atleast have a stored proc that can take care of
recreating the db links?
Any other ideas please?
thanks,
Followup May 20, 2005 - 6pm Central time zone:
centralize your user accounts -- use an ldap repository and global/enterprise users.
don't use single database accounts, use global accounts.
thanks,
May 22, 2005 - 11am Central time zone
Reviewer: sns from austin,tx
could you please elaborate your explanation?
Is it possible to convert the existing user accounts to global users?
how to create ldap repository?
any drawbacks in using ldap repository?
thanks
Followup May 22, 2005 - 12pm Central time zone:
any drawbacks -- that is a rather "broad" question - unanswerable really....
But you'd want to take a look at the doc's on this (otn has them all) and if you are interested in
a single book -- David Knox has a good one on security all around that covers enterprise users.
http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96582/toc.htm
is a good place to start as well.
procedure compilation with dblink leads to end-of-file communication channel.
May 23, 2005 - 5am Central time zone
Reviewer: sreenivasa rao from INDIA
Dear Sir,
If query a table through dblink like
SQL>select * from user_roles@scmlink;
it's working fine.
And a table in size 1GB called account_master,
SQL> select * from account_master@scmlink;
this is also working .
But a procedure is using the above statement.
if i compile that procedure.then it's giving END-OF-FILE COMMUNICATION channel error.
but,I found one solution, export the target database schema and import with different schema name
(1 or 2 times with different names),and recreate the DBLINK is working.since the target DB size is
2Gb only.
unfortunatly,due to lack of transaction tracking system (save points or rollback), i need to revert
the database with last export dump.At this moment the dblink creation becomes big problem.
what could be the problem?
Followup May 23, 2005 - 10am Central time zone:
please contact support for assistance with an ora-3113
Thanks TOM
May 24, 2005 - 12am Central time zone
Reviewer: sreenivasa rao from INDIA
May 24, 2005 - 2am Central time zone
Reviewer: alt from india
using database link is it possible to connect the same database
ie
i am using databae 'A' and i created the DB Link to the same database,when i trying to acess the
table in this database using DB Link it is showing
0RA-12638 error
it will work?
Followup May 24, 2005 - 7am Central time zone:
depends on how you create the link, but yes you can -- I do it all of the time.
sys.link$
June 14, 2005 - 5am Central time zone
Reviewer: Rajesh from Hyderabad, India
Tom,
Do you see any reason, why Oracle does not hash(or encrypt) the password before storing in
sys.link$ table?
Even if the people who have access to sys.link$ are powerful people, they are powerful only for
that database, not necessarily for the ones which are listed in sys.link$. Right?
I know, personally you are against storing plain passwords in any table. So, thought, I would give
your views on this.
Thanks
Followup June 14, 2005 - 9am Central time zone:
because no data is stored encrypted (prior to 10gr2), and in order to store data encrypted you need
to manage keys and prior to 10gr2 the database didn't have key management built into it.
the password cannot be hashed, that cannot be used to authenticate.
If you have a powerful person in that database, they do not need access to sys.link$ to use the
dblinks - that is, they need not have the passwords to make use of the link. even if the password
were encrypted, it would give that powerful person the same capability - just because of the
existence of the dblink itself.
database link procedures and variables...
June 14, 2005 - 12pm Central time zone
Reviewer: Phil from UK
Hi Tom
Not sure if you have already answered this so sorry if this is the same question in another format:
I have a package with some variables in the spec. I can use the package procedures and functions
remotely but can I access the variables too from a dblink and if not what work-around do you
recommend?
Followup June 14, 2005 - 4pm Central time zone:
yes, you can refer to remote packages. I find it easiest with synonyms
create synonym remote_pkg for pkg@remote;
then you can refer to remote_pkg as if it was a local one.
Oracle Object TYPEs over Database Link
July 4, 2005 - 7am Central time zone
Reviewer: Andriy Terletskyy from Germany
Hi Tom,
how to execute(reference) a Object Type over Database link?
-- Created on 04.07.2005 by TERLETSKYY
declare
-- Local variables here
i WP.ROW_WPBWPHG9@INFO := WP.ROW_WPBWPHG9@INFO(1);
begin
-- Test statements here
i.dbms_output;
end;
/
ERROR:
PLS-00331: illegal reference to WP.ROW_WPBWPHG9@INFO.BERENBERGBANK.DE
Regards
Andriy
database links and updates
July 25, 2005 - 6pm Central time zone
Reviewer: A reader
hi, I would like to know what happens when we update database and over the db link somebody is
reading it.
OS : sun sol 8
db : oracle 8.1.7.4
issue: we have 2 indepandent databases A & B on 2 different sunboxes. both are connected via db
link.
schema B1 in database B updates data based on selected data from database A, shchema A1
some thing like
start time : 00:00
execure immediate
' update B1.T1 p
set x = y
where exists (select 1 from A1.T1@db_link q
where q.c1 = p.c1 )'
;
end time : 00:15
the the problem we are facing is,
what happens if somebody
updates the data in a1.t1 ?
and issues commits
start time : 00:07
update a1.t1 q
set y =z;
commit;
end time : 00:10
q1) will new updates seen by the already running query .
q2.) what will be the sate of already running query as the later tx was commited ?
q3.) will it wait ?
q4) or query will fail ?
q5) anything other idea will also be helpful.
TIA
Followup July 25, 2005 - 7pm Central time zone:
... hi, I would like to know what happens when we update database and over the db
link somebody is reading it.....
same thing that happens when a database link is not involved, no different.
thanks
July 26, 2005 - 5pm Central time zone
Reviewer: A reader
q1) will new updates seen by the already running query .
q2.) what will be the sate of already running query as the later tx was commited
?
q3.) will it wait ?
q4) or query will fail ?
q5) anything other idea will also be helpful.
thanks for the link.
July 27, 2005 - 2pm Central time zone
Reviewer: A reader
thanks for the link tom.
but what I didn't get it is,
how about inserts that are committed during the query reads ?
will new commited inserts be included in the already running query result set ?
thanks,
Followup July 27, 2005 - 3pm Central time zone:
read consistency, most important.
The results of your query (read component) are set as of the time the query begins.
do this.
session 1 session 2
------------------ --------------------
variable x refcursor
exec open :x for select * from t;
delete from t;
commit;
print x
once you open that cursor, the result set is "fixed" as of that point in time. The results are
CONSISTENT.
Same with an update, the READ component of the update is read CONSISTENT (sees data as of a single
point in time)
If you have Expert One on One Oracle - I go over this in great detail
If you get the next edition volume I (coming out in Sept), I think I did it even better (the
explanation and examples) 4 years later.
Or re-read that chapter above, especially:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#17882
Thank you
July 27, 2005 - 3pm Central time zone
Reviewer: A reader
thanks, for showin me "How to Test", this is real good
now I will actually know what I am testing...
thanks
dynamically recreating db links,
August 13, 2005 - 5pm Central time zone
Reviewer: sns from austin,tx
On Linux platform we have a file that stores the name of the database, the schema name and its
password (delimited by ":" )
Every month the passwords across all the databases changes and the configuration management person
updates the file with the new password.
The issue with us is the db links needs to be recreated. A schema of some database may have db
links to all other databases.
In simple words, there are 100's of db links.
We need to change them all right after the file gets updated with the new password.
Given this scenario, what would be your steps to recreate db links? Do you use the combination of
shell script and pl/sql or just pl/sql or even just shell script?
Could you share the script if you have already written one?
Thanks,
Followup August 13, 2005 - 5pm Central time zone:
wow, why bother with passwords if you are going to store them in a file like that?
Sounds like you need a centralized repository, a directory of sorts, a central place to manage and
maintain this information. Perhaps even the introduction of PKI so as to not use passwords at all.
We use LDAP for this, our implementation is OID (Oracle Internet Directory). There are others on
the market as well.
password,
August 14, 2005 - 9am Central time zone
Reviewer: sns from austin,tx
The file is secured and only certain people can view the password. We have a shell script that
takes two parameters: DB name and Schema name and it returns password back.
The shell script checks who has execute privilege to execute the getpassword file.
This idea is perhaps some people who have pretty minimum knowledge on oracle features. Our
warehouse shop is mainly in Teradata and few of them are oracle marts.
Anyway, implementing LDAP is pretty far away for us.
Thanks,
Followup August 14, 2005 - 9am Central time zone:
The file is secured and only certain people can view the password.
uh huh.
Well, no scripts from me. I don't have anything remotely similar.
Performance over dblinks
August 25, 2005 - 12pm Central time zone
Reviewer: Chris from Louisville, KY
Tom,
I am working to get some specifics for you, but in the meantime I thought I'd ask the question
and you may point me in a direction to look. Our production database uses a database link to look
data up on a remote database. In general (probably 99.9% of the time) the query and response comes
back very quickly (0 - 250 milliseconds). However, the other 0.1% of the time it takes 5 - 8
seconds. Does that ring any bells with you. I'm going over to work with the team to get the code
and see if it can be reproduced and will update if I can. Just thought you might have some ideas
or areas I should focus on.
Followup August 25, 2005 - 6pm Central time zone:
My gut says "network burp, long dns lookup" or "remote server really smashed at that time, connect
takes very long"
but, a sql_trace would help if possible (10046 level 12 with waits)
Performance over DB link
August 29, 2005 - 12pm Central time zone
Reviewer: HR
I have the query, all the objects in DB1.
it runs 1 second direct on DB1, and it runs over one minute over the db link on DB2. it always runs
over one minute on DB2. The query only returns 20 records, so i do not think because of data size
returned is the problem.
I tested another query which has very small table in DB1,
and it runs as much as the same time on DB1 or via db link on DB2.
How can i look?
TIA
Followup August 29, 2005 - 1pm Central time zone:
look at the plans, you might use the driving_site hint to have it optimized on db1 when run from
db2
Perf over Db link
August 29, 2005 - 3pm Central time zone
Reviewer: HR
Thanks tom.
the query from the DB2 via db link:
SELECT
DECODE(f.phase_code,'C', 'Completion', 'P', 'Pending', 'R', 'Running') status_code,
f.request_id,
u.user_name||': '||substr(u.description,1, instr(u.description, '(' )-1 ) user_name,
TRUNC(((sysdate - f.actual_start_date)/(1/24))*60) runtime,
DECODE(u.user_name, 'U4IC9GAN', 'Scheduled', 'U4ICAADW', 'Scheduled','SYSADMIN',
'Scheduled',
DECODE(f.phase_code, 'R', TRUNC(((f.actual_start_date - f.REQUEST_DATE)/(1/24))*60),
'P', TRUNC(((sysdate - f.REQUEST_DATE)/(1/24))*60) ) ) waittime,
avg(DECODE(u.user_name, 'U4IC9GAN', 0, 'U4ICAADW', 0, 'SYSADMIN', 0,
DECODE(f.phase_code, 'R', TRUNC(((f.actual_start_date -
f.REQUEST_DATE)/(1/24))*60),
'P', TRUNC(((sysdate - f.REQUEST_DATE)/(1/24))*60) ))) over () avg_wait,
f.actual_start_date asd,
f.REQUEST_DATE reqtime,
DECODE(p.concurrent_program_name, 'ALECDC',
p.concurrent_program_name||'['||f.description||']',
p.concurrent_program_name) cpn,
DECODE(s.ARGUMENT_TEXT , '(None)', 'USACE', null, 'HOSTNATION', s.ARGUMENT_TEXT) parameters
FROM apps.fnd_concurrent_programs@oracle_projects p,
apps.fnd_concurrent_programs_tl@oracle_projects pt,
apps.fnd_concurrent_requests@oracle_projects f,
apps.fnd_user@oracle_projects u,
apps. fnd_conc_req_summary_v@oracle_projects s
WHERE
f.concurrent_program_id = p.concurrent_program_id
and f.phase_code in ('P', 'R')
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND f.requested_by = u.user_id
AND pt.language = USERENV('Lang')
and s.request_id= f.request_id
and DECODE(p.concurrent_program_name, 'ALECDC', p.concurrent_program_name||'['||f.description||']',
p.concurrent_program_name) like 'OP3%'
ORDER BY runtime, waittime desc
/
the plan on DB2:
QUERY_PLAN
-----------------------------------------
WINDOW SORT
HASH JOIN
NESTED LOOPS
HASH JOIN
NESTED LOOPS
REMOTE
FILTER
REMOTE
REMOTE
REMOTE
REMOTE
---
The plan on DB1:
QUERY_PLAN
------------------------------------------------------------
WINDOW SORT
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_REQUESTS
INDEX RANGE SCAN FND_CONCURRENT_REQUESTS_N7
INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_TL_U1
TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_PROGRAMS
INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_U1
TABLE ACCESS BY INDEX ROWID FND_USER
INDEX UNIQUE SCAN FND_USER_U1
TABLE ACCESS BY INDEX ROWID FND_CONCURRENT_REQUESTS
INDEX UNIQUE SCAN FND_CONCURRENT_REQUESTS_U1
INDEX UNIQUE SCAN FND_PRINTER_STYLES_TL_U1
INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_U1
INDEX UNIQUE SCAN FND_CONCURRENT_PROGRAMS_TL_U1
INDEX UNIQUE SCAN FND_USER_U1
looks like the driving_site may not apply here, the query runs and optmized on DB1?
Followup August 30, 2005 - 12am Central time zone:
the driving site hint would let you tell db1 to optimize the query in db2, that was the point.
Using DBLINK between Oracle and MS SQL Server
September 7, 2005 - 2am Central time zone
Reviewer: acaicb from CN
Hi,Tom.
Somebody told me that a connection can be created between Oracle DB and MS SQL Server using DB
LINK.
Is it right?
if it is right,how I use DB LINK to create a connection?
View with CLOB column to hide link?
September 28, 2005 - 9pm Central time zone
Reviewer: Vernon from California, USA
Tom,
You said that you like to use views to conceal the link and provide a local copy of the metadata.
Is there any way to do this when the remote table contains a CLOB column?
I only want to be able to push data to the remote table with a package using insert into x@y (...)
select ... from local_y
If the remote database is unreachable, any attempt to recompile the package body causes my session
to hang until connectivity is returned.
Thanks
Followup September 29, 2005 - 6am Central time zone:
In this case, you can use native dynamic sql to work around this issue. That'll break the
dependency between the procedure and the dblink as well.
In general I'd be opposed to this however, in versions prior to 10g, native dynamic sql causes a
parse per execute, you lose the dependency, it is not as efficient.
A Reader
September 29, 2005 - 5am Central time zone
Reviewer: A from Aus
Hi,
Is there any way/trick to hide password from getting display/stored in user_db_link.I have a
transactional system
and report system(on different box) .Report system should have read only rights on
production(trans) system .I was thinking to create db link ,but the password is a problem(gets
stored in user_db_links) which I don't want.Any way to hide this.
Cheers
Followup September 29, 2005 - 7am Central time zone:
can it be a public database link?
you can put it into another schema and create views there and grant on the views to this first
schema (so someone else owns it)
performance related issue using db links,
October 3, 2005 - 2pm Central time zone
Reviewer: sns from austin,tx
I have a query with joins on two different tables that runs in under 2 minutes. However, when I
run the same query from other database accessing the objects using db link, it is taking almost 15
times.
Here are the details:
SQL> connect user@dbname1
Enter password:
Connected.
SQL> select count(*) from (
2 select distinct d.order_num, d.tie_num, d.sku_num, d.mfg_part_num, d.order_qty, d.item_class,
d.system_class
3 from lata.prod_order_detail_lata_usd d, lata.prod_order_summary_lata_usd s
4 where s.business_unit_id <> 2020
5 and (s.order_date >= sysdate - 8
6 or s.status_date >= sysdate - 8
7 or s.order_status in ('IP', 'PP', 'HL', 'TR'))
8 and s.order_num = d.order_num
9 and d.mfg_part_num is not null
10 )
11 ;
COUNT(*)
----------
425341
Elapsed: 00:01:36.94
Running using a DB_LINK:
connect user@dbname2
Enter password:
Connected.
SQL> select count(*) from (
2 select distinct d.order_num, d.tie_num, d.sku_num, d.mfg_part_num, d.order_qty, d.item_class,
d.system_class
3 from lata.prod_order_detail_lata_usd@dblink.world d,
lata.prod_order_summary_lata_usd@dblink.world s
4 where s.business_unit_id <> 2020
5 and (s.order_date >= sysdate - 8
6 or s.status_date >= sysdate - 8
7 or s.order_status in ('IP', 'PP', 'HL', 'TR'))
8 and s.order_num = d.order_num
9 and d.mfg_part_num is not null);
COUNT(*)
----------
425341
Elapsed: 00:33:53.33
How do you trouble shoot this kind of issue? What are the things you look into for analyzing the
drastic time difference.
I know accessing through db link is slower but I didn't expect this much.
Thanks,
Followup October 3, 2005 - 8pm Central time zone:
first thing, get the plans and compare them...
you are using the CBO with stats and all right?
remote query limitations
October 18, 2005 - 12pm Central time zone
Reviewer: A reader
Hi
I have a select count(*) query which runs very fast in local but slow from a remote server
(database link)
I noticed the local query uses this execution plan
BITMAP CONVERSION* (COUNT)
BITMAP INDEX* (FULL SCAN) OF 'I_SUME_ESTADO
However the remote query runs a full table scan
Is this a database link limitation?
Followup October 18, 2005 - 2pm Central time zone:
could be - but have you tried the driving site hint out?
November 21, 2005 - 2pm Central time zone
Reviewer: A reader
create DB link under other schema name
November 30, 2005 - 5pm Central time zone
Reviewer: A reader from US
Tom,
Is there a way to create a db link under other schema names?
As user "sys", how to create a private db link under schema "hr"? it does not work the same way as
other objects
thanks,
Followup November 30, 2005 - 9pm Central time zone:
not that am am aware of.
can I come up with a way to do it - yes.... it would involve having some DBA account (because we
all know not to use sys for stuff). it would not be "pretty" or "elegant".
of course you can always use "su"
http://asktom.oracle.com/~tkyte/Misc/su.html
create DB link under other schema name - ctd
December 1, 2005 - 4am Central time zone
Reviewer: Serge Shmygelsky from Ukraine
Why can't we use ALTER SESSION SET CURRENT_SCHEMA=<owner>? I made a simple test:
SYS@REX> conn shmyg as sysdba;
Enter password: ********
Connected.
SYS@REX> alter session set current_schema=dealer;
SYS@REX> create table test (f1 number);
SYS@REX> select * from all_tables where table_name = 'TEST';
OWNER TABLE_NAME
------------------------------ ------------------------------
DEALER TEST
SYS@REX> create database link bscs connect to scott identified by tiger using 'test6';
create database link bscs connect to scott identified by tiger using 'test6'
*
ERROR at line 1:
ORA-01031: insufficient privileges
SYS@REX> conn dealer/dealer
Connected.
DEALER@REX> create database link bscs connect to scott identified by tiger using 'test6';
DEALER@REX>
Why can we create a table and not database link?
Followup December 1, 2005 - 12pm Central time zone:
never use sysdba for stuff, never - use it to startup and shutdown. don't use it day to day.
sysdba (sys) is special, magical, different. do not use it. use it to maybe create your first
account and then stop using it.
set current schema does not change anything privilege wise, it simply changes the current default
schema name that would be coded into the sql statement.
select * from t
becomes as if you had typed in:
select * from CURRRENT_SCHEMA.t
if, you could not select * from current_schema.t before the alter, you cannot after, it changes
nothing about the privilege domain.
create DB link under other schema name - ctd
December 2, 2005 - 5am Central time zone
Reviewer: Serge Shmygelsky from Ukraine
Hello Tom,
yes, I know what ALTER SESSION SET CURRENT_SCHEMA is doing. I was just wondering why it is possible
to create a table in the schema we switched to and not a database link.
P.S. You've already taught me not to use SYS account :). It was just a copy-paste after starting up
the database which I cut off :)
Thanks in advance for your response.
executing remote db package using db link and synonym
December 20, 2005 - 12pm Central time zone
Reviewer: Kuldeep from India
Dear Tom,
My requirement is to make available a remote package to my local database. Remote package contains
around 30 procedure and functions. so I cant make 30 synonym for each procedure and function of
package.
I searched your site. now I am able to call remote package's procedure easly but in the case of
calling remote package's function I am facing error.
steps what i followed:
sql> create database link mylink connect to RUSER identified by RPWD using 'RCSTR';
sql> desc rpkg@mylink
procedure p1
function f1 return number
sql> create synonym RSYN for rpkg@mylink;
sql> set serveroutput on
sql> exec rsyn.p1
PL/SQL procedure successfully completed.
sql> select rsyn.f1 from dual;
select rsyn.f1 from dual
*
ERROR at line 1:
ORA-00904: "rsyn"."f1": invalid identifier
while
SQL> declare
2 a number;
3 begin
4 a:=rsyn.f1;
5 dbms_output.put_line(a);
6 end;
7 /
75
PL/SQL procedure successfully completed.
How can I call remote package's functions in my SQLs.
Thanks and regards,
Followup December 20, 2005 - 1pm Central time zone:
why is "rsyn"."f1" in "lower" case ?
should be "upper" case:
ops$tkyte@ORA10GR2> select a.b from dual;
select a.b from dual
*
ERROR at line 1:
ORA-00904: "A"."B": invalid identifier
Strange
December 22, 2005 - 7pm Central time zone
Reviewer: abc
Tom
I dont have any db link which is pointing to REP server but when I do
SQL> select count(1) from mgr_fpa.region_ref@rep;
select count(1) from mgr_fpa.region_ref@rep
*
ERROR at line 1:
ORA-28000: the account is locked
ORA-02063: preceding line from REP
REP is db link going to REPU
SQL> select * from dba_objects where object_name like '%rep%';
OWNER OBJECT_NAME
SUBOBJECT_NAME
------------------------------
----------------------------------------------------------------------------------------------------
---------------------------- ------------------------------
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP
STATUS T G S
---------- -------------- ------------------ --------------- --------------- -------------------
------- - - -
SYS /3e096b67_OraclePreparedStatem
122963 JAVA CLASS 24-JUN-01 15-FEB-04 2002-03-02:10:03:45
VALID N N N
SYS /4784edfa_OraclePreparedStatem
122961 JAVA CLASS 24-JUN-01 15-FEB-04 2002-03-02:10:03:45
VALID N N N
SYS /8af1ab71_coreproperties
132586 JAVA RESOURCE 24-JUN-01 02-MAR-02 2002-03-02:09:57:08
VALID N N N
SYS /b267ff29_OraclePreparedStatem
122965 JAVA CLASS 24-JUN-01 15-FEB-04 2002-03-02:10:03:45
VALID N N N
SYS /bf824cc6_RTStatementJDBCPrepa
121825 JAVA CLASS 24-JUN-01 15-FEB-04 2002-03-02:10:04:17
VALID N N N
SYS java/sql/PreparedStatement
125165 JAVA CLASS 24-JUN-01 15-FEB-04 2002-03-02:10:03:06
VALID N N N
PUBLIC /18d4f450_MAnnSQLPreprocessorS
5499446 SYNONYM 16-FEB-04 16-FEB-04 2004-02-16:00:52:49
VALID N N N
PUBLIC /3e096b67_OraclePreparedStatem
122964 SYNONYM 15-FEB-04 15-FEB-04 2004-02-15:20:52:02
VALID N N N
PUBLIC /4784edfa_OraclePreparedStatem
122962 SYNONYM 15-FEB-04 15-FEB-04 2004-02-15:20:51:59
VALID N N N
PUBLIC /975e31e9_MAnnSQLPreprocessorV
5499448 SYNONYM 16-FEB-04 16-FEB-04 2004-02-16:00:52:49
VALID N N N
PUBLIC /b267ff29_OraclePreparedStatem
122966 SYNONYM 15-FEB-04 15-FEB-04 2004-02-15:20:52:03
VALID N N N
PUBLIC /ba6941e2_MAnnSQLPreprocessor
5499449 SYNONYM 16-FEB-04 16-FEB-04 2004-02-16:00:52:49
VALID N N N
PUBLIC /bd14c239_MAnnSQLPreprocessorE
5499445 SYNONYM 16-FEB-04 16-FEB-04 2004-02-16:00:52:49
VALID N N N
PUBLIC /bf824cc6_RTStatementJDBCPrepa
121826 SYNONYM 15-FEB-04 15-FEB-04 2004-02-15:20:47:04
VALID N N N
PUBLIC /efef3908_MAnnSQLPreprocessorS
5499447 SYNONYM 16-FEB-04 16-FEB-04 2004-02-16:00:52:49
VALID N N N
PUBLIC java/sql/PreparedStatement
125166 SYNONYM 15-FEB-04 15-FEB-04 2004-02-15:20:52:11
VALID N N N
ORDSYS /18d4f450_MAnnSQLPreprocessorS
21085 JAVA CLASS 01-MAY-00 16-FEB-04 2004-02-16:00:52:04
VALID N N N
ORDSYS /975e31e9_MAnnSQLPreprocessorV
21087 JAVA CLASS 01-MAY-00 16-FEB-04 2004-02-16:00:52:04
VALID N N N
ORDSYS /ba6941e2_MAnnSQLPreprocessor
21088 JAVA CLASS 01-MAY-00 16-FEB-04 2004-02-16:00:52:04
VALID N N N
ORDSYS /bd14c239_MAnnSQLPreprocessorE
21084 JAVA CLASS 01-MAY-00 16-FEB-04 2004-02-16:00:52:04
VALID N N N
ORDSYS /efef3908_MAnnSQLPreprocessorS
21086 JAVA CLASS 01-MAY-00 16-FEB-04 2004-02-16:00:52:04
VALID N N N
Please suggest
Followup December 22, 2005 - 8pm Central time zone:
unlock the account?
executing remote db package using db link and synonym
December 28, 2005 - 11am Central time zone
Reviewer: kuldeep from India
Dear Tom,
Thanks for your response.
In my last post the lower case identifier was because I edited the names of the identifiers. Sorry
I typed in lower case. But the rest of the things were correct.
There is another example (copy and past as it is)
---------------
SQL>
SQL> desc pkgp1
FUNCTION F RETURNS NUMBER
PROCEDURE P
SQL> exec pkgp1.p
PL/SQL procedure successfully completed.
SQL> select pkgp1.f from dual;
select pkgp1.f from dual
*
ERROR at line 1:
ORA-00904: "PKGP1"."F": invalid identifier
SQL>
---------------
Thanks and regards,
Followup December 28, 2005 - 5pm Central time zone:
I have tested on 10gR2 and cannot reproduce.
Versions involved here?
strange
December 28, 2005 - 1pm Central time zone
Reviewer: abc
Unlock is fine but myh concern is there is no dblink poinitng to prodocution REP then why this
connection login to REP
I dont have any db link which is pointing to REP server but when I do
what are the following objects?
SQL> select count(1) from mgr_fpa.region_ref@rep;
select count(1) from mgr_fpa.region_ref@rep
*
ERROR at line 1:
ORA-28000: the account is locked
ORA-02063: preceding line from REP
REP is db link going to REPU
SQL> select * from dba_objects where object_name like '%rep%';
OWNER OBJECT_NAME
SUBOBJECT_NAME
------------------------------
---------------------------------------------------------------------------------
----------------------------------------------- ------------------------------
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME
TIMESTAMP STATUS T G S
---------- -------------- ------------------ --------------- ---------------
------------------- ------- - - -
SYS /3e096b67_OraclePreparedStatem
122963 JAVA CLASS 24-JUN-01 15-FEB-04
2002-03-02:10:03:45 VALID N N N
SYS /4784edfa_OraclePreparedStatem
122961 JAVA CLASS 24-JUN-01 15-FEB-04
2002-03-02:10:03:45 VALID N N N
SYS /8af1ab71_coreproperties
132586 JAVA RESOURCE 24-JUN-01 02-MAR-02
2002-03-02:09:57:08 VALID N N N
SYS /b267ff29_OraclePreparedStatem
122965 JAVA CLASS 24-JUN-01 15-FEB-04
2002-03-02:10:03:45 VALID N N N
Followup December 28, 2005 - 5pm Central time zone:
object names, unless quoted, are stored in upper case.
it would be %REP%, not %rep%
and you do not necessarily need to have a database link created with global names and the proper
setup, the database will use the current credentials and connect to the remote database using them.
strange
December 28, 2005 - 6pm Central time zone
Reviewer: abc
Tom,
Do you think it makes any sense?
Select * from dual@rep from repu is giving invalid password and causing failed login attempts in
REP because of Tns enteries .
We are using ONAMES.
There is no db link pointing to production server REP
Followup December 28, 2005 - 8pm Central time zone:
yes.
executing remote db package using db link and synonym
January 3, 2006 - 8am Central time zone
Reviewer: Kuldeep from India
Dear Tom,
Thanks again for your response.
I am using Oracle 9.2.0.1.0 on Windows2000.
Regards,
Followup January 3, 2006 - 8am Central time zone:
I'll have you work with support - but they'll probably ask you to apply the current patchset to get
the 4th digit relatively up to date in your version.
With reference to earlier post in thread
January 3, 2006 - 12pm Central time zone
Reviewer: Matt from UK
In a review to this thread above:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:456820211101#38461801491167
There is a suggestion that it is possible to create a database link to an MS access database. Is
this indeed possible?
I'm looking to obtain a connection to an MS ACCESS database from Oracle 9.2.0.6 to carry out some
ad-hoc querying and reporting. I have briefly looked at Heterogeneous services, which look to be a
little like overkill for what I am trying to achieve. I am really just looking for a "quick and
dirty" connection to carry out some queries/reporting from Oracle.
Do you have a link to the doco that explains how to configure Net services to allow a connection to
a non-oracle DB such as MS ACCESS?
P.S Looking forward to the asktom re-write. Finally a real WAP interface :o)
Many thanks in advance.
Regards,
Can we execute DDL's using Database links
January 10, 2006 - 11am Central time zone
Reviewer: Matte from Austin,Tx
Is it possible to execute create/alter/drop statements on remote database by using database link?
Followup January 10, 2006 - 1pm Central time zone:
you cannot do ddl over a database link.
db links and security
January 11, 2006 - 6pm Central time zone
Reviewer: sudershan from usa
oracle 9.2.0.6
Tom,
In case of private database links..besides user_db_links..
is there any other place where text password is visible.
Followup January 12, 2006 - 10am Central time zone:
sys.link$
DBLink with Analytic Function
January 13, 2006 - 4am Central time zone
Reviewer: Joe from Malaysia
Hi Tom,
I have a case here where a dblink is used to insert some records from the local DB to the remote
DB.
The statement looks something like this:
INSERT INTO TARGET_TABLE@DBLINK
SELECT SOURCE.*
FROM SOURCETABLE SOURCE
WHERE LOG_ID IN
(
SELECT LOG_ID
FROM (
SELECT
LOG_ID,
MAX(LOG_DATE) OVER (PARTITION BY LOG_GRP_ID) AS MAXDATE
FROM LOG_HIST
WHERE
TRUNC(LOG_DATE) >= TO_DATE('03-AUG-2005','DD-MON-YYYY')- 1
AND TRUNC(LOG_DATE) <= TO_DATE('09-DEC-2005','DD-MON-YYYY')
AND audit_log_table = 'SOMETABLE'
) TBLA
WHERE TRUNC(TBLA.MAXDATE) >= TO_DATE('03-AUG-2005','DD-MON-YYYY')
AND TRUNC(TBLA.MAXDATE) < TO_DATE('09-DEC-2005','DD-MON-YYYY')
AND TBLA.LOG_ID = SOURCE.LOG_ID
);
Total records selected and inserted is around 200 records only.
The statement runs for > 2 minutes. But when i remove the INSERT INTO part,the SELECT itself runs
in < 1 second.
Upon further investigation when I :
1. remove the DBLINK and insert into a local table, the statement completes in < 1 second.
2. remove the OVER PARTITION BY but still use DBLINK, the statement also completes in < 1 second.
So I figure it is a problem when analytic functions are used in combination with DBLink.
Currently the temporary workaround is to INSERT into a local table first along with the OVER
PARTITION BY.
Then SELECT from the local table to the DBLINK table.
This takes around 1-2 seconds.
I am still trying to figure out why the DBLINK and OVER PARTITION BY combination results in such
poor performance.
Is there an explanation why this is so? Is there a problem with Oracle when using analytic
functions such as OVER PARTITION BY with DBLINK.
Preferred method to grant access to linked objects
January 16, 2006 - 6pm Central time zone
Reviewer: Sudershan from USA
Oracle 9.2.0.6.0
Hi Tom,
What is your preferred link method to give access to users
for remote objects.
I use a one user that own the links (with the password)..and
that user then grant access to other users..this way no one else has access to the link.
Password is visible only to the schema that owns the link..
and through sys.link$ (so make sure no one has access to this sys table).
What do you think about this approach in terms of security?
Thanks.
Followup January 16, 2006 - 9pm Central time zone:
depends entirely on your NEEDS.
what you've set up is a proxy database link, all users will be "proxied" into the other database
using that individuals credentials. If that is what you need - that is what you need.
Else, you would create the database link without a username/password and let the current users
credentials be used. If that is what you need.
January 23, 2006 - 11am Central time zone
Reviewer: virgile CREVON from france
Hi,
Why not using DB_LINKs with embedded CONNECT_STRING ??
Many of the questions here would have been solve with this kind of trick.
Here's an example :
scott@8i> create database link ora8idev
2 connect to scott
3 identified by tiger
4 using 'ora8idev'
5 /
use this instead :
scott@8i> create database link ora8idev
2 connect to scott
3 identified by tiger
4 using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = MYHOSTHERE)
(PORT = MYPORTHERE)
)
)
(CONNECT_DATA =
(SID = MYSIDHERE)
)
)'
5 /
Pro : only user with db_link can modify the DB_LINK definition (without this trick, anybody can
modify TNSNAMES.ORA and, suddenly, the app stop responding ...).
Con : always the password stored in CLEAR...
Regards,
Virgile
Followup January 23, 2006 - 11am Central time zone:
the con has nothing to do with the question though - they both have that attribute, it is not any
different.
You can use either construct, it is up to you.
Also getting ORA-02019
January 30, 2006 - 1pm Central time zone
Reviewer: V from East coast
Here is the setup, on Oracle 9.2.0.6, Windows 2003
SQL> connect SchemaA/password@NSN1
SQL> create database link MyLink1
2 connect to VCG identified okm135
3 using 'STAF'
4 /
Database link created.
SQL> create or replace VIEW rem_mydocs as
2 (select * from mydocs@MyLink1)
3 /
View created.
SQL> grant select on rem_mydocs to UserA ;
Grant succeeded
SQL> Create or replace function fn_rem_mydocs
2 return Sys_RefCursor
3 IS
4 v_rc Sys_RefCursor;
5 begin
6 open v_rc for
7 select * from rem_mydocs ;
8 return (v_rc);
9 end;
10 /
Function created.
SQL> grant execute on fn_rem_mydocs to UserA;
Grant succeeded
SQL> connect UserA/test975@NSN1
Connected.
SQL> create or replace synonym rem_mydocs for SchemaA.rem_mydocs;
Synonym created.
SQL> create or replace synonym fn_rem_mydocs for SchemaA.sp_rem_mydocs ;
Synonym created.
SQL> select count(*) from rem_mydocs ;
COUNT(*)
---------
324
SQL> var g_ref refcursor
SQL> begin :g_ref := fn_rem_mydocs; end;
2 /
SQL> print g_ref
ERROR:
ORA-02019: connection description for remote database not found
I have not been able to figure it out. Any help would be greatly appreciated.
Followup January 30, 2006 - 3pm Central time zone:
I'll have to ask you to work with support on this one, nothing pops immediately to mind.
Also getting ORA-02019
February 2, 2006 - 10am Central time zone
Reviewer: V from East Coast
Although there was an Bug reported for this error, the symptom where different. So, I kept looking
for a solution and found a workaround to my problem. Invoker Rights
I created the function with AUTHID CURRENT_USER
and now it works.
create or replace function fn_rem_mydocsreturn Sys_RefCursor
AUTHID CURRENT_USER
is
v_rc Sys_RefCursor;
begin
open v_rc for select * from rem_mydocs ;
return (v_rc);
end;
/
Followup February 2, 2006 - 12pm Central time zone:
that completely changes the behavior of this entirely - you do understand that right. entirely and
compleletly changes the behavior
this workaround is really "we've changed what we want from the database"
Refcursor over database link
February 9, 2006 - 11am Central time zone
Reviewer: William from Europe
Hi Tom,
Is it possible in 9iR2 to use a refcursor over a database link?
As follows:
DB1
function get_attributes (p_type in varchar2
, p_date in date
)
return sys_refcursor
is
result sys_refcursor;
begin
execute immediate 'alter session set hash_join_enabled = true';
open result for
select PRDID
, NAME
, DATATYPE
, STRINGVALUE
, FILENAME
from fe_lpd_attributevalue
where ( date_inserted > p_date
or date_modified > p_date
)
and ( SECOND_PRD_TYPE = p_type
or ( p_type is null and SECOND_PRD_TYPE <> 'B')
);
--
return result;
end;
DB2
procedure grab ( p_type in varchar2 default null) is
c sys_refcursor;
l_number number;
l_value2 varchar2(200);
l_value3 varchar2(200);
l_value4 varchar2(200);
l_value5 varchar2(2000);
l_value6 varchar2(200);
l_value7 varchar2(200);
l_value8 varchar2(200);
l_value9 varchar2(200);
begin
-- this function is a remote call on another DB!!!
c := GET_ATTRIBUTES(p_type ,sysdate);
fetch c into l_number
, l_value3
, l_value4
, l_value5
, l_value6
;
close c;
end if;
end grab;
When I run the procedure Grab on DB2 i am getting an error:
ERROR at line 1:
ORA-01007: variable not in select list
for the moment i cannot find out what happens so I just want to know if it is possible to use a
refcursor over a database link?
Kind regards,
William
Followup February 10, 2006 - 11am Central time zone:
refcursors may not be returned over a dblink:
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1448
that is what the somewhat obscure wording:
...
Using a REF CURSOR variable in a server-to-server RPC results in an error. However, a REF CURSOR
variable is permitted in a server-to-server RPC if the remote database is a non-Oracle database
accessed through a Procedural Gateway.
......
is meant to convey. It could be said a bit more clearly probably. You are trying to do a "server
to server RPC"
performance issues which is better
February 13, 2006 - 5am Central time zone
Reviewer: daniel from KSA
Hi Tom,
We have a database that is very heavy oltp and we have around 300 tran/sec with a redo size of
1,727,818 per sec.
The top 50 to 60% wait in the top5 wait event is log_file_sync.
We tried to fix the io and other things, but we could not reduce much waits and now it seems that
we have to batch the commits but thats design change and nature of application is not allowing
this. We have the following idea:
We know that out of 300 tran per sec, around 60% of the commits come from one process(price-engine)
which keeps on updating 2 tables which have share price of each production that application sells.
Now we are thinkign of:
1. Creating another instance(InsB) and in that instance copying the above 2 tables. The
Price-Engine will now update the InstanceB and not InstanceA.
But note that users (500) will connect to the first db as before.
2. The first instanceA wil also have these 2 tables, but we will create a unix-java process which
will have one connections to each of these databases.
What this process will do is to read the two tables continusoly every 1/2 sec from instanceB, and
will send updates to the first InstanceA and then commits.
Note that the price-engine will update the two tables in InstanceB heavily say like 100 updates in
1 sec , but we only replicate at every 1/2 sec to the first instance, which is acceptable to our
business users.
This way we reduce the load on instance A.
Now the question is " is there a better way"
We dbas suggested that we do not neeed to have the tables in the first instance, we wil create
synoms in dblink over the two instance and 500 users will read the 2 tables online on the seond db
via db link.
bUT this way every time there will be a connection created on second instance.
Is there a connection pooling or something in dblink that will make dblink to remain connect all
the time???
cheers
Followup February 13, 2006 - 8am Central time zone:
but how big are the waits. you will always and forever have a top 5 timed events.
And if the price engine (sounds like a background process) is the biggest waiter for this - what
effect will this have on the end users - will it change them at all.
eg: go to an application level trace, show yourself that the end users are waiting massively on
this - before touching a thing.
logfile sync
February 15, 2006 - 12am Central time zone
Reviewer: daniel from ksa
Hi Tom.
The top wait is the logfile sync wait which was 60% last week.
If you recall i asked a question some time back about logfile sync waits and you told me that to do
that i have to batch COMMITS or increase speed of my san which is ibm es800-shark.
Now the good news is that yesterday we solved this problem.
(the problem was not solved when i wrote this thread).
I have a tar opebed for this for 1 month and all oracle engineers can do was the same reply as you
said.
Anyway what i did was asked the aix admin to create me a separate mount point on same san say
'/redolog' but mount it with cio(concur io) option, this is like having a raw device but with a
file system(aix 5.3 and oracle metalink note suggests that;docid:245356.1
And the logfile waits are gone, they are now 3% as compared to 60%. And out users have conformed
that app is now performing efficiently, and our price engines etc have not more thruput i.e. we
increased no of tran/sec.
My advise to that metalink engineer was:
Try having some expertise in oracle support from os point of view. if they could only ask us to put
a trace of logwr , perhaps some one in oracle who knows the os basics can tell us where we are
having issues.
Anyway the effect of cio mount point (bypassing the os cache0 is exponential.
We are so happy that we achieved this.
You may term this as not an oracle problem in the first place, yes i agree but i wish if there are
multi role experts in oracle support. All they do is to first try to force you to upgrade to
latestt patch and then if not solve, contact your os vendor. I mean this is so unacceptable from
management point of view, we do not want to be in between oracle and ibm pointing fingers.
Cheers
Followup February 15, 2006 - 9am Central time zone:
... increase speed of my san
which is ibm es800-shark. ...
which is precisely what you did. You cannot expect us to know your hardware and be able to tell
you how to make your hardware better? I mean, maybe you do - but it isn't going to be a reasonable
request.
You made your log device faster, many articles on this site suggest using forcedirectio (an option
on Solaris) - not knowing your hardware (we don't all have your configuration) makes it, well, sort
of difficult.
Performance using dblink and join operations.
February 20, 2006 - 5am Central time zone
Reviewer: Jorge from Madrid, Spain
Hi Tom!,
We have two separate databases on different machines. The question is very simple. What should I
consider (about performance) if I do a join between a local instance with another remote instance?.
Suppose I have an instance A and another remote B. My query is select * from a A,b@dblink B where
A.x=B.y. Is it true that remote data from B is sent to the data buffer cache A and there (in A
instance) resolved the query?. If so, then I think my solution would be configure my protocol in
order to transfer quickly the amount of data.
I have a non technical question but I will appreciate very much your advice. Six days ago, I
received a mail from Oracle. As I could see, there is a two days seminar about advance performance
tuning for 9.2. I am a dba junior, last year I did a four month master in Oracle 9i administration.
My problem is that I don´t know if this course is very advanced to me. It treats about index
performance,myths about indexes and all this subject matter but I don´t know if it is very soon or
very advanced to me. I have been reading your books( now I am re-reading Oracle Expert
Architecture) , this very useful forum, articles by M. Rittman and Jonathan Lewis for more than one
year but I always have questions and doubts. Oracle training manager has adviced to me that this
course is very complex. Tom, please, what would you advice to me?
Thank you Tom, again.
Followup February 20, 2006 - 8am Central time zone:
It could be that B will be sent to A, it could be that A will send to B little queries of the form
"select * from b where y = :bind_variable" as well.
As always "it depends"
Sorry - I don't know what the course you are referring to entails. I cannot comment on whether it
would be appropriate for you or not.
Database Link
February 28, 2006 - 12am Central time zone
Reviewer: Debasish Ghosh from India
I have local database and remote database both are on 10g.
In local database there is two schema with same table. And remote database has also same table
(connected via intranet).
I am using oracle 10g developer suit for frontend designing.
My logic is
In local database there is two schema
Local Remote
Schema – A Schema-C
Schema – B
When I insert data into to table abc on local schema A at the same time the same data will be saved
on to remote database through database link. If database link not found then the same data will be
saved on the second schema on local database. When the connection established then the data
transfer from the second schema to remote database.
Here my question is how can I know (remote connection is not found) then the data go to second
schema on local database if found then data go to remote database.
I could not trap the when the connection is not established on developer 10g suit.
Thanks for advance
Followup February 28, 2006 - 7am Central time zone:
why? this is an utterly bad idea. I cannot imagine this in real life. What are you doing?
What action should have take
March 1, 2006 - 12am Central time zone
Reviewer: D.Ghosh from India
what is other way to save data local & into server at a tile. if link is fail then what action
should have taken.
Followup March 1, 2006 - 8am Central time zone:
"& into server at a tile"?
Insert data locala as well as remote server
March 9, 2006 - 3am Central time zone
Reviewer: Debasish Ghosh from Calcutta, India
what is other way to save data local & into server at a tmle. if link is fail then what action
should have taken.
DG
How to save data into to remote table
March 9, 2006 - 3am Central time zone
Reviewer: D.Ghosh from India
what is other way to save data local & into server at a time. if link is fail then data should not
store into a remote database, after establish the link how I could update remote table with the new
row which was not saved due to link failure, but the data has saved in local database.
Getting Error ORA-12154: TNS:could not resolve service name
March 15, 2006 - 7am Central time zone
Reviewer: Vijay from India
Hi Tom,
I tried to create a database link as follows,
TNSNAMES Entry:
DRP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <COMPUTER NAME>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRP.DRP)
)
)
The syntax I have used to create the database link,
create database link DRP
connect to <USER>
identified by <PASSWORD>
using 'DRP'
everything seems to be ok. But still getting the above error. Can you please help me?
Regards,
Vijay
Followup March 15, 2006 - 5pm Central time zone:
that drp = entry is in the tnsnames.ora file on the SERVER correct, your local copy on your pc is
meaningless for a db link.
Using current session on dblink
April 4, 2006 - 8pm Central time zone
Reviewer: Jairo Ojeda from Costa Rica
I have users that executes my procedures and perform transactions on tables directly through a db
link and the audit trail and other logs get the user of the db link (generic user) but I need to
get the real database user that perform the transaction not the db link user, where my database is
the remote one access by the db link.
To get it, I set a test case where NEMESYS is the remote database,
On remote database: --my database
CREATE USER TEST1 IDENTIFIED BY TEST1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMPORAL;
GRANT CREATE SESSION TO TEST1;
CREATE USER SCHEMA2 IDENTIFIED BY SCHEMA2 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMPORAL;
GRANT CREATE SESSION TO SCHEMA2;
GRANT CREATE PROCEDURE TO SCHEMA2;
ALTER USER SCHEMA2 QUOTA UNLIMITED ON USERS;
create or replace package schema2.pkg_test
as
function pf_plus(pa int, pb int) return int;
end pkg_test;
/
create or replace package body schema2.pkg_test
as
function pf_plus(pa int, pb int) return int
is
li_return int;
begin
li_return := pa + pb;
return li_return;
end pf_plus;
end pkg_test;
/
grant execute on schema2.pkg_test to test1;
On local database:
CREATE USER TEST1 IDENTIFIED BY TEST1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMPORAL;
GRANT CREATE SESSION TO TEST1;
CREATE USER SCHEMA1 IDENTIFIED BY SCHEMA1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMPORAL;
GRANT CREATE SESSION TO SCHEMA1;
GRANT CREATE PROCEDURE TO SCHEMA1;
ALTER USER SCHEMA1 QUOTA UNLIMITED ON USERS;
CREATE PUBLIC DATABASE LINK DBL_REMOTE USING 'NEMESYS';
create or replace package schema1.pkg_local
as
procedure pp_plus(pa int, pb int);
end pkg_local;
/
create or replace package body schema1.pkg_local
as
procedure pp_plus(pa int, pb int)
is
li_result int;
begin
li_result := schema2.pkg_test.pf_plus@dbl_remote(pa, pb);
dbms_output.put_line('El resultado es: '||li_resultado);
end pp_plus;
end pkg_local;
/
ORA-04052: error occurred when looking up remote object SCHEMA1.SCHEMA2@DBL_REMOTA
ORA-00604: error occurred at recursive SQL level 1
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from DBL_REMOTE
I need to get the database user account of the end user when the end user connects to my database
through a db link and the procedure (on my database) that this end user called is called from a
procedure on their local database.
Followup April 5, 2006 - 5pm Central time zone:
does that user exist on the remote database - it would appear not.
You can use dbms_session@remote to set the client id to any string you like on the remote site and
that string will be placed into the audit trail as well.
but basically, unless the invoking site tells the remote site "I am so and so" the remote site
doesn't "know"
ORA-00904: invalid identifier
April 5, 2006 - 1pm Central time zone
Reviewer: Kamala iruku from MD
i have select statement which gives some rows with
database link
SELECT a.invoice_id,vendor_id,invoice_payment_id,payment_num,
period_name,amount,bank_account_num,bank_num,discount_taken,Accounting_date,NULL,
a.org_id
FROM
ap_invoice_payments_all@apps_link a, ap_invoices_all@apps_link b
WHERE
a.invoice_id = b.invoice_id
and a.org_id = 62
AND a.last_update_date LIKE SYSDATE - 1;
But, when I put this select statement in the cursor, I am
getting the error
SQL> show errors
Errors for PACKAGE PKG_CELLPOINT_FACT_STAGE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
11/1 PL/SQL: SQL Statement ignored
19/5 PL/SQL: ORA-00904: "A"."LAST_UPDATE_DATE": invalid identifier.
This package is compiled on 8i on windows 2000. I installed
10g Rel 2 on Solaris x86_64.I did the export on 8i and
imported on 10g Rel 2. Import went through without any
problems. The above package got errors.
Followup April 6, 2006 - 9am Central time zone:
"a.last_update_date like sysdate-1" - how scary is that! yow, what are you trying to do there???
Not sure why you are getting the error, but thank goodness you are - that needs to be FIXED.
I'm pretty sure you meant to code:
where a.last_update_date >= trunc(sysdate-1)
and a.last_update_date < trunc(sysdate)
(eg: all records from yesterday). Using like - ouch. Implicit conversion of last_update_date to
a string. same with sysdate-1. Using the nls_date_format that just happens to be in place!!!
ouch.
If the error persists after that change (likely it will) please utilize support to see if there is
a known issue with the really really old 8i software.
ORA-00904: invalid identifier
April 5, 2006 - 1pm Central time zone
Reviewer: Kamala iruku from MD
i have select statement which gives some rows with
database link
SELECT a.invoice_id,vendor_id,invoice_payment_id,payment_num,
period_name,amount,bank_account_num,bank_num,discount_taken,Accounting_date,NULL,
a.org_id
FROM
ap_invoice_payments_all@apps_link a, ap_invoices_all@apps_link b
WHERE
a.invoice_id = b.invoice_id
and a.org_id = 62
AND a.last_update_date LIKE SYSDATE - 1;
But, when I put this select statement in the cursor, I am
getting the error
SQL> show errors
Errors for PACKAGE PKG_CELLPOINT_FACT_STAGE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
11/1 PL/SQL: SQL Statement ignored
19/5 PL/SQL: ORA-00904: "A"."LAST_UPDATE_DATE": invalid identifier.
This package is compiled on 8i on windows 2000. I installed
10g Rel 2 on Solaris x86_64.I did the export on 8i and
imported on 10g Rel 2. Import went through without any
problems. The above package got errors.
Using current session on dblink
April 6, 2006 - 3pm Central time zone
Reviewer: Jairo Ojeda from Costa Rica
Thanks for the dbms_session@remote references.
Following the test case,
I created the schema1 user on the remote database, granted the execute on schema2.pkg_test and then
schema1.pkg_local on the local database compiled.
The test case works fine and I can get the end user on the remote database.
1. I set the SQLPLUS_PRODUCT_PROFILE to disabled "DECLARE" and "BEGIN" command with SQL*Plus, but
If they accesses through dblink, How can I disabled "DECLARE" and "BEGIN" command. --I know that
there are other tools to limit but I want to know I can do that to a user that accesses trough a
dblink.
2. Can you tell me if I can set something for the schema1 at the remote database to do not permit
execute the any package granted --schema2.pkg_test
It is because I need to grant the execute on remote database to let package compiled on the local
database, but the only user that can execute my package is the end user not the schema1 user, this
user only need the execute privilege to compile their code.
Followup April 8, 2006 - 8am Central time zone:
1) but - you are NOT accessing the remote database via sqlplus, you are accessing the remote
database via a database link!!!!
that product user profile stuff only limits sqlplus and in my opinion should just be ignored these
days. The only thing it does is provide a FALSE SENSE OF SECURITY. In the olden golden days -
before there was a ton of ad-hoc tools to connect to the database, it *might* have worked. Today
in 2006, rather futile to use it.
2) eh?
How to use /*+ driving_site */ if using synonyms to hide dblinks.
April 7, 2006 - 2pm Central time zone
Reviewer: Rob from Pittsburgh, PA
Tom:
I am using synonyms to refer to my remote tables like this:
CREATE SYNONYM ATTRIBUTE
FOR ATTRIBUTE@dblink;
rather than referencing dblinks in the sql.
I would like to use the driving_site hint since most of the data resides in the remote db. The hint
seems to be ignored if you don't have a table@dblink in the FROM clause of the sql stmt. Any tricks
to work around this?
Followup April 8, 2006 - 9am Central time zone:
are you sure about that?
ops$tkyte@ORA10GR2> @test
ops$tkyte@ORA10GR2> /*
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> drop table t1;
ops$tkyte@ORA10GR2> drop table t2;
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t1 as select * from all_objects;
ops$tkyte@ORA10GR2> alter table t1 add constraint t1_pk primary key(object_id);
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T1');
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t2 as select * from all_users;
ops$tkyte@ORA10GR2> alter table t2 add constriant t2_pk primary key(username);
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T2');
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace synonym t2_remote for t2@ora10gr2@loopback;
ops$tkyte@ORA10GR2> */
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> set linesize 1000
ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select * from t1, t2_remote where t1.owner = t2_remote.username;
Execution Plan
----------------------------------------------------------
Plan hash value: 2842506388
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50059 | 5426K| 231 (5)| 00:00:02 | | |
|* 1 | HASH JOIN | | 50059 | 5426K| 231 (5)| 00:00:02 | | |
| 2 | REMOTE | T2 | 34 | 612 | 3 (0)| 00:00:01 | ORA10~ | R->S |
| 3 | TABLE ACCESS FULL| T1 | 50059 | 4546K| 226 (4)| 00:00:02 | | |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OWNER"="T2_REMOTE"."USERNAME")
Remote SQL Information (identified by operation id):
----------------------------------------------------
2 - SELECT "USERNAME","USER_ID","CREATED" FROM "T2" "T2_REMOTE" (accessing
'ORA10GR2.REGRESS.RDBMS.DEV.US.ORACLE.COM@LOOPBACK' )
ops$tkyte@ORA10GR2> select /*+ driving_site( t2_remote ) */ * from t1, t2_remote where t1.owner =
t2_remote.username;
Execution Plan
----------------------------------------------------------
Plan hash value: 861941715
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 50059 | 5426K| 231 (5)| 00:00:02 | | |
|* 1 | HASH JOIN | | 50059 | 5426K| 231 (5)| 00:00:02 | | |
| 2 | TABLE ACCESS FULL | T2 | 34 | 612 | 3 (0)| 00:00:01 | ORA10~ | |
| 3 | REMOTE | T1 | 50059 | 4546K| 226 (4)| 00:00:02 | ! | R->S |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A2"."OWNER"="A1"."USERNAME")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE
CT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECOND
ARY" FROM "T1" "A2" (accessing '!' )
Note
-----
- fully remote statement
ops$tkyte@ORA10GR2> set autotrace off
ops$tkyte@ORA10GR2>
Update on ORA-00904: invalid identifier April 05, 2006
April 11, 2006 - 9am Central time zone
Reviewer: Kamala iruku from MD
I referred the above case to the oracle support. They
found out that it seems to be bug in 10g Rel 2. Here is
the test case we did it.The following procedure created
successfully.
create or replace procedure a is
v_date date;
CURSOR CUR_AP IS
SELECT select sysdate from dual@apps_link;
begin
open cur_ap;
fetch cur_ap into
v_date;
close cur_ap;
end;
Procedure created.
But, when I tried to create procedure
1 create or replace procedure a is
2 v_invoice NUMBER(15);
3 CURSOR CUR_AP IS
4 SELECT invoice_id from ap_invoices_all@apps_link;
5 begin
6 open cur_ap;
7 fetch cur_ap into
8 v_invoice;
9 close cur_ap;
10* end;
SQL> /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE A:
LINE/COL ERROR
-------- --------------------------------------------------------
4/2 PL/SQL: SQL Statement ignored
4/9 PL/SQL: ORA-00904: "INVOICE_ID": invalid identifier
Oracle support answer for this!!!
This is a new bug still in description phase as development has yet to reproduce even though the
support engineer was able to reproduce with the customer'
s testcase. Although the bug is not yet public since it hasn't been accepted by development as a
bug,As it appears the workaround is acceptable for you at least for now.
PROBLEM:
--------
App which worked in 8i and 9i (9.2) is failing in 10.2.0.2.
In short, the problem is that an update using a synonym for a remote table
(dblink) fails with ORA-904.
.
The long version is that their PLSQL app appears to be making updates when it
is not because of this ORA-904 condition.
The testcase provided below demonstrates all of this.
.
.
DIAGNOSTIC ANALYSIS:
--------------------
Ran the testcase against 10.2.0.2 databases and saw the same behavior.
Also ran the testcase against 9.2.0.6.0 databases and the problem did not
occur (did not test this against 10.2.0.1).
.
Also noted that by referencing schema.table@dblink, instead of synonym, the problem does not occur.
WORKAROUND:
-----------
Call schema.table@dblink instead of synonym. This is unacceptable as it
requires a code change for their application.
DB Link Names
April 17, 2006 - 3am Central time zone
Reviewer: Kiran Kumar from India
Tom,
Is there any specific naming conevtion has to be followed while creating DB links, I saw some of
the topics in this site on Global Name convention, do we have to create DB link with the same name
as Global Name ? please explain me this topin in detail.
Thanks and Regards
Kiran
Followup April 17, 2006 - 8am Central time zone:
have you checked out the documentation first? We explain things in detail in there. It is a
convention - if you have global names set to true (good idea), then the dblink name must
incorporate the remote databases global name - so as to avoid confusion and have unique names for
all distributed databases.
Bidirectional dblinks not recommended?
April 20, 2006 - 10pm Central time zone
Reviewer: Shawn Kim from Korea
Hi Tom,
I have 2 databases (A and B) and 2 WAS per each(WAS_A and WAS B).
Database A has a table A_TBL and database B has the similar table B_TBL
totally same columns but simialr table name and totally different data.
I'like to create 2 dblinks, one from A to B and the other one from B to A.
I expect that any WAS of two can generate same ouput by queries as follows..
WAS_A with database A
====================
select * from A_TBL where ...
union all
select * from B_TBL@B where ...
WAS_B with database B
====================
select * from A_TBL@A where ...
union all
select * from B_TBL where ...
But I hear that bidirectionl dblinks are strongly not recommended from DBA.
Is that true? if so why?
A lot of thanks
Shawn Kim
Followup April 21, 2006 - 7am Central time zone:
"WAS"?
as the DBA why - these are not "bi-directional dblinks", this is
a) database A needs to query B, therefore A must have a dblink to B
b) database B needs to query A, threefore B must have a dblink to A
sounds like "fullfillment of requirements" to me.
fullfillment of requirements
April 21, 2006 - 10am Central time zone
Reviewer: Shawn Kim from Korea
Thanks a lot,Tom.
Sorry for my poor English;;
In Korea, "WAS" is a usual abbreviation of Web Application Server such as WebLogic..(perhaps that
makes sense only in Korea..)
bi-directional is another poor English just to say a) and b).
I understand that you say there is no problem for dbliks like that.right? I will ask the dba why
the hell he does not recommend again tomorrow.
Thanks..
Followup April 21, 2006 - 3pm Central time zone:
That is the problem with abbrevations - which as I get older, I am swearing off of (without first
defining them on the same page)...
That is the way database links are designed to work, yes.
Up to Date Data
April 24, 2006 - 4am Central time zone
Reviewer: Nikhilesh from INDIA
Dear Tom,
We got a mail from our DBA for following problem and he is seeking for solution.Could you please
comment on this.
Background :
A) AT&T has a BO reporting tool which extracts reports from CSG database
B) To avoid performance penalty, they backup the data from production server and
restore it on a report server daily at night. This makes the reporting server information 1 day
old, as they do this activity once a day.
C) Now they want the information to be more up-to-date, as much as possible.
The primary database is of approximately 28GB size. There is one more database separately created
for Reporting purposes.
Problems :
The datatypes used in this application are BLOBs and CLOBs in majority. We have explored following
options :
1) Materialized view or snapshots result in an error saying CLOBs and/or BLOBs are not
supported over the db-link
2) While investigating Logical Standby option, we came across few columns (around 44)
which were appearing as not supported. All these columns belong to the tables which are must for
the reporting purposes.
3) Replication is not supported with BLOBs and CLOBs.
Followup April 24, 2006 - 5am Central time zone:
1) that is not correct, blobs and clobs are supported
2) but you don't tell me what they are!
3) false
But actually, I don't see why you wouldn't use a single database. Hypothetical performance issues
aside.
Tuning distributed query
April 24, 2006 - 11am Central time zone
Reviewer: A reader
Hi, Tom,
I have a question about tuning distributed query.
I have a distributed query that starts from a front end database, say FE, and spread work to
various back end databases on other servers, say BE1, BE2, etc.
I created database links from FE to BE1, BE2, etc and named as BE1, BE2, etc.
Each BE database has the same schema structure, just having different data. Don't ask me why not
use partitioned table on a single schema, it's not my decision. What's done is done.
So we have a view on FE database as a union all of all corresponding tables on BE databases.
Create view V1
as
select * from t1@BE1
union all
select * from t1@BE2
union all
select * from t1@BE3
...
My distributed query is third party tool generated, quite complicated, but will boil down to
something as simple as this:
select * from v1 where c1 in ('somevalue', 'somevalue2')
I want the CBO to rewrite the query *consistently* as
select * from t1@BE1 where c1 in ('somevalue', 'somevalue2')
union all
select * from t1@BE2 where c1 in ('somevalue', 'somevalue2')
union all
select * from t1@BE3 where c1 in ('somevalue', 'somevalue2')
...
This way, the predicate is consistently passed onto BE databases to spread out CPU and IO.
It also means I don't want Oracle to pull all data in t1 from various BE databases into FE memory
and ran filter there. Moreover, I would like to be able to achieve pushing group by into BE
machines, if that's possible.
Materialized view is not an option for me.
Is there a way to do just that?
Thanks,
Insert and driving_site
May 26, 2006 - 7am Central time zone
Reviewer: Andrey V. from Russia
Is the remote view the only way to join tables on remote site in case of insert?
It requires create view and create database ling privilege on remote site, and what if I'm not able
to create objects or links there? I'm suffering from such problem too - remote table is about 200Mb
and local is very small = to join this tables in insert .. select .. statement Oracle always use
full remote table download to local site(regardless to driving_site hint)
Thank you...
Followup May 26, 2006 - 8am Central time zone:
it does not require create database link privilege - you already HAVE the dblink.
create view is pretty much mandatory if you want to use dblinks in my opinion.
or how about a global temporary table on the remote site.
Dblink Session
May 27, 2006 - 9am Central time zone
Reviewer: reader from india
Hi,
I want to identify the remote connections which are being
made using db_links.
Thanks,
Not sure
May 29, 2006 - 4am Central time zone
Reviewer: Andrey V.
Sorry Tom, I'm not sure that I understood it well. Why I don't need "create db link" privilege? Do
I have to create view on local site? It doesn't work because when I use insert it makes no
difference. And on remote site, I have no database link(it's not defined in remote server
dictionary).
This is common situation I think when people create database link to schema that has only create
synonym privilege and some select privileges to certain tables. Objects on remote database are
owned by another user. So "create global temporary table" is not possible and "create view" as
well.
Do you think it would be the best approach in this case to pass local tables to remote site as a
list of values?
Followup May 29, 2006 - 7am Central time zone:
You don't need create database link (some else creates that, you wouldn't be creating it on the fly
or on a recurring basis, no need for YOU to have it)
Also, in most cases you would not need a dblink at all - you just need create view.
across network,
June 6, 2006 - 5pm Central time zone
Reviewer: A reader
I have two questions.
1. If we are doing INSERT..SELECT while SELECT is performing select using a DB LINK, is there a way
to find out how much data is coming through the network in a particular duration (say in one
minute).
2. Is there a way to know when was the last commit/rollback happened on a particular table?
Resolution of pseudo columns in a query to Remote database
June 20, 2006 - 4pm Central time zone
Reviewer: Baiju from India
Hi Tom,
What will be the output of
select sysdate from sys.dual@remote_db
consider the database pointed by remote_db is running on a different timezone. :) :)
Thanks,
Baiju
Followup June 21, 2006 - 9am Central time zone:
sysdate comes from the local database.
If you want sysdate to be remote, you can create a view on the remote site and query that.
SYSDATE from Database Link
June 29, 2006 - 5pm Central time zone
Reviewer: Tim from PA, USA
Thanks for the info on using a view for getting the server time from across a link.
Before we saw this - we had tried using a function and it didn't work - returned the local time. I
was wondering if you knew why? I would have thought that the function would have acted in a
similar manner as the view - at least in this respect - ie, return the remote server time.
CREATE OR REPLACE FUNCTION remote_sysdate
return date
is
begin
return sysdate;
end;
/
We put the above function on both sides of the link and then execute:
select
(select to_char(remote_sysdate,'dd-MON-yyyy hh24:mi:ss') from dual) time_local,
(select to_char(remote_sysdate,'dd-MON-yyyy hh24:mi:ss') from dual@Remote_DBLink) time_remote,
((select remote_sysdate from dual) - (select remote_sysdate from dual@Remote_DBLink)) days_diff
from dual;
The time returned was clearly the local time. (Using a view as you suggested returned the time
difference between the two systems with no problems.)
Thoughts?
Thanks.
Followup June 29, 2006 - 6pm Central time zone:
where does the function "remote_sysdate" reside?
SYSDATE from Database Link
June 30, 2006 - 11am Central time zone
Reviewer: Tim from PA, USA
We created the function on both sides. The one server is 8.1.7 and the other is 9.2.0.4. As I
mentioned - that view you suggested - works great. I was just confused as to why the same concept
using a function did not work.
Followup June 30, 2006 - 12pm Central time zone:
I don't see you calling the remote function though?
select
(select to_char(remote_sysdate,'dd-MON-yyyy hh24:mi:ss') from dual)
time_local,
that was the local function 'remote-sysdate' against the local dual table..
(select to_char(remote_sysdate,'dd-MON-yyyy hh24:mi:ss') from
dual@Remote_DBLink) time_remote,
that ran a remote query against dual, got a row back and then invoked the local function
remote_sysdate
((select remote_sysdate from dual) - (select remote_sysdate from
dual@Remote_DBLink)) days_diff
from dual;
that just combined the two from above - but BOTH are the local function
just like "select sysdate from dual@dblink" runs the "local sysdate" function, select
remote_sysdate from dual@dblink - runs the local remote_sysdate function.
You would just
exec :x := remote_sysdate@dblink;
run the REMOTE function.
SYSDATE from Database Link
June 30, 2006 - 12pm Central time zone
Reviewer: Tim from PA, USA
Thanks. I pretty much feel like an idiot now...
:)
Although in my defense - two other people also looked at this - and we all missed it. I guess
sometimes you see what you want to see.
Again - thanks.
Views across database links
July 11, 2006 - 7pm Central time zone
Reviewer: Tim from PA, USA
Another followup on the database link if you don't mind.
Further up on this page you have suggested that it is your preference to use views to encapsulate
table via link reference (if I have read correctly).
I would like to be able to do this.
We have two database instances both on Oracle 9.2.0.4. Using your suggestion, I would like the
view to be stored once in a single schema and allow several user accounts to access the view (which
Oracle then goes across the link to get the data).
However, I am having a problem in getting this to work. The SQL used to create the links and
user/schema accounts is shown followed by a cut-n-paste from SQLPlus.
Could you please explain why I am getting the ORA-00942 / ORA-00942 error?
Thanks.
Create public database link QFN8DEV2
using 'QFN8DEV2';
Create public database link QFN8TST
using 'QFN8TST';
sc_schema and sc_user created in both local and remote database instances.
CREATE USER SC_SCHEMA
IDENTIFIED BY VALUES SQUIRREL42
DEFAULT TABLESPACE mytablespace
TEMPORARY TABLESPACE DBTEMP
ACCOUNT UNLOCK;
GRANT CREATE VIEW TO SC_SCHEMA;
GRANT CREATE TABLE TO SC_SCHEMA;
GRANT CREATE SESSION TO SC_SCHEMA;
GRANT CREATE SYNONYM TO SC_SCHEMA;
GRANT CREATE TRIGGER TO SC_SCHEMA;
GRANT CREATE SEQUENCE TO SC_SCHEMA;
GRANT CREATE PROCEDURE TO SC_SCHEMA;
ALTER USER SC_SCHEMA QUOTA UNLIMITED ON mytablespace;
CREATE USER SC_USER
IDENTIFIED BY VALUES MOUSE24
DEFAULT TABLESPACE mytablespace
TEMPORARY TABLESPACE DBTEMP
ACCOUNT UNLOCK;
GRANT CREATE SESSION TO SC_USER;
sc_schema@QFN8DEV2>
sc_schema@QFN8DEV2> @connect sc_schema@qfn8tst
Enter password: ********
Connected.
sc_schema@QFN8TST> create table t_foo4
2 as select * from all_objects
3 where rownum < 100;
Table created.
sc_schema@QFN8TST> grant select on t_foo4 to sc_user;
Grant succeeded.
sc_schema@QFN8TST> @connect sc_schema@qfn8dev2
Enter password: ********
Connected.
sc_schema@QFN8DEV2> create view v_foo4
2 as select * from t_foo4@qfn8tst;
View created.
sc_schema@QFN8DEV2> select count(*)
2 from v_foo4;
COUNT(*)
----------
99
1 row selected.
sc_schema@QFN8DEV2> grant select on v_foo4 to sc_user;
Grant succeeded.
sc_schema@QFN8DEV2> @connect sc_user@qfn8dev2;
Enter password: ********
Connected.
sc_user@QFN8DEV2> select count(*)
2 from sc_schema.t_foo4@qfn8tst;
COUNT(*)
----------
99
1 row selected.
sc_user@QFN8DEV2> select count(*)
2 from sc_schema.v_foo4;
from sc_schema.v_foo4
*
ERROR at line 2:
ORA-00942: table or view does not exist
ORA-00942: preceding line from QFN8TST
sc_user@QFN8DEV2>
Followup July 12, 2006 - 3pm Central time zone:
add the schema name to the view - not just t_foo4, sc_schema.t_foo4.
Views across database links
July 12, 2006 - 5pm Central time zone
Reviewer: Tim from PA, USA
Excellent !!!
Thanks - this will work very nicely for us. I don't understand why the schema name is necessary in
the view definition - but I am willing to roll with it. I appreciate your time on this.
:)
Followup July 12, 2006 - 5pm Central time zone:
a view is stored sql...
you are using "current user dblinks"
depending on WHO is logged in, that is WHO gets logged into the remote site. That is the "current
schema" over there.
select on user.table to diffuser on remote db
July 24, 2006 - 12pm Central time zone
Reviewer: jas
I want to grant select on user.table to diffuser on remote db.
how can I give that.
I dont want to grant it to public
Followup July 24, 2006 - 2pm Central time zone:
just grant it? not sure what you mean.....
just grant select on the table to the user(s) you want to have select on it.
it doesn't works
July 24, 2006 - 3pm Central time zone
Reviewer: A reader
error is no user or role present
Followup July 24, 2006 - 4pm Central time zone:
umm, context?
no idea what you mean. lay it all out. be specific, be precise. pretend you are trying to
explain your problem to your mom (whose theoretically doesn't work in technology)
oracle report
July 25, 2006 - 10am Central time zone
Reviewer: Alex smith
Hi tom,
I'm getting very strange problem. Actually i'm running my oracle report (10g) using application
server, report server. The report is originated from Jave script screens but instead of printing
'£' symbol it prints '$' sign. To get this sign I'm using following select stmt.
select to_char(1234,'L99,99') from dual;
could you please advise me what is going wrong?
Thank,
Alex Smith
Followup July 25, 2006 - 11am Central time zone:
nls settings, specifically - nls_currency.
it is set to $ apparently
ora-2054,ora-2068,ora-12571
August 7, 2006 - 9pm Central time zone
Reviewer: A reader
I don't know this is right place for my question!But I hope tom or someone help me to resolve it.
My question:
ServerA: table_name A
ServerB: table_name B
Database link ServerA to ServerB : a_b_link
Now I have a trigger.When I insert a row into A on ServerA,it automate insert it into B on
ServerB.But when ServerA to ServerB's network disconnect,it raise error
ora-2054,ora-2068,ora-12571,I want catch these error and handle it,but I don't kow hot to do it.
create or replace trigger a_ait
after insert on a
for each row
begin
insert into a@orcl_link values (:new.x,:new.y);
/*
How do I catch these exception because network disconnect here????
when ... then
insert into a_bak values (:new.x,:new.y);
*/
end;
Thanks in advance!!!!!
regard Tom-kyte.
Followup August 8, 2006 - 7am Central time zone:
not all exceptions are catchable.
if you use pragam exception_init and map those error codes, can you catch them?
nmgzw
August 10, 2006 - 4am Central time zone
Reviewer: nmgzw
thanks very much for your regard response!!!
I have try it.But it can't catch these error!
If I don't other method to catch it??
thanks!!!
Followup August 10, 2006 - 9am Central time zone:
not all are catchable.
the client will get this one.
August 10, 2006 - 8pm Central time zone
Reviewer: nmgzw
I understand your valuable answer.
thanks very much!!
tnsnames.ora
August 11, 2006 - 11am Central time zone
Reviewer: sam
Tom:
Is the file tnsnames.ora created per machine or database installed on machine?
I have one unix server that will have 3 instances/databases (A,B,X) on it. Can I have one link
"my_link" in database A pointing to a database "X" on same machine while the same link name
"my_link" ceated in Database B pointing to a database "X" on different machine on network ?
Thank you,
Followup August 11, 2006 - 12pm Central time zone:
it is a client thing, it most often is on a machine WITHOUT a database.
You can have one
You can have dozens
You can have zero
what would you like?
(ps: one unix server should have AT MOST one instance on it).
db links
August 11, 2006 - 3pm Central time zone
Reviewer: sam
Tom:
I am confused.
1. I thought everytime you install oracle server you will have one tnsnames.ora on the server
itself? Correct. I think when you have a plsql program that has a statement like;
select * from table@my_link;
then it looks in the tnsnames,ora to find out the server/database where that table is.
2. When you have a machine that wants to connect to the database, you install oracle client on
that machine and create also another tnsnames.ora file on the client. When the client like
SQL*plus or SQL*navigator tries to connect it also looks at the "service name" on the server
(tnsnames.ora) before it allows you in?Correct
3. I think you can have same link name in two databases that are pointing to different service
name with each pointing to different machine?Correct.
Followup August 11, 2006 - 3pm Central time zone:
1) you don't have to
the tnsnames.ora can be located centrally and discovered via the TNS_ADMIN environment variable (I
have installs of oracle from 7.3 on up on a single test machine - one tnsnames.ora in
/var/opt/oracle)
2) you don't have to (have a tnsnames). You could use the connect string directly, you can use a
directory service, you can use a network share for a single tnsnames.ora.
3) sure.
how can i posting question in AskTom.Com
August 15, 2006 - 10pm Central time zone
Reviewer: maxzsim from SINGAPORE
Hi ,
i want to post questions here but how can i do that ?
pls advise
tks & rdgs
Followup August 16, 2006 - 8am Central time zone:
wait until I'm taking them - see home page, it says when I'm not taking them (and why) and when I
am
Tom is the greatest!
August 17, 2006 - 10am Central time zone
Reviewer: Ben Buckley from Fort Worth, TX
Tom,
Your answers are complete, straightforward and accurate. You are the GREATEST!!!
Atypical problem
August 20, 2006 - 6am Central time zone
Reviewer: Arindam Mukherjee from Kolkata, India
Respected Mr. Tom,
Just now, I have got one problem of calling one function through Database link. SOMETIMES it takes
too LONG and sometimes it gives result too fast. Is it possible programmatically to wait for a
specific time frame say 20 seconds and after that if the result does not reach us, we cancel that
call of that function and the program goes along another route. Well, since my English is not
smart enough, I would like to portray it through pseudo code as follows.
Time t : = 0;
Number a := 0; /* Initialization*/
UNTIL t <= 20 sec.
{
Number a: = calling remote function through dblink.
}
If a != 0 then /* Remote Function call successful */
End if;
If a = 0 then /* 20 sec over but function calling is not successful */
End if;
Please help me. I am really in dire need of this solution. Kindly give me full executable code, not
psuedo code like me.
Followup August 20, 2006 - 8am Central time zone:
No there is not.
link
August 22, 2006 - 4pm Central time zone
Reviewer: sam
Tom:
I am trying to connect from a new 9i instance "ABCP" to an old 8i instance "ABCP".
DBA created this entry in the tnsnames.ora.
OLDABCP.company.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOTCOL = TCP)(HOST = www.company.com)(PORT = 1521))
)
(CONNECT_DATA = (SID = abcp)(SERVER = DEDICATED))
)
When I create a db link using that TNS service name, it gets created but when I use it it gives me
this error. Do you know what is happening.
1 create database link test
2 connect to abcadmin identified by abcadmin
3* using 'oldabcp'
SQL> /
Database link created.
SQL> select * from dual@test;
select * from dual@test
*
ERROR at line 1:
ORA-12538: TNS:no such protocol adapter
Followup August 27, 2006 - 3pm Central time zone:
try this:
using '(description=(address_list=(......';
and tell us if it reproduces, if not - the dba updated the tnsnames.ora file for you.
What about DB_LINKS which are not public?
September 5, 2006 - 2am Central time zone
Reviewer: A reader
Hi Tom,
I am presently trying to fine tune a package.
As it happens the package has a db_link defined in it which references a remote table.
The structure is as follows:
Normal User:
....
Package and Package Body
Database Link
.....
DBA: My ID
Now as it happens, i wish to see if the database link is active or not. But i do not want to
connect as the Normal User and then test the connection. Another way is to create a db_link similar
to that one and sfter testing it i can drop that. But that is not the idea. Also assume that i do
not know the password of that user.
I have the DBA privileges, in addition to that what other privs do i need.
Please advise,
Oracle Version 9i Release 7
HP-UNIX
Thanks as always
Followup September 5, 2006 - 4pm Central time zone:
I am not following you here. How would connecting as the "normal user" tell you if the link was
"active" - define "active" for me here.
Sorry for the confusion..
September 6, 2006 - 12am Central time zone
Reviewer: A reader
Hi Tom,
Sorry for the misinformation.
Let me be a bit more precise.
A user, say 'X' has a db_link in its schema called, "remote_db". Please note that this db_link is
not public.
Now, my user, which has the DBA privs, wishes to check the db_link is working.
So, my question is how do i check the db_link X.Remote_db.
Please bear in mind the following points:
1. I have no access to the remote db.
2. The Remote_db uses a connect to clause.
3. I do not know the pwd of the user X nor of the user defined in the connect to clause of the
db_link.
I tried using current_schema=X but, in vain.
Oracle 9i Rel 7
Please advise.
Followup September 6, 2006 - 7am Central time zone:
you will have to "become that user".
That is something the DBA can definitely do.
Glad to know that a DBA can...
September 7, 2006 - 1am Central time zone
Reviewer: A reader
Hi Tom,
I am relieved to know that a DBA can accomplish the same.
But a hint or an example would havein appropriate :)
I scoured thru the docs but could not find anything.
Thanks as always.
Sorry for the low Stars...
September 7, 2006 - 7am Central time zone
Reviewer: A reader
Hi Tom,
Couldn't help it.
I know of that approach. But in my prior post i had asked you if you know of any other approach.
As it is production i do not want to change the password and set it back.
Of course exp/imp is another way.
But way too hectic.
Can it be done like select * from <user>.table;
I want a similar approach..
Login as myself and check the db_link thats it.
As far as i know it is not possible. Or is IT?? :)
Followup September 7, 2006 - 8am Central time zone:
exp/imp isn't "another way"
I'm telling you the way, sorry you don't like it.
Copy data for a few tabes from one DB to the other
September 8, 2006 - 5pm Central time zone
Reviewer: A reader
Tom,
We are planning on putting a DBLink between 2 Oracle DB's. This DBlink will be used to get
customer and vendor data from DB1 to DB2. Triggers on DB1 will be used to copy data from DB1 to
DB2 (after update, insert, delete etc.). The number of customers/vendors that get created daily is
about 10000. Total no of tables where the triggers may have to be put in is about 10. Both the
DBs are in the same physical location.
I would like to know
1) If the DBLink or the trigger is going to put additional load on the source system (DB1) or
destination system (DB2). Additional as in slowing down perforamance noticably.
2) What is the best way to copy data in such situations?
Thank you
Followup September 9, 2006 - 12pm Central time zone:
that would be the wrongest way to do it.
please read about materialized views.
Copy data for a few tabes from one DB to the other
September 10, 2006 - 12am Central time zone
Reviewer: A reader
Thank you Tom - I will be reading about Materialized views. But the situations where the db links
are already present (data coming from db1 to db2 through triggers/dblinks).
1) Will the presence of a dblink impact the source system?
2) Is there a rule of thumb reg. using dblinks and Materialized views?
Thank you Tom for the valuable advice
Followup September 10, 2006 - 9am Central time zone:
1) only if you do something through the dblink, if you don't use it, it is just metadata and
doesn't do a thing
2) ROT is ROT (rule of thumb), meaning no, there are not ROT's that are universally applicable.
another ORA-02019 question
September 19, 2006 - 2pm Central time zone
Reviewer: Jim from Phila, PA
Hi Tom, I have a slightly different db link problem:
I have a user a who owns 2 db links, db1 and db2 and two views which point to b.t1 and c.t2 These
views are used as an access layer to the two tables b.t1 and c.t2 for developers
create view a.v1 as select * from b.t1@db1
create view a.v2 as select * from c.t2@db2
There is a developer, d who has select, insert,update and delete on a.v1. d can do the following:
select * from a.v1
select * from a.v2
insert into a.v1 values (...)
but d gets an ORA-02019 when he tries
insert into a.v1 select * from a.v2 where ....
how is db link db2 being evaluated?
the corporate wating line
September 20, 2006 - 2pm Central time zone
Reviewer: Jim from Phila, PA
I have put in a request to add the tns entries to the two servers in question, I expect that the
problem will be gone once that is done.
Missed you at SIG, had a query to tune ( 50 min down to 30 sec, thank you Cary!)
Thanks!
How to find the objects which created by db link?
September 25, 2006 - 5pm Central time zone
Reviewer: A reader from NY, USA
Tom,
If I created the table via database link as below, how can I know it was using database
link later? *_tables, *_objects has no db link information(only in *_synonyms). Please provide the
query or dictionary name.
AUCTIONET@anetdev > select DB_LINK ,host, USERNAME from user_db_links;
DB_LINK
--------------------------------------------------------------------------------
HOST
--------------------------------------------------------------------------------
USERNAME
------------------------------
AUCTIONET_MIRMVU
dev1
MIRMVU
AUCTIONET@anetdev > create table test_link as select * from MVU_EXCHANGE_RATES@AUCTIONET_MIRMVU
2 where 1=2;
Table created.
Thanks very much!
Followup September 26, 2006 - 2am Central time zone:
you cannot, it is not anything we would "remember".
Simple but firm answer
September 26, 2006 - 10am Central time zone
Reviewer: Reader from NY,USA
Tom,
Thanks for the response.This is the answer what I want to confirm from you. I could not find any
dictionary to get db link information in SUCH case above(if create synonym for remote database
object via db link, then we know the db link from *_synonyms). But it is not MY case. I thought
maybe there is some dictionary for my case, so I posted the above question. Now I am clear.
Followup September 26, 2006 - 3pm Central time zone:
well, just to clarify - because you confuse me here - the sql used in the create table as select...
is not stored...
however the dblinks and all are
but if you are looking for the original sql used in the create table as select, we don't have that
(well, auditing could have captured it, but not by default)
RE: Another ORA-02019 question
September 27, 2006 - 1pm Central time zone
Reviewer: Jim from Phila, PA
This is a followup on my previous question.
I have verified that the tns entries are on both remote servers. I am still getting the 02019 when
I insert into t1@d1 using select t2@d2.
The db link d2 does not exist on the db referenced by db link d1.
My original question still remains, where is db link d2 evaluated?
on the db referenced by db link d1, since that is where the select is done? if so do I have to
have db linnk d2 defined there?
it obviously is not done on the db where the insert statement is submitted because I can select
from t2@d2 on that database.
Followup September 27, 2006 - 4pm Central time zone:
the database link is resolved by the dedicated server that is "running" that query.
so the dedicated/shared server and its "environment" will resolve that dblink.
so, if you use a shared server (environment comes from whomever started database)
so, if you use a direct dedicated server (forked from YOUR process, no net)... (environment comes
from YOUR environment)
so, if you use a dedicated server from the listener - (environment comes from listener)
and TNS_ADMIN could be different for all three....
you could always use the (description=.....) instead of a tnsname connect string in the create
database link to remove any ambiguity.
Resultset via db-link versus DB2
October 5, 2006 - 9am Central time zone
Reviewer: Franco Perego from Italy
Hi Tom,
the DB2 administrator release a series of stored procedures in the DB2 database to access large
volume of data. The result of those procedure is a result set. Is there a way to call those
procedures from Oracle (10g2) using a db-link and read the result set?
Thanks in advance,
Franco Perego
Database Link and security
October 25, 2006 - 12pm Central time zone
Reviewer: MS from USA
Oracle Release 9206
global_names=true
Tom,
We have a development group that does the data updates in production.
My question is regarding methods to grant them this kinda temporary maintenance access on certain
production tables:
1. Grant the access through a link (password protected)...and let them do updates from the dev
database.
2. Create a maint account in production db..and let them do the maint. there..
3. Tom's way
What woud be your choice and why?
Thanks a bunch..and hope you are having fun at the open world..
Followup October 25, 2006 - 2pm Central time zone:
3) they develop the processes that are fully tested in development and then these processes are
installed and run in production by the appropriate individual - which may well be the DBA (might
not be the dba, that is a matter of policy)
I would not be giving access from the dev db to the real production instance.
requirement,
October 26, 2006 - 5pm Central time zone
Reviewer: A reader
I have a database DBS_DEV. Is there a way to know what all databases have a DB link to DBS_DEV
database?
thanks,
Followup October 27, 2006 - 7am Central time zone:
no, you would have to go to those databases and see if they have any links, there are no cross
database dependencies set up.
where it will fetch data
November 1, 2006 - 8am Central time zone
Reviewer: Parag J Patankar from India
Hi Tom,
Suppose I am 2 databases on two different servers, A and B. From B I have created db link to access
database on A.
Now suppose I have written a query on server b to access tables of server a using dblink where my
query will get processed ? Where will be db blocks of these two tables will be stored ? On Server A
or Server B ? Kindly explain.
thanks & regards
PJP
Followup November 1, 2006 - 8am Central time zone:
by default, B would parse and optimize this query, generate SQL to send to A to request data, A
would process it's bit of sql (caching it's blocks in its buffer cache" and return data to B, B
would (if needed) stash that information in your UGA/PGA (private memory) and process it further,
ultimately returning the data to you.
Error while accessing tables using DBLINKS
November 2, 2006 - 2am Central time zone
Reviewer: Gowrish from India
I have 2 Oracle database (10.2.0.1.0) running on two servers
and both have the same username and the passwords are identified externally
ie.,
Create user ops$graphix identified by externally.
I have created database link to connect to other sever
Create database link dblink using ‘secondary’;
When I select say
Select * from dual@secondary;
It throws the following error
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from EGSECONDARY
But I am able to connect to the remote by
SQL> /@secondary
In init.ora in remote database I have set the
remote_os_authent=true
os_authent_prefix=ops$
remote_login_passwordfile=Â’EXCLUSIVEÂ’ and also tried with = NONE
Please help.
Followup November 2, 2006 - 7am Central time zone:
you do not use OS authentication over the dblink (your oracle backend is not running with your
credentials after all)
please get rid of that os_authent - ouch (I think I'll just drop a machine into your network
running unix and create an account, yah, that's the ticket). turn that off NOW.
alter user ops$graphix identified by SOME_PASSWORD;
ops$graphix can still connect / locally
ops$graphix can identify themselves securely over the network (including dblinks)
Error while accessing tables using DBLINKS
November 2, 2006 - 7am Central time zone
Reviewer: Gowrish from India
Thanks a lot Tom for you inputs
long time to connect to the database
November 4, 2006 - 10am Central time zone
Reviewer: Dawar Naqvi from Los Angeles, CA USA
OS: IBM/AIX 5.2 Unix
DB: 10.2
Client: 10.2
Tools: SQLPLUS, Enterprise Manager Studio
Tom,
It takes long time to connect to the database. (50 sec)
Once its connect to the Database, its work fine.
we tested connection via client using tools such as SQLPLUS & Enterprise manager studio.
We have some other server (same as above) on the network running same DB version are fine.
And takes less time to connect to the database through client.
What should be check from Database site?
Any feed back will be appreciated.
cheers,
Dawar
Followup November 4, 2006 - 12pm Central time zone:
sounds like DNS problems could be here.
if you replace the hostname in your tns connect string with a static IP address, what then
connection to the DB
November 4, 2006 - 1pm Central time zone
Reviewer: Dawar Naqvi from Los Angeles, CA USA
Tom,
Here was my tnsnames entry from server.
SID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =fully-qualified-name)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sid)
)
)
Prior to changes, It took 61 sec to get connected.
So I repaced " fully-qualified-name" with IP address.
Then It took 30 sec to connect.
But we have one more server on same location.
sqlplus connection is right away. (normal)
Its using fully qualified name in tnsnames.ora at server site.
But its fine with that.
cheers,
Dawar
Followup November 4, 2006 - 2pm Central time zone:
might therefore be time to take a look at your network if simply using an IP address cut 50% of the
connect time - remove Oracle from the equation for a minute and make sure your networking is
networked right.
ORA-03106 Error with DBlinks
January 8, 2007 - 7am Central time zone
Reviewer: Karthik from Chennai,India
Hi Tom,
I have several procedures which connect to a different database through database link.I imported this dump in a new machine. I'm not able to compile only those procedures which use this dblink. All other procedures are compiling properly.SQL queries which use this DB link also run correctly.
For eg:
select * from emp@production
gives me no errors. When I use this query inside a procedure, this procedure doesnt compile and the error points to the DBlink.I get this error message
ORA-00604: error occurred at recursive SQL level 1
ORA-03106: fatal two-task communication protocol err
What could be the issue?
Thanks in Advance,
Karthik
Followup January 8, 2007 - 1pm Central time zone:
likely version related - please utilize support.
Comparing Views with Synonyms
January 30, 2007 - 12am Central time zone
Reviewer: Raj from India
I am currently working on a 8.1.7.4 database which has db links with 7.3.4 database. All the dblinks are running fine.
But the 8.1.7.4 database is now getting upgraded to 9.2.0.8 and so I am planning to use the 8.1.7.4 as the 'middle-man' between 9i and 7 databases.
Now, to fetch the data from 7 database which among synonyms or views is recommended to be created on the 8i database so that I can fetch the data from tables on 7 database into the 9i database more quickly and efficiently.
Followup January 31, 2007 - 1pm Central time zone:
I would test 9iR2 to 7.3 before I'd use the man in the middle approach (it was 10g that 7.3 was kaput).
It is less a question of efficiency (views/synonyms) - a synonym has a slight advantage in the parse phase. A view would import the meta data into the other database (which a synonym would not) which has advantages as well in some cases.
so, use either one (or just skip the 8i instance if at all possible)
Database Link with VPD and Application Context
January 30, 2007 - 8am Central time zone
Reviewer: Max
I'm facing strange problems with a certain type of insert via a public database link pointing from a local (9iR2 on WIN XP) to a remote database (9iR2 on AIX).
These inserts fail due to NO_DATA_FOUND exceptions within triggers on (remote) target tables.
The triggers do the following very simple lookup from a (remote) parent table before insert for each row:
select <MyCol1> into :NEW.<MyCol1>
from <MySchema>.<MyTab>
where <MyCol2> = :NEW.<MyCol2>
and <MyCol2> = :NEW.<MyCol3>
There are VPD policies in place for insert, update, delete, and select on both, source and target tables.
The database link uses a remote account with role DBA and additional "exempt access policy" system privilege.
It has been verified (by performing minus set operations) that both, local and remote parent table contain the same set of data.
<MySchema>.<MyTab>.<MyCol2> is feed via a column default which takes its value from an application context. It has been proven that this session context had been successfully initialized on the remote site from within the local site. The expected values could be read out from that context on the remote site from within the local site via the database link through dictionary views as well as with user-defined packaged functions. Furthermore a very similar insert accessing nothing but the local database (without a database link) succeeds and inserts rows that get a *NEW* value for <MySchema>.<MyTab>.<MyCol> (which is part of the primary key) taken from that session context. The only difference compared to the failing inserts using the database link is that the latter do NOT get a NEW value from the session context but the value that already exists within the local site (and is about to be copied to the remote site).
Can you imagine any reason(s) why such similar inserts via database link fail?
Followup January 31, 2007 - 12pm Central time zone:
insufficient data here - would need a test case to work with. sorry...
As for "Database Link with VPD and Application Context "
January 31, 2007 - 1am Central time zone
Reviewer: Max
I've found a call to sys_context( 'userenv', 'os_user' ) inside a policy function to return a *different* value when called via the database link (even if this points back to the same database).
I'm not quite sure whether one should have expected such behaviour ...
Followup January 31, 2007 - 10am Central time zone:
the OS_USER on the dblink would be different - it is a different OS user doing the work here - it is a server to server conversation - not a client to server
sys_context via db link (cont.)
February 3, 2007 - 9am Central time zone
Reviewer: Max
Hmmm, I'm not quite sure: Client and server reside on the same machine. I 've received my os-user's name in both cases but preceeded by domain(?) in one of them ...
Meanwhile I've opened a TAR (including the test case), but they haven't been able to reproduce the outcome so far ...
Performance related to db links
April 6, 2007 - 12pm Central time zone
Reviewer: Vikas Khanna from INDIA
Hi Tom,
I am desparately looking help from you and hope you will do to get this resolved.
We have a statement given below which used to take about an hr to complete and insert data in the denormalized table. Howver we have moved the denorm table on another dastabase and using db link trying to insert the data using the same sql and it is taking too long a time to complete.
It has already passed 4 hrs and still running. I have choosen the push process to get the data pushed so that the parallel processes and the has joins should function the same way they used to do in the single instance, It is at the time of insert it should go for serial execution.
Explain plan for INSERT /*+ PARALLEL(imp_denorm 16) */ INTO imp_clk_invrep_denorm imp_denorm
(
request_tag,
impression_tag,
click_tag,
impression_timestamp,
click_timestamp,
invalid_click_report_timestamp,
invalid_imp_report_timestamp,
account_id,
ad_campaign_id,
ad_id,
ad_creative_id,
ad_match_id,
ad_match_bid_id,
ad_position,
--ad_count,
ad_cost,
imp_rank,
publisher_id,
treatment_id,
channel_id,
request_count,
report_date,
created_date,
updated_date,
invalid_click_filter_id,
invalid_imp_filter_id,
imp_upd_flag,
clk_upd_flag,
inv_imp_upd_flag,
inv_clk_upd_flag,
dup_imp_flag,
invalid_imp_data_score,
invalid_clk_data_score
)
(
SELECT /*+ ORDERED PARALLEL(req 16) PARALLEL(imp 16) PARALLEL(clk 16) USE_HASH(imp clk) USE_HASH(req) */
imp.request_tag,
imp.impression_tag,
clk.click_tag,
FROM_TZ(imp.impression_timestamp,'UTC') AT TIME ZONE 'America/Los_angeles' AS impression_timestamp,
FROM_TZ(clk.click_timestamp,'UTC') AT TIME ZONE 'America/Los_angeles' AS click_timestamp,
NULL invalid_click_report_timestamp,
NULL invalid_imp_report_timestamp,
imp.account_id,
imp.ad_campaign_id,
imp.ad_id,
imp.ad_creative_id,
imp.ad_match_id,
imp.ad_match_bid_id,
imp.ad_position,
--imp.ad_count,
imp.ad_cost,
NULL imp_rank,
req.publisher_id,
req.treatment_id,
req.channel_id,
req.request_count,
TRUNC(FROM_TZ(imp.impression_timestamp,'UTC') AT TIME ZONE 'America/Los_angeles') report_date,
sysdate,
sysdate,
NULL invalid_click_filter_id,
NULL invalid_imp_filter_id,
1 imp_upd_flag,
(CASE WHEN click_tag IS NOT NULL THEN 1 ELSE NULL END) clk_upd_flag,
NULL inv_imp_upd_flag,
NULL inv_clk_upd_flag,
NULL dup_imp_flag,
NULL invalid_imp_data_score,
NULL invalid_clk_data_score
FROM
impression_data imp,
click_data clk,
request_data req
WHERE
imp.insert_date > to_date('23/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') AND imp.insert_date <= to_date('24/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') AND
req.insert_date > to_date('23/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss')-1/24 AND req.insert_date <= to_date('24/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') AND
clk.insert_date > to_date('23/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') AND clk.insert_date <= to_date('24/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') AND
imp.impression_tag = clk.impression_tag(+) AND
req.request_tag = imp.request_tag
UNION ALL
SELECT /*+ ORDERED PARALLEL(req 16) PARALLEL(imp 16) PARALLEL(clk 16) USE_HASH(clk imp) USE_HASH(req) */
imp.request_tag,
imp.impression_tag,
clk.click_tag,
FROM_TZ(imp.impression_timestamp,'UTC') AT TIME ZONE 'America/Los_angeles' AS impression_timestamp,
FROM_TZ(clk.click_timestamp,'UTC') AT TIME ZONE 'America/Los_angeles' AS click_timestamp,
NULL invalid_click_report_timestamp,
NULL invalid_imp_report_timestamp,
imp.account_id,
imp.ad_campaign_id,
imp.ad_id,
imp.ad_creative_id,
imp.ad_match_id,
imp.ad_match_bid_id,
imp.ad_position,
--imp.ad_count,
imp.ad_cost,
NULL imp_rank,
req.publisher_id,
req.treatment_id,
req.channel_id,
req.request_count,
TRUNC(FROM_TZ(imp.impression_timestamp,'UTC') AT TIME ZONE 'America/Los_angeles') report_date,
sysdate,
sysdate,
NULL invalid_click_filter_id,
NULL invalid_imp_filter_id,
1 imp_upd_flag,
(CASE WHEN click_tag IS NOT NULL THEN 1 ELSE NULL END) clk_upd_flag,
NULL inv_imp_upd_flag,
NULL inv_clk_upd_flag,
(CASE WHEN imp.insert_date <= to_date('23/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') THEN 1 ELSE NULL END) dup_imp_flag,
NULL invalid_imp_data_score,
NULL invalid_clk_data_score
FROM
click_data clk ,impression_data imp, request_data req
WHERE
clk.insert_date > to_date('23/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') AND clk.insert_date <= to_date('24/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') AND
imp.insert_date > to_date('23/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss')-4 AND imp.insert_date <= to_date('23/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') AND
req.insert_date > to_date('23/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss')-4 AND req.insert_date <= to_date('23/03/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') AND
imp.impression_tag = clk.impression_tag AND req.request_tag = imp.request_tag
);
dwuser@ADMW>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2794969881
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 13M| 2864M| | 216K (65)| 00:43:22 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10007 | | | | | | | | Q1,07 | P->S | QC (RAND) |
| 3 | BUFFER SORT | | 1048M| 129G| | | | | | Q1,07 | PCWP | |
| 4 | UNION-ALL | | | | | | | | | Q1,07 | PCWP | |
|* 5 | HASH JOIN | | 2754K| 567M| 44M| 78347 (2)| 00:15:41 | | | Q1,07 | PCWP | |
| 6 | PX RECEIVE | | 4728K| 658M| | 34351 (2)| 00:06:53 | | | Q1,07 | PCWP | |
| 7 | PX SEND HASH | :TQ10003 | 4728K| 658M| | 34351 (2)| 00:06:53 | | | Q1,03 | P->P | HASH |
| 8 | BUFFER SORT | | 1048M| 129G| | | | | | Q1,03 | PCWP | |
|* 9 | HASH JOIN | | 4728K| 658M| 357M| 34351 (2)| 00:06:53 | | | Q1,03 | PCWP | |
| 10 | PX RECEIVE | | 55M| 5092M| | 16404 (4)| 00:03:17 | | | Q1,03 | PCWP | |
| 11 | PX
Followup April 9, 2007 - 10am Central time zone:
this used to be a parallel direct path insert, it isn't when you introduced the dblink. It isn't that the hash join is "slower" probably, it is that the thing that fed off of the hash join (the insert) is
a) working over a dblink - not process to process local communication (network = not as fast)
b) not direct path anymore
c) not in parallel
April 25, 2007 - 4pm Central time zone
Reviewer: Alexander the ok
Hi Tom,
I need some ideas from you please for the following problem, we are out. We are using streams to keep a schema in sync between two sites. Unfortunately streams bombs often enough that we need a way to sync them back up again. I can use a merge over a db link that works beautifully however we have one table with CLOBs.
Since these are active sites, the only way I can think of to do this to shut them down and use imp/exp. What do you think? Are we out of luck? Thank you.
Who's using db link
May 7, 2007 - 1pm Central time zone
Reviewer: Dan from PA, USA
Hi Tom,
Is there way to know who's using a db link? Say, after a user issued two queries like "select sysdate from dual@db1" and "selet sysdate from daul@db2", Oracle kept the both link "session" open. How can we find out the user has these two "link sessions" open?
Thank you!
end-of-file on communication channel
June 11, 2007 - 12am Central time zone
Reviewer: mohammed from qatar
Hi Tom,
i have two database connected to each other but always im getting error
Errors in file /u01/app/oracle9i/admin/PROD/udump/prod1_reco_674046.trc:
ORA-02068: following severe error from CEN_LINK
ORA-03113: end-of-file on communication channel
but im lucky because still its working again it seems the connection get disconnect and connect again automatically
Thanks Alot
June 13, 2007 - 5am Central time zone
Reviewer: A reader
ORA-02068: following severe error from CEN_LINK
ORA-03113: end-of-file on communication channel
Followup June 13, 2007 - 8am Central time zone:
ora-3113, ora-600, ora-7445 => support.
OK
July 20, 2007 - 12am Central time zone
Reviewer: Kumar
Hi Tom,
When we use a Database Link to connect to a remote database,
Does it create a session in Target database?
If not,In target databases how we can identify the sessions that have connected remotely?
Thanks for your time.
Followup July 20, 2007 - 8am Central time zone:
yes, it creates a session.
monitor the db link
July 26, 2007 - 4am Central time zone
Reviewer: Daniel from CZE
hi tom, is it possible to monitor if the db link is currently ok?
can you help me to find the best solution how to do that?
thanks
daniel
Followup July 27, 2007 - 8am Central time zone:
define "ok"
Authentication for fixed user database link
August 8, 2007 - 11am Central time zone
Reviewer: Raza from Canada
Hi Tom,
Can you please explain, how the authentication for fixed user database link occurs. More precisely
I am interested in knowing if the userid and password are sent in clear text over the network ?
I would appreciate if you point me out to any documentation that talks about this in detail.
Thanks
Followup August 14, 2007 - 10am Central time zone:
passwords are not sent in the clear over the network
and if you use ASO (advanced security) all network traffic may be encrypted if you wish.
passwords have been encrypted by the transmitting client for a decade or more...
Create Db link with as sysdba for sys user in standby db
August 9, 2007 - 4pm Central time zone
Reviewer: A reader
Hi, DB verion 9208
How do I create a Database link to connect to sys as sysdba to a remote db.
create database link connect to "sys as sysdba" identified
by password using 'host'
I tried this. Link gets created but it gives error
oracle inittialization or shutdown in progress.
I am trying to connect to a stand by DB. I believe for a Standby DB , only connections as sysdba
are permitted hence the above error.
If I use sqlplus and directly connect sys as sysdba it works fine.
How to get the link working ?
Info across Db-links
August 10, 2007 - 8am Central time zone
Reviewer: martina from vienna, austria
Hi Tom,
What possibility do we have to get information across db-links? something like
dbms_application_info.set_client_info (did not work). can i find out something with sys_context?
Too many people (not users, not os-users) are retrieving using db_links and thus we would like to
use any Info with FGAC at the remote site. i would also be thankful for some documentation-link for
me to read!
thank you in advance!
martina
Followup August 14, 2007 - 11am Central time zone:
the remote database would be reliant on the local database to have SENT this information over the link. The local database that opens the connection should invoke dbms_application_info@remote_site in order to set these values.
Info across DB-Link
August 14, 2007 - 11am Central time zone
Reviewer: martina from vienna, austria
Thank you, that works great!
Do you have an idea how i could automate this thing - something like a
"before_opening_db_link"-trigger?
thanks,
martina
Followup August 20, 2007 - 12pm Central time zone:
stored procedures :)
there is no such a triggering event - your local session would need to invoke something.
About remote connection to Database
August 15, 2007 - 1pm Central time zone
Reviewer: xgla from USA
Hi Tom, your followup are very usuful for me.
I have a problem. I want to connect my DB from a remote pc in other network.
I install Net Service and I configured my tnsname.ora.
I put in host name my static Ip where is open the port 1521 in my server. But I get the error
TNS-12535 - TNS- Operation time out.
My server is dedicated, I have only one listener, one database. I traced my listener in the server
and the conecctin is establish, and I treced the cliente too, I got the tns-12535, 12560, 505, and
60 error.
My question is :
There are other parameter that I have configurated in order to connect my DB remotely ?
Thank in advanced.
Followup August 20, 2007 - 7pm Central time zone:
would there happen to be a big old firewall in the way
About remote connection to Database
August 28, 2007 - 9am Central time zone
Reviewer: xgla from USA
Yes, I have a good firewall, a watchguard firebox, but I have open the 1521 port. I noted that
listener give other random port to process.
How to do in this case ?
Thank you so much.
Followup September 4, 2007 - 1pm Central time zone:
tell us more about how you currently have this database setup, are you trying to use shared server? what operating system.
pass login/password while invoking dblink?
September 19, 2007 - 4am Central time zone
Reviewer: nairs
If I create a db link without the connect to clause (create database link db1 using 'mydb';) and if
the user on the linked target database is different from the one on the source database, it gives
the invalid username/password error.
Apart from modifying the db link name with 'connect to..identified by..', is there a way to give
the username and password while invoking the dblink? (something like select * from
dual@db1@user/pwd?)
Followup September 19, 2007 - 12pm Central time zone:
no, there is not.
DB Links
October 23, 2007 - 2pm Central time zone
Reviewer: Rajesh from Hartford,CT
Hi Tom,
I have a problem in DB Links. I have 2 databases A and B.
Both are linked each other by DB Links. In A, we have two schemas S1 and S2. A procedure in S1
updates a table in S2.
Grants,synonyms and everything are all good between S1 and S2. Now if we call the procedure in A
from database B, I get Invalid Username/Password showing the error in the update statement.
If the table is in the same schema S1, it works.
If the table is in S2 schema, it doesnt work.
The DB link is created between A and B through S1. Should I have to create another DB Link between
A and B through S2 ?
Can you help me in this ?
Thanks
Rajesh
Followup October 23, 2007 - 6pm Central time zone:
how could a STORED PROCEDURE call give an "update error"
you have not described this very well.
there is nothing to comment on.
DB Links
October 23, 2007 - 8pm Central time zone
Reviewer: Rajesh from Hartford,CT
Hi Tom,
When the procedure is invoked from the different database B, the statements within the proc starts
executing and when the update stmt on a table of another schema is executed, I get the error.
It may be because of when an another schema's (S2 of same database A) table is to be updated, it
requires the username/password to execute the update from a different database B though it has a DB
link with database A (with schema S1).
Should I also create a similar DB link (like database B and schema S1 of A) for database B along
with the schema S2 of database A which has the table ?
Thanks
Rajesh
Followup October 24, 2007 - 8am Central time zone:
without an example, without the ACTUAL ERROR MESSAGE, I cannot comment.
It should not matter if the code is executed via a dblink.
ORA-02064
October 24, 2007 - 4pm Central time zone
Reviewer: Anne from MN, USA
Hi Tom,
I have 2 databases A and B. A is 9.2.0.4 and B is 11i Ebiz 9.2.0.6 RDBMS.
Package in A calls package in B (which has out and commit) over a dblink => I get 02064 error.
I am not sure how to resolve this, because I cannot take the commit out of pkg in B.
Basically what pkg in A does is calculates billable dollars in A and creates invoices in B :
1. Does a bunch of calculations to create Invoices for billable projects
2. Inserts into tables in A
3. Inserts into staging tables in B
4. Commit in A (so data is committed in B from step 3)
5. Calls create_11i_invoice@B to create invoices from the staging tables in B. create_11i_invoice
is a generic pkg in B that creates invoices using the 11i api's, so I cannot take off the commits
from there.
Could you please advise on how I could do this ? Thanks so much and appreciate your help!
Followup October 24, 2007 - 4pm Central time zone:
you cannot do transactional control over a dblink - the remote site isn't allowed to commit (the invoking site has to sort of make that decision, they are the only one that knows what all is involved in actually committing - it could be many other sites - that one remote site has no clue)
you'll need to change the process - perhaps you schedule a JOB in "b" and when you call dbms_job@remote and schedule and commit it - it'll run FROM b shortly after.
and then B is the "coordinating site" and B can commit
ORA-02064
October 24, 2007 - 4pm Central time zone
Reviewer: Anne from MN, USA
Thanks so much, Tom, excellent idea!
Couple of questions :
1. In "B", job_queue_processes = 10.job_queue_interval is not set. Is this ok for dbms_job to function ?
2. Schedule a job in B :
procedure schedule_job_create_inv
as
l_job number;
l_errbuf varchar2(100);
l_retcode number;
g_org_id number := 108;
begin
dbms_job.submit(l_job, 'apps.create_11i_invoice_pkg.create_11i_invoice(l_errbuf, l_retcode,
g_org_id);');
end;
/
Could you please confirm if the above is correct ?
3. Call dbms_job@remote and schedule and commit
In pkg :
dbms_job.run(job_number)@B;
commit;
I am not sure if this is correct. Please advise.
Once again thanks so much for your help. Much appreciated and very valuable!
Followup October 25, 2007 - 5pm Central time zone:
1) job queue interval is a hidden parameter these days, do not set it.
2) you'd want to run that block of code ON B, yes. Either by calling dbms_job@remote or by having this procedure schedule job create inv residing on B
3) no, you submit the job remotely. When you COMMIT, it runs - on B - not distributed - and can commit.
it looks like you have "in out parameters" to your procedure (your job), that won't work. especially the OUT parameters
we are queueing a job to be executed - after we commit - in some other session. You'll use a table to get inputs/outputs to and from this job.
Not sure that I understand
October 26, 2007 - 11am Central time zone
Reviewer: Anne from MN, USA
Thanks so much, Tom, but I am not sure that I understand this completely...
Could you please help and thanks for your patience...
From what I understand, I have to create this proc sched_create_inv on B.
Then on A, call sched_create_inv@remoteB and commit in A => This will run the proc sched_create_inv
on B and in turn run the create_11i_pkg on B.
Am I following you so far ?
I lost you completely on "You'll use a table to get inputs/outputs to and from this job"... Could
you help.
Thanks so much.
Followup October 29, 2007 - 11am Central time zone:
a) this stuff would exist on "B"
create table create_inv_parameters( job number primary key, errm varchar2(100), retcode number,
org_id number );
procedure schedule_job_create_inv( p_org_id in number )
as
l_job number;
begin
dbms_job.submit(l_job,'apps.create_11i_invoice_pkg.create_11i_invoice(JOB);');
insert into create_inv_parameters( job, org_id ) values ( l_job, p_org_id );
end;
/
Your procedure create_11i_invoice would start with:
.... ( p_job in number ) /* only parameter, the JOB ID */
as
...
l_rec create_inv_parameters%rowtype;
begin
select * into l_rec from create_inv_parameters where job = p_job;
.... process, l_rec.org_id has your org id
update create_inv_parameters set errm = l_errm, retcode = l_retcode
where job = p_job;
end;
/
b) from A, you call
schedule_job_create_inv@B( l_org_id );
commit; <<== that permits the job to run asynchronously on the remote site
c) later, you can query create_inv_parameters@b to see how the job did
dbms_job
October 30, 2007 - 4pm Central time zone
Reviewer: Anne from MN, USA
Thank you so much Tom - that was extrememly helpful - it works great!
Database link in cursor
November 22, 2007 - 3pm Central time zone
Reviewer: Czesiu from POLAND
Hi Tom,
I try to get user table names from remote database and then question them about number of rows, so
I use:
DECLARE
counter NUMBER;
sql_stmt VARCHAR2(500);
CURSOR tables IS
SELECT table_name FROM user_tables@SVT;
BEGIN
FOR tab IN tables LOOP
sql_stmt := 'SELECT COUNT(*) FROM '||tab.table_name||'@SVT';
EXECUTE IMMEDIATE (sql_stmt) INTO counter;
DBMS_OUTPUT.PUT_LINE(tab.table_name||' '||counter);
END LOOP;
END;
All the time I encounter ORA-00903 error: 'Invalid table name'. Did i make any mistake? Isn't that
Pl/SQL code valid?
Thanks & Regards
Followup November 26, 2007 - 11am Central time zone:
why not just:
select table_name, num_rows from user_tables@svt;
presuming of course that they have valid statistics, it would give you a good guesstimate.
ops$tkyte%ORA10GR2> DECLARE
2 counter NUMBER;
3 sql_stmt VARCHAR2(500);
4 CURSOR tables IS
5 SELECT table_name FROM user_tables@ora10gr2@loopback;
6 BEGIN
7 FOR tab IN tables LOOP
8 sql_stmt := 'SELECT COUNT(*) FROM '||tab.table_name||'@ora10gr2@loopback';
9 EXECUTE IMMEDIATE (sql_stmt) INTO counter;
10 DBMS_OUTPUT.PUT_LINE(tab.table_name||' '||counter);
11 END LOOP;
12 END;
13 /
T_HEAP 10000
T 50301
TEST2 3
TEST1 5
T1 35
EMP 2
T2 35
T_HASH 10000
PL/SQL procedure successfully completed.
code does "function" however - maybe you want to try once:
ops$tkyte%ORA10GR2> DECLARE
2 counter NUMBER;
3 sql_stmt VARCHAR2(500);
4 CURSOR tables IS
5 SELECT table_name FROM user_tables@ora10gr2@loopback;
6 BEGIN
7 FOR tab IN tables LOOP
8 sql_stmt := 'SELECT COUNT(*) FROM '||tab.table_name||'@ora10gr2@loopback';
9 DBMS_OUTPUT.PUT_LINE(sql_stmt);
10 EXECUTE IMMEDIATE (sql_stmt) INTO counter;
11 DBMS_OUTPUT.PUT_LINE(tab.table_name||' '||counter);
12 END LOOP;
13 END;
14 /
SELECT COUNT(*) FROM T_HEAP@ora10gr2@loopback
T_HEAP 10000
SELECT COUNT(*) FROM T@ora10gr2@loopback
T 50301
SELECT COUNT(*) FROM TEST2@ora10gr2@loopback
TEST2 3
SELECT COUNT(*) FROM TEST1@ora10gr2@loopback
TEST1 5
SELECT COUNT(*) FROM T1@ora10gr2@loopback
T1 35
SELECT COUNT(*) FROM EMP@ora10gr2@loopback
EMP 2
SELECT COUNT(*) FROM T2@ora10gr2@loopback
T2 35
SELECT COUNT(*) FROM T_HASH@ora10gr2@loopback
T_HASH 10000
PL/SQL procedure successfully completed.
and what you see might become "obvious" - eg: I can make this break easily:
ops$tkyte%ORA10GR2> create table "hahahahaha" (x int);
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> DECLARE
2 counter NUMBER;
3 sql_stmt VARCHAR2(500);
4 CURSOR tables IS
5 SELECT table_name FROM user_tables@ora10gr2@loopback;
6 BEGIN
7 FOR tab IN tables LOOP
8 sql_stmt := 'SELECT COUNT(*) FROM '||tab.table_name||'@ora10gr2@loopback';
9 DBMS_OUTPUT.PUT_LINE(sql_stmt);
10 EXECUTE IMMEDIATE (sql_stmt) INTO counter;
11 DBMS_OUTPUT.PUT_LINE(tab.table_name||' '||counter);
12 END LOOP;
13 END;
14 /
SELECT COUNT(*) FROM hahahahaha@ora10gr2@loopback
DECLARE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from ORA10GR2@LOOPBACK
ORA-06512: at line 10
which would be corrected by doing this:
ops$tkyte%ORA10GR2> DECLARE
2 counter NUMBER;
3 sql_stmt VARCHAR2(500);
4 CURSOR tables IS
5 SELECT '"' || table_name || '"' table_name FROM user_tables@ora10gr2@loopback;
6 BEGIN
7 FOR tab IN tables LOOP
8 sql_stmt := 'SELECT COUNT(*) FROM '||tab.table_name||'@ora10gr2@loopback';
9 DBMS_OUTPUT.PUT_LINE(sql_stmt);
10 EXECUTE IMMEDIATE (sql_stmt) INTO counter;
11 DBMS_OUTPUT.PUT_LINE(tab.table_name||' '||counter);
12 END LOOP;
13 END;
14 /
SELECT COUNT(*) FROM "hahahahaha"@ora10gr2@loopback
"hahahahaha" 0
SELECT COUNT(*) FROM "T_HEAP"@ora10gr2@loopback
"T_HEAP" 10000
SELECT COUNT(*) FROM "T"@ora10gr2@loopback
"T" 50301
SELECT COUNT(*) FROM "TEST2"@ora10gr2@loopback
"TEST2" 3
SELECT COUNT(*) FROM "TEST1"@ora10gr2@loopback
"TEST1" 5
SELECT COUNT(*) FROM "t"@ora10gr2@loopback
"t" 0
SELECT COUNT(*) FROM "T1"@ora10gr2@loopback
"T1" 35
SELECT COUNT(*) FROM "EMP"@ora10gr2@loopback
"EMP" 2
SELECT COUNT(*) FROM "T2"@ora10gr2@loopback
"T2" 35
SELECT COUNT(*) FROM "T_HASH"@ora10gr2@loopback
"T_HASH" 10000
PL/SQL procedure successfully completed.
database links
November 24, 2007 - 3am Central time zone
Reviewer: A reader
hai tom,
How can i see the changes in remote database table after the trigger is fired in a local
database?.i could see the changes in local database ,but not on remote database...
here is the example wat i have did..
1) ::TO CREATE A DATABASE LINK IN SCOTT,N CREATING LINK TO WB(ANOTHERDATABASE)::
create database link dblinkwbh
connect to WB
identified by wb
using 'WB';
Database link created.
2)THEN I HAVE CREATED A TRIGGER IN SCOTT FOR INSERTING :
CREATE OR REPLACE TRIGGER TG_AFTER_INSERT
AFTER INSERT
ON test_dblink
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
pro_test1(:new.bacno,:new.name);
end;
/
TRIGGER CREATED .
create or replace procedure pro_test1(p_bacno number,p_name varchar2)
as
begin
insert into wbh@dblinkwbh(mem_bacno,mem_name)
values(p_bacno,p_name);
exception when others then Rollback;
commit;
end;
PROCEDURE CREATED.
3)WHEN I INSERT DATA IN TABLE test_dblink ... THE TRIGGER IS FIRED N PROCEDURE IS ALSO
EXECUTED..AND I CAN SEE THE CHANGES IN LOCAL DATABASE ..
SELECT * FROM wbh@dblinkwbh;--COUNT IS INCREASED..
BUT WHEN I CONNECT TO WB DATABASE ..
I CANNOT SEE THE CHANGES IN WBH TABLE..
SELECT * FROM WBH;--NO CHANGES..I HAVE COMMITED IN THE DATABASE ALSO ..I CANT SEE THE CHANGES
CAN I KNOW THE REASON Y ???
THANKQ IN ADVANCE TOM
Followup November 26, 2007 - 11am Central time zone:
...
create or replace procedure pro_test1(p_bacno number,p_name varchar2)
as
begin
insert into wbh@dblinkwbh(mem_bacno,mem_name)
values(p_bacno,p_name);
exception when others then Rollback;
commit;
end;
...
good gosh - do you know what that does? that exception block??????????
ARG, I'm starting to give up.
If you hit any error, then - you silently rollback (which fortunately will fail since you are in a trigger anyway). You SILENTLY rollback. You SILENTLY AND IN AN UNDETECTABLE FASHION rollback. And then you commit. (that commit is part of the exception block)
It gets frustrating look at 'error handling' code that does so much serious damage like this.
so, the reason you cannot 'see' your changes should be obvious.
You create a new session on the remote site, it is not visible to that session until YOU COMMIT.
and the commit does not, will not, cannot be in the procedure.
Please - I suggest you look at every single exception block in your code and if you have any that
a) rolllback
b) commit
have someone that really knows what transactions are/mean look at your code.
also, if you have ANY instances of:
when others
then .....
and there IS NOT a "raise" or "raise_application_error" there - comment them out - they are the most dangerous bits of code. They must be removed.
committing updates over a link
December 7, 2007 - 8am Central time zone
Reviewer: Dave Martin from The Hague, Holland
Tom,
are..
update blah@yyyy set xxx='fgh';
commit;
..and..
call update_blah@yyy('fgh');
commit;
..the same, assuming update_blah is a procedure on the remote database which simply updates blah
and exits without a commit?
Recommended Uses for DB Links
December 10, 2007 - 12pm Central time zone
Reviewer: Steve Landy from East Hartford, CT
Tom,
Regarding DB Links, we have a fairly diverse shared database environment existing between 4
different data centers in 4 different locations (2 of which are out of state). Quite frequently we
get requests for DB Links to be setup between databases to pass data around (mostly batch type of
jobs that run daily). Do you have any other recommended alternatives other than DB Links to serve
the same type of purpose. We would like to keep the dependancy between databases limited
(especially across the WAN). Any suggestions would be greatly appreciated. Also, what would you
recommend as "best practices" for DB Links?
Followup December 10, 2007 - 1pm Central time zone:
Well, database links are the easiest way for a plsql routine to reach out and get data from another database.
how does it make a database "dependent" on another database? I can see how it makes a PROCESS dependent on two databases - but the databases themselves don't really care or use the dblinks?
Recommended Uses for DB Links
December 10, 2007 - 3pm Central time zone
Reviewer: Steve Landy from East Hartford, CT
Well in that case it becomes a supportability/maintainability concern. If one DB needs to be taken
down/upgraded/etc. and another DB has a DB Link association with that DB process (251 DBs in our
environment) it becomes a maintenance nightmare to coordinate application teams and so forth to
make sure there aren't any processes consuming data from the DB being maintenanced. We just
recently got a request to have DB Links setup from one 1 Devel DB to 8 Prod DBs. In your, opinion
do you think this is a moot point and isn't a concern from a supportability/maintainability
standpoint? Would you recommend using DB Links as the "best practice" medium for exchanging data
between disparate databases as apposed to other alternatives (especially across the WAN)?
Followup December 10, 2007 - 4pm Central time zone:
that will be true regardless of what "technology" you use.
period. remove the database link, introduce "X".
when the database isn't up, it cannot produce "X"
when the database isn't up, it cannot consume "X"
I don't care what integration technique you use - you have 251 points of conversation. Say you introduce some middleware, now you have way more than 251 points of conversation.
What I would focus on is getting from 251 database instance to - less.
Now that said, a development database should not have links to production - that just isn't smart.
Production to production - sure.
to development from production? *maybe* (because production is very controlled)
from development to production? I hesitate to say "never", but "pretty much never".
January 4, 2008 - 5pm Central time zone
Reviewer: A reader
We have two databases A and B (version 9.0.2) running on different unix machines.
There is a database link created on A to refer tables in B.
Our front end application which is connected to A, reports error for database link with message
code e.g. "PLS-00907 cannot load library unit ", but when we try to look into database error logs,
we don't see any error logged.
Is there a way to find out whether the issue is with Database link or with Unix box and networking
?
Followup January 4, 2008 - 5pm Central time zone:
it won't be the unix box or networking - I cannot think of a single reason why it would be.
a pls error like that would not be "logged" anywhere - the application would get it and deal with it.
Now, you should be able to remove your application from the equation (just log into sqlplus and do the same thing - it should reproduce)
and from there, we'll want to whittle down the remote procedure to the smallest thing it can be in order to reproduce...
January 7, 2008 - 5pm Central time zone
Reviewer: A reader
Hi Tom,
Thanks for information on PLS error logging.
To reproduce the error, as you mentioned, We took one of the SQL statement which refers to remote
database out and ran it couple of time, but it did not error out.
We checked the application log and found that it is happening some random time. To mimic the way
application being use, we ran the single SQL from sql plus through a loop for full day, but could
not reproduce it.
Followup January 7, 2008 - 5pm Central time zone:
you'll want to utilize support - they can set an event that can capture additional information when the error occurs.
January 8, 2008 - 5pm Central time zone
Reviewer: A reader
Thanks Tom.
We will do as you said and try to find some more information.
get data from 11g in 10g through db link
January 11, 2008 - 4pm Central time zone
Reviewer: Sean from NJ, USA
Hi Tom,
I am not able to get data from 11g through 10g (10.2.0.3) database by using db link. Here is the
screen shot. Is this the known behavior? Thanks so much. -- Sean
SQL> create database link nd30 connect to fdw identified by nd30 using 'nd30';
Database link created.
SQL> select sysdate from dual@nd30;
select sysdate from dual@nd30
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from ND30
SQL> connect fdw/nd30@nd30
Connected.
connect to 11g from 10 through db link
January 16, 2008 - 11am Central time zone
Reviewer: sean from NJ, USA
Hi Tom,
Oracle support helped me solve the problem. In 11g, password is case sensitive, one needs to
create db link like this:
create database link db11g connect to scott identified by "tiger" using 'db11g';
Thanks. -- Sean
password for db links
January 22, 2008 - 4am Central time zone
Reviewer: Satya Prasad Moparthy from Bangalore
Hi Tom,
We have upgraded our databases to 8.1 to 10.2, and i used to refer sys.link$ view to check for the
passwords of all the db links. But, i observed that this view does not contain the passwords
anymore, where i can get the passwords for the db links and why it was removed from link$ view in
version 10.
Followup January 22, 2008 - 7am Central time zone:
you won't.
that was the point of removing them.
and reason 21143242 why you should not query sys.* tables directly, they are ours and subject to massive change without warning.
dblink not working from stored procedure
February 12, 2008 - 8pm Central time zone
Reviewer: Carlos from Bellevue, WA
Hi Tom,
Why is that a dblink works fine in independent queries but when I take the exact same query to
create a cursor inside a procedure, I get the msg: "table or view not found". Thanks
Followup February 13, 2008 - 12pm Central time zone:
you ask if you can bind the order by ordinal - answer is NO, you cannot
but your example, which you seem to think is using a bind for that, is NOT using a bind.. You are using concatenation.
scott@test>create or replace procedure myproc (x out sys_refcursor,dep
tno int,y int default 1)
2 as
3 begin
4 open x for 'Select * from emp where deptno = :dno order by '||y using
deptn
o;
5 end;
6 /
you build a query like:
select * from emp where deptno = :dno order by 1
and then bind in the :dno, but not the ordinal.
Right Answer to Wrong Question?
February 13, 2008 - 1pm Central time zone
Reviewer: Greg from Reston (you know where that is)
Tom, I think that you were answering *this* question:
using by bind in order by condition February 12, 2008 - 10pm US/Eastern
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2320123769177#664602900346116237
Sorry but my question was other...
February 13, 2008 - 7pm Central time zone
Reviewer: Carlos from Seattle, USA
Here goes again Tom:
Why is that a dblink works fine in independent queries but when I take the exact same query to
create a cursor inside a procedure, I get the msg: "table or view not found". Thanks
Followup February 13, 2008 - 11pm Central time zone:
give me an example, tell us how the dblink is created (i'm guessing you do not have the user/pass in it, and when the procedure is run, it uses the credentials of the currently logged in user)
In order to answer you - we need *details*
an example would rock...
for example, consider what happens to scott, depending on how dblink is created:
ops$tkyte%ORA10GR2> create user a identified by a default tablespace users quota unlimited on
users;
User created.
ops$tkyte%ORA10GR2> create user b identified by b;
User created.
ops$tkyte%ORA10GR2> grant create session, create table to a;
Grant succeeded.
ops$tkyte%ORA10GR2> grant create session, create procedure, create database link to b;
Grant succeeded.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> create table t as select * from dual;
Table created.
a%ORA10GR2> grant select on t to b;
Grant succeeded.
a%ORA10GR2>
a%ORA10GR2> connect b/b
Connected.
b%ORA10GR2> create database link ora10gr2@loopback using 'ora10gr2';
Database link created.
b%ORA10GR2> select * from a.t@ora10gr2@loopback;
D
-
X
b%ORA10GR2> begin
2 for x in (select * from a.t@ora10gr2@loopback)
3 loop
4 null;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
b%ORA10GR2> create or replace procedure p
2 as
3 begin
4 for x in (select * from a.t@ora10gr2@loopback)
5 loop
6 dbms_output.put_line( 'row = ' || x.dummy );
7 end loop;
8 end;
9 /
Procedure created.
b%ORA10GR2> exec p
row = X
PL/SQL procedure successfully completed.
b%ORA10GR2> grant execute on p to scott;
Grant succeeded.
b%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> exec b.p
BEGIN b.p; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from ORA10GR2@LOOPBACK
ORA-06512: at "B.P", line 4
ORA-06512: at line 1
scott%ORA10GR2> connect b/b
Connected.
b%ORA10GR2> drop database link ora10gr2@loopback;
Database link dropped.
b%ORA10GR2> create database link ora10gr2@loopback connect to b identified by b using 'ora10gr2';
Database link created.
b%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> exec b.p
row = X
PL/SQL procedure successfully completed.
Many Thanks!!!
February 14, 2008 - 1pm Central time zone
Reviewer: Carlos from Seattle, USA
Your example was suffice to realize my issue. Thanks, you are an INTERGALACTIC MASTER!!!
Remote databse objects
February 20, 2008 - 4am Central time zone
Reviewer: Sachin from india
Hi Tom,
Here ,I just want to clear one doubts and i know you can give me suitable answer.
My Question is, if i create synonym based on remote database's objects(Table) and use that
synonyms.
and if same thing we do, creating view based same remote database objects and use that in our code.
Just let me know , there are any performance issue with
any of above.
Thanks!!!
Followup February 20, 2008 - 9am Central time zone:
the main difference between the synonym and the view is that the view will import the metadata into the local instance (user_views will be populated, the column names will be pulled over, their types will be pulled as well)
compilation of that procedure will be easier
you can describe the objects referenced by the procedure (they are in the dictionary)
for this reason alone, I prefer to use views - now the view is dependent on the dblink, not the procedure itself (the procedure could actually compile without doing the over the network processing)
February 22, 2008 - 2am Central time zone
Reviewer: Sachin from india
means, its just reduse user dependencies...
you are Great!!!
How database links work
February 27, 2008 - 2am Central time zone
Reviewer: Tengis from UB, Mongolia
Hello Tom, i read Oracle Administrator Guide. There are contents contained "what are database
links" or "How to create database link". But i want to know more about dblinks. For example, what
happened to open dblink when network failed. Could you please tell me how database links work?
Followup February 27, 2008 - 2am Central time zone:
they are just network connections - one database is the client of the other.
if the network fails, the link will "fail" when it is detected, eg: you'll get an error.
selecting clob over a dblink
March 12, 2008 - 10am Central time zone
Reviewer: Michal Pravda from Prague
Hello,
I need to copy data (one way synchronize ideally but not necessary) from table@db1 to table@db2.
Dblink exists and works but I can't use it (at least directly) because the table contains two
CLOBs. Could you suggest a workaround to ORA-22992 - Cannot use lob locators selected from remote
tables?
Followup March 12, 2008 - 5pm Central time zone:
lobs work over dblinks - are you trying to do things slow by slow perhaps? If you use a nice big bulky single sql statement and erase all of your procedural code, you'll be amazed....
clob copying
March 13, 2008 - 1am Central time zone
Reviewer: Michal Pravda from Prague
Hi,
I feel like an idiot :(. I haven't actually tried what I asked for.
I was trying to "select" clob over a dblink yesterday which didn't work. After your suggestion a I
just tried an "insert select" and that worked. I had a prejudice "no select" then "no insert
(select)".
Thank you.
db links
March 29, 2008 - 10am Central time zone
Reviewer: balakrishna from India.Bangalore
Hi Tom ,
I just want to know if some one has logged into my database using db links , how do i trace this
session .can you please help me out.
Regards
Balakrishna
Followup March 31, 2008 - 8am Central time zone:
if you select program from v$process, for a dedicated connection, you would see
oracle$ORACLE_SID@$hostname
for the remote connection program name (oracle_sid is the sid of the local database, hostname is the hostname of the local database)
There really isn't that much of a difference between a dblink logon and a 'regular' logon, they are both 'regular'
dblink
April 2, 2008 - 12pm Central time zone
Reviewer: Sam from NJ
Tom, I have question regarding DBLINK.
If I am using a dblibk to query a remote table and using order by or window functions in the query
then whose tempory segment ( temp tablespace)it will use . Remote databse where table resides ? or
the schema from where I am executing the query ?
Followup April 2, 2008 - 12pm Central time zone:
either one. it depends on the plan that is generated.
if the sort takes place remotely - it'll use the remote temporary tablespace associated with the user you are connected to the remote instance as.
if the sort takes place locally - it'll use your local temporary tablespace (the one associated with the effective schema executing the query)
DB link
June 11, 2008 - 3pm Central time zone
Reviewer: Imran from San Antonio, Texas
Hi Tom,
My question is also related to the tpoic DB link,
When I created table usubg database link it got created but I have find that all varcahr2 fields
width become increse.
Create table test as select * from test@anotherserver;
Imran
Followup June 11, 2008 - 8pm Central time zone:
your character sets are different. The remote data is probably single byte and you are probably multi-byte.
It will "grow" the fields so that any string that fit in the other database should fit in yours.
Issues in 10g DBlink???
June 16, 2008 - 12pm Central time zone
Reviewer: Bhushan from India
Hi Thomas,
Need your suggestions/inputs if you have come across or heard of such a problem.
Let me give you a little background.
We have a application Appl_A which is running on Jrun and connects to a DB (Say APP_DB_8)
(8.1.7.4).There are procedures in DB_A which run and populate data in local tables.They connect to
a remote DB (8.1.7.4) (say DB_DWH_8) through a DB Link and extract data using select statement.And
then a Update is Run on the local table which refers to remote tables as well.Everything is going
on fine till now.
I hope you would be clear till here.
Now, Client decided to Migrate and Upgrade the DB to 10g (10.2.0.4).Migrated to a new server and DB
upgraded .Now when i try to run a procedure on the new 10g DB, it connects to the same db
(DB_DWH_8).The inserts work fine.However when the Update statement is run it takes a very long
time.
the scenario is something like this:
Application DB: APP_DB_8
DWH DB: DB_DWH_8
Procedure on APP_DB_8:
Proc1:
--some code--
insert into A (select g,h,j from x@APP_DB_8);
Commit;
--approximately inserts 1.2 Lac Records
Update a set f=b (select w from y@APP_DB_8);
--updates approx. 14k records and then the rest are deleted
commit;
end proc1;
--Proc completes in 10-12 minutes
The same procedure is run on 10g then
insert into A (select g,h,j from x@APP_DB_8); working fine
whereas Update a set f=b (select w from y@APP_DB_8);
runs on and on for hours but never completes. (i waited for 1.5 hours and then killed it)
Is this something related to 10g DB link issue? or do you see there can be any other reason for it?
Thank in Advance,
Bhushan
Followup June 16, 2008 - 1pm Central time zone:
trace it, compare plans. are they the same?
Trace Plans for above Issue
June 17, 2008 - 5am Central time zone
Reviewer: Bhushan from India
Hi Thomas,
I could get the trace PLans for the SQL running from 8i to 8i however it takes too long to give the
trace plan for the update statement from 10g to 8i.(from sqlplus)
HEre is the trace plan from 8i to 8i from sqlplus
Explain PLan 8i
SQL> UPDATE MYT_COM_PEAM_MASTER PEAM
2 SET (PEAM_NAME, PEAM_ORG, PEAM_OWNER, PEAM_STATUS) = (SELECT P.PEAM_NAME PEAM_NAME,
3 C.PEAM_ORG PEAM_ORG,
4 P.PEAM_OWNER,
5 C.PEAM_STATUS PEAM_STATUS
6 FROM PEAM@L_PRDDW P,
7 PEAM_CODES@L_PRDDW C
8 WHERE UPPER(TRIM(P.PEAM_NAME)) =
9 UPPER(TRIM(C.PEAM_NAME))
10 AND PEAM.SHIPTO_NUMBER =
11 P.CUSTNO
12 AND NVL(UPPER(PEAM.GRADE_CODE),
13 'G') =
14 NVL(UPPER(P.GRADE_CODE),
15 'G')
16 AND P.COLOR_CODE IS NULL
17 AND UPPER(C.PEAM_STATUS) =
18 'ACTIVE'
19 AND ROWNUM < 2)
20 WHERE PEAM_NAME IS NULL
21 AND PEAM_ORG IS NULL
22 AND PEAM_STATUS IS NULL;
176199 rows updated.
Elapsed: 00:01:102.97
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=40 Card=1 Bytes=43)
1 0 UPDATE OF 'MYT_COM_PEAM_MASTER'
2 1 TABLE ACCESS (FULL) OF 'MYT_COM_PEAM_MASTER' (Cost=40 Card=1 Bytes=43)
3 0 COUNT (STOPKEY)
4 3 NESTED LOOPS (Cost=3 Card=1 Bytes=77)
5 4 REMOTE* (Cost=2 Card=1 Bytes=50) L_PRDDW.WORLD
6 4 REMOTE* L_PRDDW.WORLD
5 SERIAL_FROM_REMOTE SELECT "PEAM_OWNER","CUSTNO","GRADE_CODE","C
OLOR_CODE","PEAM_NAME" FROM "PEAM" "
6 SERIAL_FROM_REMOTE SELECT "PEAM_NAME","PEAM_ORG","PEAM_STATUS"
FROM "PEAM_CODES" "A1" WHERE UPPER(T
Statistics
----------------------------------------------------------
7 recursive calls
720705 db block gets
3909 consistent gets
1941 physical reads
85312704 redo size
516 bytes sent via SQL*Net to client
2180 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
176199 rows processed
-------------------------------------------
Also,
I could get the explain plan from pl/sql developer for both instances (8i to 8i and 10g to 8i).
Unfortunately i cannot copy paste it here since it does not allow to copy.
One difference i could see is the cost,cardinality and Bytes are exactly the same for fetching the
data from remote location.However for update it increases randomly.
When updating using 8i to 8i it is Cost=40,Cardinality=1 and bytes=43 however from 10g to 8i it is
cost=234,cardinality=181306 and bytes=3988732.
I hope this helps.
Thanks,
Bhushan
Trace PLan for 10g to 8i
June 17, 2008 - 8am Central time zone
Reviewer: Bhushan from India
Hi,
I kewp this update running and it gave me this output after 2 to 2.5 hours for 10g to 8i DB LInk(i
guess did not look at the winodw after first 30-45 mints)
SQL> UPDATE MYT_COM_PEAM_MASTER PEAM
2 SET (PEAM_NAME, PEAM_ORG, PEAM_OWNER, PEAM_STATUS) = (SELECT P.PEAM_NAME PEAM_NAME,
3 C.PEAM_ORG PEAM_ORG,
4 P.PEAM_OWNER,
5 C.PEAM_STATUS PEAM_STATUS
6 FROM PEAM@L_PRDDW P,
7 PEAM_CODES@L_PRDDW C
8 WHERE UPPER(TRIM(P.PEAM_NAME)) =
9 UPPER(TRIM(C.PEAM_NAME))
10 AND PEAM.SHIPTO_NUMBER =
11 P.CUSTNO
12 AND NVL(UPPER(PEAM.GRADE_CODE),
13 'G') =
14 NVL(UPPER(P.GRADE_CODE),
15 'G')
16 AND P.COLOR_CODE IS NULL
17 AND UPPER(C.PEAM_STATUS) =
18 'ACTIVE'
19 AND ROWNUM < 2)
20 WHERE PEAM_NAME IS NULL
21 AND PEAM_ORG IS NULL
22 AND PEAM_STATUS IS NULL;
176199 rows updated.
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=234 Card=181306 Byte
s=3988732)
1 0 UPDATE OF 'MYT_COM_PEAM_MASTER'
2 1 TABLE ACCESS (FULL) OF 'MYT_COM_PEAM_MASTER' (TABLE) (Co
st=234 Card=181306 Bytes=3988732)
3 1 COUNT (STOPKEY)
4 3 NESTED LOOPS (Cost=2 Card=1 Bytes=77)
5 4 REMOTE* OF 'PEAM_CODES' (REMOTE) (Cost=1 Card=1 Byte L_PRDDW.
s=27) WORLD
6 4 REMOTE* OF 'PEAM' (REMOTE) (Cost=1 Card=1 Bytes=50) L_PRDDW.
WORLD
5 SERIAL_FROM_REMOTE SELECT "PEAM_NAME","PEAM_ORG","PEAM_STATUS"
FROM "PEAM_CODES" "A1" WHERE UPPER("
6 SERIAL_FROM_REMOTE SELECT "PEAM_OWNER","CUSTNO","GRADE_CODE","C
OLOR_CODE","PEAM_NAME" FROM "PEAM" "
Statistics
----------------------------------------------------------
269 recursive calls
712620 db block gets
1191 consistent gets
855 physical reads
87810408 redo size
454 bytes sent via SQL*Net to client
1921 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
176199 rows processed
Hope this helps...
If you see the rows processed is the same in the previous posted Explain plan and this..confirming
the data is similar..
Regards,
Bhushan Salgar
Followup June 17, 2008 - 10am Central time zone:
then trace it, see what the waits are and understand - 8i was far out of support before 10g was released, this is an entirely unsupported configuration, anything could be going wrong here and there might be no way to correct or change that.
Any Leads PLease!!!
June 18, 2008 - 1am Central time zone
Reviewer: Bhushan from India
Yes i agree this is a unsupported configuration for whatever reason, however can we identify
loooking at the plan what might be going wrong. Also, i know you will not agree but i changed the
following update statement to a for loop and it works OK. Well given a real Life situation where
you have no control over the DB version and the infrastructure would you implement the same
solution with a Disagreement note at the bottom saying it is working now might not work a year
later :)
Here are the statements...
UPdate Statement:
UPDATE MYT_COM_PEAM_MASTER peam
SET (peam_name
,peam_org
,peam_owner
,peam_status
)
=
(SELECT
p.peam_name peam_name
,c.peam_org peam_org
,p.peam_owner
,c.peam_status peam_status
FROM
peam@l_prddw p
,peam_codes@l_prddw c
WHERE
UPPER(trim(p.peam_name))=UPPER(trim(c.peam_name))
AND
peam.shipto_number = p.custno
AND
NVL(UPPER(peam.grade_code),'G') = NVL(UPPER(p.grade_code),'G')
AND
p.color_code IS NULL
AND
UPPER(c.peam_status) = 'ACTIVE'
AND
ROWNUM < 2
)
WHERE
peam_name IS NULL
AND
peam_org IS NULL
AND
peam_status IS NULL;
Changed to a For Loop (Kindly note the loop iteration is only ONCE)
FOR x IN (SELECT
p.peam_name peam_name
,c.peam_org peam_org
,p.peam_owner
,c.peam_status peam_status
FROM
peam@l_prddw p
,peam_codes@l_prddw c,
MYT_COM_PEAM_MASTER peam
WHERE
UPPER(trim(p.peam_name))=UPPER(trim(c.peam_name))
AND
peam.shipto_number = p.custno
AND
NVL(UPPER(peam.grade_code),'G') = NVL(UPPER(p.grade_code),'G')
AND
p.color_code IS NULL
AND
UPPER(c.peam_status) = 'ACTIVE'
AND
ROWNUM < 2) LOOP
UPDATE MYT_COM_PEAM_MASTER SET peam_name=x.peam_name,
peam_org=x.peam_org,
peam_owner=x.peam_owner,
peam_status=x.peam_status WHERE peam_name IS NULL
AND
peam_org IS NULL
AND
peam_status IS NULL;
commit;
END LOOP;
Followup June 18, 2008 - 1pm Central time zone:
I thought you said the plans were the same. They look the same - so it wasn't due to a plan change. What I just said above stands:
then trace it, see what the waits are and understand - 8i was far out of support before 10g was released, this is an entirely unsupported configuration, anything could be going wrong here and there might be no way to correct or change that.
different plan while using dblink
July 21, 2008 - 9pm Central time zone
Reviewer: A reader
<code>Hi Tom,
I have two schema, F_ORDER_LINE & L_AD_SLOT_PROPORTION table is under RAPT_BDDM schema, while T_ORDER_LINE_TOTAL_PROPORTION is in schema RAPT_UDM.
When I ran my query it took more then 1 hr for processing 4 months data, so I was trying different way to tune the query (as per my little knowledge) I am running this query from RAPT_UDM user and accessing RAPT_BDDM tables. Earlier I was accessing both the tables via SCHEMA_NAME.TABLE_TABLE method, then I have created synonym in rapt_udm which is poining to rapt_bddm table (synonym created schema_name.table_name method only) and both the way it is taking more then 1 hr. Then I created db link in RAPT_UDM schema and then created synonyum via table_name@db_link and surpricely it is taking 23 minuts.
Below is the explain plan for all differn way when i access tables via schema_name.table name or synonym (which is created schema_name.table_name) it is showing 1hr 44 minutes while dblink method showind 27 second, can u explain why it is showing that much changes in the plan. i am the only user connected to the database and there is no other activity is going on. I have tryied no. of times but i got the same diff.
18:07:42 DNUD>@dnbd
Connected.
18:07:48 NBD>show user
USER is "RAPT_BDDM"
18:07:50 NBD>desc f_order_line
Name
---------------------------------------------------------
ORDER_ID
ORDER_LINE_ID
SCHEDULED_DATE
ORDER_NAME
ORDER_DATE
PLACEMENT_ID
AD_COMPONENT_ID
INVENTORY_TYPE_ID
CUSTOMER_ID
AGENCY_ID
SCHEDULED_IMPRESSIONS
EXTENDED_SALES_PRICE
EXTENDED_LIST_PRICE
EXTENDED_FLOOR_PRICE
REMNANT_REVENUE_CPM
ADJUSTMENT_IMPRESSIONS
ADJUSTMENT_SALES_PRICE
FLIGHT_START_DATE
FLIGHT_END_DATE
DAY_OF_WEEK
PRICING_TYPE_ID
PACKAGE_ID
ORDER_TYPE_ID
CLUSTER_TYPE_ID
DELIVERY_TYPE_ID
18:07:59 NBD>grant all on f_order_line to rapt_udm;
Grant succeeded.
18:08:19 NBD>@dnudm
Connected.
18:08:27 DNUD>desc rapt_bddm.f_order_line
Name
---------------------------------------------------------
ORDER_ID
ORDER_LINE_ID
SCHEDULED_DATE
ORDER_NAME
ORDER_DATE
PLACEMENT_ID
AD_COMPONENT_ID
INVENTORY_TYPE_ID
CUSTOMER_ID
AGENCY_ID
SCHEDULED_IMPRESSIONS
EXTENDED_SALES_PRICE
EXTENDED_LIST_PRICE
EXTENDED_FLOOR_PRICE
REMNANT_REVENUE_CPM
ADJUSTMENT_IMPRESSIONS
ADJUSTMENT_SALES_PRICE
FLIGHT_START_DATE
FLIGHT_END_DATE
DAY_OF_WEEK
PRICING_TYPE_ID
PACKAGE_ID
ORDER_TYPE_ID
CLUSTER_TYPE_ID
DELIVERY_TYPE_ID
NUMBER
18:08:36 DNUD>show user
USER is "RAPT_UDM"
18:09:25 DNUD>create synonym bddm_f_order_line for rapt_bddm.f_order_line;
Synonym created.
Elapsed: 00:00:00.46
18:09:44 DNUD>desc bddm_f_order_line
Name Null? Type
Name
---------------------------------------------------------
ORDER_ID
ORDER_LINE_ID
SCHEDULED_DATE
ORDER_NAME
ORDER_DATE
PLACEMENT_ID
AD_COMPONENT_ID
INVENTORY_TYPE_ID
CUSTOMER_ID
AGENCY_ID
SCHEDULED_IMPRESSIONS
EXTENDED_SALES_PRICE
EXTENDED_LIST_PRICE
EXTENDED_FLOOR_PRICE
REMNANT_REVENUE_CPM
ADJUSTMENT_IMPRESSIONS
ADJUSTMENT_SALES_PRICE
FLIGHT_START_DATE
FLIGHT_END_DATE
DAY_OF_WEEK
PRICING_TYPE_ID
PACKAGE_ID
ORDER_TYPE_ID
CLUSTER_TYPE_ID
DELIVERY_TYPE_ID
different plan by using dblink (better)
July 21, 2008 - 9pm Central time zone
Reviewer: A reader
18:10:57 DNUD>DROP SYNONYM BDDM_F_ORDER_LINE;
Synonym dropped.
Elapsed: 00:00:00.62
18:13:25 DNUD>CREATE SYNONYM BDDM_F_ORDER_LINE FOR F_ORDER_LINE@BDLINK;
Synonym created.
18:13:58 DNUD>ED
18:14:08 DNUD>
18:14:21 DNUD>SELECT
18:14:22 2 sfolt.order_id
18:14:22 3 ,sfolt.order_line_id
18:14:22 4 ,scheduled_date
18:14:22 5 ,ad_slot_id
18:14:22 6 ,ad_component_id
18:14:22 7 ,inventory_type_id
18:14:22 8 ,delivery_type_id
18:14:22 9 ,(case when nvl(scheduled_impressions,0) <> 0 then
(extended_sales_price/scheduled_impressions)*1000 else null end) cpm
18:14:22 10 ,scheduled_impressions * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot
ELSE proportion/total_proportion END) scheduled_impressions
18:14:22 11 ,extended_sales_price * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot
ELSE proportion/total_proportion END) extended_sales_price
18:14:22 12 ,extended_list_price * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot
ELSE proportion/total_proportion END) extended_list_price
18:14:22 13 ,extended_floor_price * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot
ELSE proportion/total_proportion END) extended_floor_price
18:14:22 14 ,remnant_revenue_cpm * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot
ELSE proportion/total_proportion END) remnant_revenue_cpm
18:14:22 15 ,adjustment_impressions * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot
ELSE proportion/total_proportion END) adjustment_impressions
18:14:22 16 ,adjustment_sales_price * (CASE WHEN total_proportion = 0 THEN 1/total_ad_slot
ELSE proportion/total_proportion END) adjustment_sales_price
18:14:22 17 ,sum(sfolt.scheduled_impressions/(sfolt.flight_end_date -
sfolt.flight_start_date +1)) over ( partition by sfolt.order_id, sfolt.order_line_id order by
sfolt.order_id, sfolt.order_line_id) total_impressions
18:14:23 18 FROM
18:14:23 19 T_ORDER_LINE_TOTAL_PROPORTION tp
18:14:23 20 ,bddm_f_order_line sfolt
18:14:23 21 ,rapt_bddm.l_ad_slot_proportion lptp
18:14:23 22 WHERE
18:14:23 23 tp.order_id = sfolt.order_id
18:14:23 24 AND tp.order_line_id = sfolt.order_line_id
18:14:23 25 AND tp.flight_start_date = sfolt.flight_start_date
18:14:23 26 AND tp.flight_end_date = sfolt.flight_end_date
18:14:23 27 AND sfolt.placement_id = lptp.placement_id
18:14:23 28 AND sfolt.day_of_week = lptp.day_of_week
18:14:23 29 and scheduled_date between '01-apr-2008' and '31-jul-2008'
18:14:23 30 and proportion_month = '01-JUN-2008'
18:14:23 31 /
Elapsed: 00:00:00.36
Execution Plan
----------------------------------------------------------
Plan hash value: 3101442654
----------------------------------------------------------------------------------------------------
----------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time | Pstart| Pstop | Inst |IN-OUT|
----------------------------------------------------------------------------------------------------
----------------------------------------------
| 0 | SELECT STATEMENT | | 1724K| 494M| | 135K
(1)| 00:27:12 | | | | |
| 1 | WINDOW SORT | | 1724K| 494M| 1077M| 135K
(1)| 00:27:12 | | | | |
|* 2 | HASH JOIN | | 1724K| 494M| | 24158
(4)| 00:04:50 | | | | |
|* 3 | HASH JOIN | | 1587 | 390K| 3992K| 7165
(2)| 00:01:26 | | | | |
| 4 | TABLE ACCESS FULL | T_ORDER_LINE_TOTAL_PROPORTION | 60920 | 3272K| | 119
(3)| 00:00:02 | | | | |
| 5 | REMOTE | F_ORDER_LINE | 30482 | 5864K| | 6548
(2)| 00:01:19 | | | BDLINK | R->S |
| 6 | PARTITION RANGE SINGLE| | 11M| 523M| | 16829
(4)| 00:03:22 | 2 | 2 | | |
|* 7 | TABLE ACCESS FULL | L_AD_SLOT_PROPORTION | 11M| 523M| | 16829
(4)| 00:03:22 | 2 | 2 | | |
----------------------------------------------------------------------------------------------------
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SFOLT"."PLACEMENT_ID"="LPTP"."PLACEMENT_ID" AND
"SFOLT"."DAY_OF_WEEK"="LPTP"."DAY_OF_WEEK")
3 - access("TP"."ORDER_ID"="SFOLT"."ORDER_ID" AND "TP"."ORDER_LINE_ID"="SFOLT"."ORDER_LINE_ID"
AND
"TP"."FLIGHT_START_DATE"="SFOLT"."FLIGHT_START_DATE" AND
"TP"."FLIGHT_END_DATE"="SFOLT"."FLIGHT_END_DATE")
7 - filter("PROPORTION_MONTH"=TO_DATE('2008-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
Remote SQL Information (identified by operation id):
----------------------------------------------------
5 - SELECT
"ORDER_ID","ORDER_LINE_ID","SCHEDULED_DATE","PLACEMENT_ID","AD_COMPONENT_ID","INVENTORY_TYPE_ID","SC
HEDULED_IMPRESSIONS","EX
TENDED_SALES_PRICE","EXTENDED_LIST_PRICE","EXTENDED_FLOOR_PRICE","REMNANT_REVENUE_CPM","ADJUSTMENT_I
MPRESSIONS","ADJUSTMENT_SALES_PRICE","
FLIGHT_START_DATE","FLIGHT_END_DATE","DAY_OF_WEEK","DELIVERY_TYPE_ID" FROM "F_ORDER_LINE"
"SFOLT" WHERE "SCHEDULED_DATE"<=:1 AND
"SCHEDULED_DATE">=:2 (accessing 'BDLINK' )
Different Versions of oracle
July 23, 2008 - 3pm Central time zone
Reviewer: Scott from New York, NY, USA
Tom,
Are DB Links compatible with different versions of Oracle? My 10g database can use the link to
access my 9i, but I am having issues with my 9i accessing my 10g. Is this a software limitation?
Thanks,
Scott
Followup July 24, 2008 - 10am Central time zone:
it should work - all supported releases should work - please utilize support.
database links from 10g to 9i
July 29, 2008 - 7pm Central time zone
Reviewer: kevin from san francisco, ca
I seem to be experiencing the same issue. I can create a db link and successfully query from 10g
(10.2.0.4) to 9i (9.2.0.8) but not from 9i to 10g. The tnsnames entry appears fine on the 9i side,
I can successfully connect to 9i from the 10g host using sqlplus and tnsnames alias. BUT, when I:
create database link mylink
connect to <user> identified by <pword>
using 'samealiasiusedforsqlplusconnect';
and issue select sysdate from dual@mylink; I get an error about name resolution. Yes, I will
follow-up with support but just wanted to provide a real example of the problem the other reader
was apparently experiencing. Thanks.
Followup August 1, 2008 - 11am Central time zone:
no error, no able to comment very much
but if it is saying "unable to resolve your tnsnames" stuff, that has nothing to do with versions - everything to do with the configuration on the 9i side, you are reading the wrong tnsnames.ora file or the right one does not have this tns connect entry set in it.
to "prove" that
create database link .... using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=...(SERVICE_NAME=ora10gr2)))'
just put the tns entry right in there.
db links
July 29, 2008 - 9pm Central time zone
Reviewer: kevin from san francisco, usa
...update to previous. Adding the env variable TNS_ADMIN to where the listener is run from and
tnsnames.ora entries are held solved the problem.
Followup August 2, 2008 - 4pm Central time zone:
you were reading the wrong tnsnames.ora file then... tns_admin tells us where to find it instead of looking in the default paths first.
August 5, 2008 - 2pm Central time zone
Reviewer: Alexander
Tom,
Can I have you opionion on the following. If I am using a 10.2.0.2 database to connect to a 10.2.0.4 database via db link, should I expect this:
SQL> merge into ebr.vin_mask a
2 using
3 (
4 select * from ebr.vin_mask@orac01x
5 minus
6 select * from ebr.vin_mask
7 ) b
8 on (a.vin_make_code = b.vin_make_code
9 and a.mask_position = b.mask_position
10 and a.model_year = b.model_year
11 and 1 = 0)
12 -- when matched then / This can't happen these columns are pks /
13 -- update
14 -- set a.vin_make_code = b.vin_make_code,
15 -- a.mask_position = b.mask_position,
16 -- a.model_year = b.model_year
17 when not matched then
18 insert(
19 a.vin_make_code,
20 a.mask_position,
21 a.model_year
22 ) values(
23 b.vin_make_code,
24 b.mask_position,
b.model_year
25 26 );
merge into ebr.vin_mask a
*
ERROR at line 1:
ORA-02064: distributed operation not supported
When I go test ===> dev, (both 10.2.0.4), this does not happen. There are no unsupported columns.
SQL> desc ebr.vin_mask
Name Null? Type
----------------------------------------- -------- ----------------------------
VIN_MAKE_CODE NOT NULL CHAR(10)
MASK_POSITION NOT NULL CHAR(1)
MODEL_YEAR NOT NULL NUMBER(4)
This only strange thing is, when I hit the issue, it's within a job with half a dozen other merge's very similar to the one that is failing, but they all work.
Also, global_names is set to true on the 10.2.0.4 instances, not in the problematic 10.2.0.2.
I did however try it using alter session, it still bombed. Do you have any ideas? Really appreciate it.
Followup August 5, 2008 - 6pm Central time zone:
contact support, reference bug #4311273
Dblink and network problems
August 20, 2008 - 7am Central time zone
Reviewer: setadini from Italy
I'm running a web-site backend database with oracle XE and many other remote db's (all of them are
10gR2 Standard or Enterprise) are syncronizing their schemas on this machine via dblink via
bi-directional insert/select statements.
The syncronize procedure is run every 5 minutes on the "remote" databases without any problem.
When a network error happens during a syncronization, sometimes the remote session remains busy
with the event "SQL*Net message from dblink" (for days...) and even killing it no other session is
able to use the (network-restored) dblink until a shutdown.
Is there a way or special syntax to make the session expire/timeout to avoid the shutdown?
Thanks
Atlast i know the reason!!!
August 21, 2008 - 7am Central time zone
Reviewer: Bhushan from India
Hi Thomas,
You know what is the best way to keep one busy and gain knowledge without being bored..Read
asktom.oracle .com ;)
Anyways..i had asked a question sometime back about the issues in 10g DBlink to 8i DB's and you
answered there might be a way to correct it or might not..the 8i configuration is not supported any
more.
Well i did find out there is a way :)
well it was all about
alter session set optimizer_index_cost_adj=35
and i do not have to use the RBO the CBO works even better then the RBO now.
Well good thing: I found it on your own site
Bad thing: Will have to convince the client again ;)
Cheers,
Bhushan
Followup August 21, 2008 - 8am Central time zone:
umm, sorry to disappoint, but that optimizer setting will have no impact on your ability to connect or not connect 10g to 8i. It may or may not work depending on what you do (never tested internally), but that optimizer setting has *nothing* to do with the ability to connect or not.
export/import public db links using exp/imp
September 10, 2008 - 1am Central time zone
Reviewer: A reader
How can I export/import public dblinks using exp/imp
I am exporting importing 2 schemas from a Database and these Schemas also use public DB links in
the source schema.I dont want to do a full exp/imp as I am only interested in 2 schemas.
How to export/import public dblinks.
One way could be to recreate them manually, But is there any exp imp option for this ?
something like
exp userid/pass owner=schema_1,schema_2,PUBLIC
IMP userid/pass FROMUSER=schema_1,schema_2,PUBLIC TOUSER=schema_1,schema_2,PUBLIC.
Thanx
Followup September 11, 2008 - 10am Central time zone:
you cannot, you'll have to recreate them. when you export at the schema level, only things owned by the schema come - the public database links are not owned by the schema.
db 9208 export/import public db links using exp/imp
September 10, 2008 - 1am Central time zone
Reviewer: A reader
Sorry forgot Db version is 9208
export/import public db links using exp/imp
Compiled code and datbase links
September 10, 2008 - 11am Central time zone
Reviewer: Mike from Cleveland, OH USA
I have a setup like this:
Oracle1 has user SchemaA which owns table T.
Oracle2 has user SchemaA which owns:
- Synonym T which points to SchemaA.T@Oracle1. (DB link Oracle1 is a public link to Oracle1 with no
CONNECT TO clause or other authentication. Passwords match for SchemaA on Oracle1 and Oracle2.)
- Package P. Package P has SELECT and INSERT calls to a table-like object called T, which is
supposed to resolve using the synonym above.
On Oracle2, we have issued GRANT EXECUTE ON SchemaA.P TO ExecutionUserB.
In SQLPlus, we connect as ExecutionUserB@Oracle2 and run an anonymous block to execute SchemaA.P.
Now, the problem. It seems that the credentials being used over the link are those of
ExecutionUserB, not SchemaA. I thought that, since the link is being used by compiled code, the
SELECT, INSERT, etc. calls would have the credentials of the owner of the code.
Is this the expected behavior? Or is there some piece of the puzzle I am missing? I can 'fix' this
by creating a link with the credentials I want, but that makes for one more headache when passwords
change.
Followup September 11, 2008 - 11am Central time zone:
... Now, the problem. It seems that the credentials being used over the link are
those of ExecutionUserB, not SchemaA. I thought that, since the link is being
used by compiled code, the SELECT, INSERT, etc. calls would have the
credentials of the owner of the code.
....
not for current user database links - the password needs to be sent from database 1 to database 2 - we do not have the password stored for the owner of the procedure, we only have the current users credentials.
You would have to create the link with the desired set of credentials if you wanted them to be "constant"
About ORA-02064
September 11, 2008 - 9am Central time zone
Reviewer: Vijay Bhaskar from India
Tom,
Could you please elaborate your comment..
"you cannot do transactional control over a dblink - the remote site isn't allowed to commit......"
If so then, are't we violating the above, when RPC calls (not involving OUT variables) runs fine ?
Further, the workaround you suggested as above, may not fit for all. For example, in our case :-
We have
dbase A :-
--------
proc a(accepts OUT params & containts "commits")
This proc also has an "insert" statement which logs an entry(invoker's details)into a local tables
whenever it's being invoked.
A user(say SCOTT) in dbase B invokes the above procedure "a", through RPC call...
in dbase B :- exec a@A.world;
now it would fail with ORA-02064. To avoid this I have to remote commit..& thereby, rely on invoker
to commit, so that i can see the log record in dbase A. If he decides to rollback otherwise, i will
loose the information. isn't ?
So, this in a way, introduces dependency on B...
Can you please throw some light on this.
Regards,
Vijay
Followup September 16, 2008 - 12pm Central time zone:
... If so then, are't we violating the above, when RPC calls (not involving OUT
variables) runs fine ?
...
explain why you think so? I don't think so...
... Further, the workaround you suggested as above, may not fit for all. For
example ...
explain again, this is a HUGE page, "as above" could mean hundreds of things in this context.
Proc A is not allowed to have a commit, the invoker - from database B would do the commit.
There is no dependency here - the site that initiated the dblink OWNS the transaction. Database B in your case is the site that would commit. Proc A should NOT have a commit, it would be executed from database B and B would commit.
What I'm trying to say is...
September 30, 2008 - 8am Central time zone
Reviewer: Vijay Bhaskar from India
Tom,
Thanks a ton for your response. I might not have able to properly understand your point.
Let me explain the point I'm trying to make
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for HPUX: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
SQL>
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------
BIP2D2.DEVENV1.BT.CO.UK
SQL>
SQL> sho user
USER is "SCOTT"
SQL>
SQL> create table t_user_data(
2 userid varchar2(10)
3 ,username varchar2(30)
4 ,hiredate date
5 ,last_logged_in timestamp(6)
6 )
7 tablespace appl_data
8 /
Table created.
SQL> alter table t_user_data add constraint t_user_data_pk primary key(userid);
Table altered.
SQL>
SQL> insert into t_user_data values('E1','Vijay',to_date('09/08/2008','dd/mm/yyyy'),null);
1 row created.
SQL> commit;
Commit complete.
SQL> set lin 990
SQL>
SQL> select * from t_user_data;
USERID USERNAME HIREDATE LAST_LOGGED_IN
---------- ------------------------------ --------- ---------------------
E1 Vijay 09-AUG-08
SQL>
SQL> create or replace procedure pr_last_login(uid t_user_data.userid%type)
2 as
3 begin
4 update t_user_data set last_logged_in=systimestamp where userid=uid;
5 if sql%notfound then
6 raise_application_error(-20001,'User '||uid||' is not registered !');
7 end if;
8 commit;
9 end;
10 /
Procedure created.
SQL>
Then I logged onto another database (invoking) named BIP2T2 as SCOTT :-
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 30 17:13:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SQL>
SQL>
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for HPUX: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
SQL>
SQL>
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
BIP2T2.DEVENV1.BT.CO.UK
SQL> sho user
USER is "SCOTT"
SQL>
Created a database link called BIP2D2.DEVENV1.BT.CO.UK pointing, as the name implies, to BIP2D2
database.
SQL>
SQL> create database link bip2d2.devenv1.bt.co.uk
2 connect to scott
3 identified by <password>
4 using 'bip2d2.devenv1.bt.co.uk'
5 /
Database link created.
SQL>
SQL> set lin 990
SQL> column DB_LINK format a30
SQL> column HOST format a30
SQL> column USERNAME format a30
SQL>
SQL> select DB_LINK,USERNAME,HOST from user_db_links;
DB_LINK USERNAME HOST
------------------------------ ---------- ------------------------------
BIP2D2.DEVENV1.BT.CO.UK SCOTT bip2d2.devenv1.bt.co.uk
SQL>
SQL> create synonym pr_last_login for scott.pr_last_login@bip2d2.devenv1.bt.co.uk;
Synonym created.
SQL>
I will now be making a RPC call to procedure pr_last_login using the DB link
BIP2D2.DEVENV1.BT.CO.UK.
SQL> exec pr_last_login('E1');
PL/SQL procedure successfully completed.
SQL>
Now, let's check the data in the table t_user_data :-
SQL> select * from t_user_data@BIP2D2.DEVENV1.BT.CO.UK;
USERID USERNAME HIREDATE LAST_LOGGED_IN
---------- ---------- ---------
--------------------------------------------------------------------
E1 Vijay 09-AUG-08 30-SEP-08 01.17.08.175550 PM
SQL>
As you can see the information about last_login time has been updated/recorded.
So, herein I could successfully commit the transaction from database BIP2D2. How Oracle is then
managing the transaction control, in this case ?
Shouldn't it be invoking database BIP2D2 which is supposed to "COMMIT" here.The same call, however
would fail, if it involves the OUT paraneters.
Can you please explain this behaviour.
Best Regards,
Vijay
Followup September 30, 2008 - 9am Central time zone:
only the initiating site can commit in a distributed transaction (reason 1,234,223 I truly wish commit and rollback did NOT exist in the plsql language - along with when others, autonomous transactions and the like).
do not rely on this - what happens if you start a transaction in the primary site first - do not go down this path.
Thanks a ton....
October 3, 2008 - 2am Central time zone
Reviewer: A reader from India
I have now got it right & fine...
It's more to do with the business functionality that one is trying to code-in...which means, under
no circumstances the primary site is supposed to complete(commit/rollback) the
transaction....and thus, initiating database should always control the transactional flow....
Once again, many thanks for your contribution to Oracle fraternity....much appreciated...
Best Regards,
Vijay
Slow in Database link
November 24, 2008 - 6am Central time zone
Reviewer: mabousaid
I have a map it reads from oracle 10g and it is very fast but when I create oracle database link to
another server oracle 10g it becomes very slow and it freezes.
In this way I created db link
CREATE PUBLIC DATABASE LINK "CIS" CONNECT TO "APP" IDENTIFIED by "APP" USING 'MAKKAH'
Followup November 24, 2008 - 6pm Central time zone:
you have given no useful information here.
I don't know what "a map" is or does.
I don't know what the difference between the first scenario you talk of and the second is really - I don't know how you are using the database link, what are you doing with it.
Same db or different
January 24, 2009 - 6am Central time zone
Reviewer: A reader
Hi Tom,
We are in the process of making decision, whether we should extend existing data warehouse db (10g) to include data marts or not. The obvious pros of having different instance (on different machine) is to have better control over performance (without impacting the other), independent upgrades, new installation can be 11g etc. However, there could be situations where users from DM may (sometimes) drill into DW tables.
Based upon your advise above, do you see any impact with the following approach.
1) Create new instance 11g for data marts
2) Create views in DM (11g) with DB links to DW tables(10g)
3) Let front end tool handle the drill down through views
Regards,
Followup January 24, 2009 - 1pm Central time zone:
what you view as "pros", I see mostly as cons
o many instances to monitor, adjust, allocate resources to, pay attention to
o having to upgrade tons of instances, instead of just one - patching patching patching.
I'm for a single database
I'll always say that, I'll always fight the creation of yet more databases...
Single db
January 24, 2009 - 6pm Central time zone
Reviewer: A reader
Hi Tom,
Thanks for quick response and your views.
However, do you see technically any issue with this approach?
Regards,
Followup January 24, 2009 - 7pm Central time zone:
sorry, I thought I was clear...
Everything you see as a "pro", I see as clearly and obviously being a "con"
You will increase complexity, dependency, have more to manage, backup, monitor, tune, fix, upgrade, etc.
I see no advantage to creating a new database if the existing one has the capacity, or could be easily upgraded to have the capacity to handle the load.
Single db
January 24, 2009 - 9pm Central time zone
Reviewer: A reader
Thanks Tom.
Agreed....
My apologies - sometimes it is not easy.. machine reached it's limit of maximum CPUs, SAN may need upgrade etc. etc.
But trying to figure out that if technically not feasible, then there is no need to even consider any other option except to upgrade it.
Followup January 25, 2009 - 11am Central time zone:
it is always *technically* feasible. Anything can technically be built. Software is quite flexible that way.
I run into this discussion all of the time
q: "can this be done"
a: "yes, but should you do it - that is the question we should ask"
q: "no, i just want to know 'can this be done'"
a: "in your case, no - you are no where near wanting to consider that"
q: "that doesn't answer my question"
a: "I know, I won't tell you to do something that you'll ultimately regret. Whether you *can* do something is very different from whether you *should*"
So, technically feasible - sure
Do you want to do it? Only if simplier solutions are not feasible.
Thanks
January 25, 2009 - 5pm Central time zone
Reviewer: A reader
Hi Tom,
Thanks for the help.
Regards,
How can I find what views/synonyms use database links?
January 27, 2009 - 1pm Central time zone
Reviewer: Kevin from Austin TX
I fear this will be simple but I'm failing miserably at finding an answer. How can I find out with
a query, in a 9i database I've inherited, what views and synonyms are utilizing database links? I'm
trying to untangle a web of interdependencies between 2 (or more) databases.
Thanks in advance for any assistance.
Followup January 28, 2009 - 3pm Central time zone:
we do not track the dependency at the database link level.
dba_synonyms does have a column for a referenced dblink.
for dba_views, you would need to use a "like" on the view text
1 begin
2 for x in (select owner, view_name, text from dba_views where text_length <= 32760)
3 loop
4 if (lower(x.text) like '%@ora11gr1@loopback%' ) then dbms_output.put_line(
x.owner||'.'||x.view_name); end if;
5 end loop;
6* end;
ops$tkyte%ORA11GR1> /
OPS$TKYTE.V
PL/SQL procedure successfully completed.
but that'll work for 32k or less views only... (eg: most of them)
database link password
February 28, 2009 - 3pm Central time zone
Reviewer: Matt from MA, USA
our DBA is telling "no" for database link on one of the production db p1 to another production db
p2 saying that people with select catalog role on p1 can access data on p2.
is it correct that people with select catalog role can see password for database link ? If yes,
what is the alternative solution ?
Thanks,
Followup March 3, 2009 - 10am Central time zone:
does your DBA understand the various ways dblinks can be created?
a) with a user/password hard coded, only the privileges of THAT user are conveyed, eg: you do not get your privileges on the remote site, you get the privs of the user specified in the dblink.
b) with current user, the current users credentials are used, so, if the user in the remote database has a privilege to do something, well, frankly they could just connect using sqlplus.
among others - not sure what the issue is?
and no, select catalog doesn't let anyone see the password. Ask them to demonstrate the issue, they won't be able to.
dblink password
March 4, 2009 - 7pm Central time zone
Reviewer: Matt from MA, USA
Thanks Tom.
I asked our DBA, if there is any way he can prove the point for catalog role or otherway around to
know, the password and he say it is possible,but dont know the sql at this moment.
I think I will wait for his answer, if he really has any sql to get the password, or am I asking
something to risk prod env with security issue.
btw, database is Oracle 10g.
Thanks.
Followup March 4, 2009 - 8pm Central time zone:
select catalog role lets you see views, it does not provide access to sys.tables, you would need access to sys.link$ in older versions - and he won't have it with that role.
In current releases, the password is not stored in a readable format in sys.link$, so even if you can 'see it', it won't matter, you cannot use it.
But, you cannot see it, so the point is "not there"
Database link
March 19, 2009 - 1pm Central time zone
Reviewer: A Reader
Hi Tom,
I am trying to create a database link using a stored procedure but getting an error ORA-01031:
insufficient privileges
CREATE OR REPLACE PROCEDURE pr_create_db_link( p_user_id IN VARCHAR2,
p_password IN VARCHAR2,
p_database_name IN VARCHAR2 ) IS
g_error_msg varchar2(512);
g_sql varchar2(2000);
BEGIN
-- Create database link for source
g_sql := 'CREATE DATABASE LINK TEST.WORLD CONNECT TO '||p_user_id||
' IDENTIFIED BY '||p_password||' USING '||''''||p_database_name||'''';
EXECUTE IMMEDIATE g_sql;
EXCEPTION
WHEN OTHERS THEN
g_error_msg := RPAD(SQLERRM,512);
dbms_output.put_line(g_error_msg);
RAISE;
END;
But it works, if I replace the above as an anonymous block.
declare
g_error_msg varchar2(512);
g_sql varchar2(2000);
BEGIN
-- Create database link for source
g_sql := 'CREATE DATABASE LINK TEST.WORLD CONNECT TO '||'TEST_USER'||
' IDENTIFIED BY '||'TEST_PASSWORD'||' USING '||''''||'TEST_DATABASE'||'''';
EXECUTE IMMEDIATE g_sql;
EXCEPTION
WHEN OTHERS THEN
g_error_msg := RPAD(SQLERRM,512);
dbms_output.put_line(g_error_msg);
RAISE;
END;
Do I need additional privilege/role to create database link through a stored procedure? Please
advise.
Performance / Scalability issues with DB Links
April 14, 2009 - 3pm Central time zone
Reviewer: Sam from OH USA
Hi Tom,
Are there any performance or scalability issues we need to consider if db links are used in an OLTP system ?
i.e. How does the link handle multiple sessions using the link simultaneously ?
Thanks
Sam
Followup April 14, 2009 - 4pm Central time zone:
If you are thinking of using dblinks "a lot" in an interactive OLTP application, in real time - please, please - rethink this.
First of all, why would you even consider having two databases if part of one is contained in the other. The only thing you'll get from this is massively decreased availability (the probability that one or the other is down is larger than the probability that one well managed one is). You'll have upgrade issues. You'll have performance issues (you are adding a ton of layers). You'll be doing two phase commits - which will make things slower even more (forget about the extra work to run bits of queries here there and everywhere and pull it all together).
database links are great for background stuff, moving data from OLTP to reporting, a tiny bit of really necessary replication in extreme cases - but in the background (so that if one system is down, the other keeps trucking along, so that end users don't have to wait/pay the penalty of distributed stuff).
But - it really sounds like you want a SINGLE DATABASE - ONE DATABASE.
I'm with you there
April 15, 2009 - 2pm Central time zone
Reviewer: Sam from OH USA
Unfortunately, that is a battle that has already been fought and lost. We even showed them some one of the asktom posts that deal with this topic. The DBA team made the argument for a single database and someone (with a SQL server background) finally pulled rank on us and decided that we will have separate databases.
The issue now is that I have an application that needs to insert a few rows at a time in a remote database. The code is in pl/sql but it is part of a multi user system.
At this point my only option is to use a "shared" db link or to move the code to either java or c#.
Any suggestions ?
Thanks
Sam
Followup April 15, 2009 - 2pm Central time zone:
does that sqlserver guy understand that two separate databases in Oracle is ENTIRELY AND TOTALLY AND WHOLLY DIFFERENT than two databases in sqlserver?????
In sqlserver, you can have two databases in one instance (they use three layers to reference things - select * from DATABASE.OWNER.TABLE - we do not have that concept)
In sqlserver - it would make sense to use two databases in a single instance.
In Oracle - it would make sense to use two separate sets of TABLESPACES in a single instance.
Your sqlserver guy is right - in sqlserver, but not in Oracle - this is a big big - career limiting - move.
I feel so strongly about this - I'd be glad to get on the phone with you, your management team and the sqlserver guy and explain to them what a huge mistake you are making. Seriously.
You cannot move the code to java or c# (you sort of need transactional integrity here - you cannot do it in two separate transactions)
This is a huge mistake, I cannot see this happen. thomas.kyte@oracle.com
April 16, 2009 - 10am Central time zone
Reviewer: Alexander
So Tom, does these mean if we're being forced into some terrible decision we can run it by you to
see if you can talk some sense into the powers that be ;) Or is this a one time offer.
Followup April 16, 2009 - 11am Central time zone:
this is a one time offer as the mistake this customer is going to make is so huge.
What I know will happen:
sqlserver guy has made it so that Oracle stands zero chance of performing.
sqlserver guy does that by imposing sqlserver techniques on Oracle (the multiple database thing)
sqlserver guy will therefore 'prove' that Oracle isn't as good as sqlserver.
I can see a migration in the future.
I want to prevent about 12 months of real pain, followed by a migration.
DB Link for online huge transactions
April 28, 2009 - 8am Central time zone
Reviewer: Arindam Mukherjee from India
Sir,
There are two Oracle 10g databases on two different servers (OS - Linux Redhat 4.0). Every day around 100 thousands records will be inserted online into two databases simultaneously. For example, there is one screen under funding module. After submission, data will be stored to Accounts module and funding module simultaneously to different tables through a PL/SQL Procedure residing in funding module. For account module, there are around 6 tables and for funding module there are maximum 3 tables. Under this circumstance, I have two options.
First option - All tables should be in one schema in one database server.
Second option - Two modules will be on different servers and database link will bridge the gap between two servers when data gets populated to two modules.
Since the transaction data volume is around 100 thousands records per day, at the end of month it could be around 300 thousands records per day, I am bit confused with database link usage.
Please help me get the right decision.
Regards,
Arindam Mukherjee
Followup April 28, 2009 - 10am Central time zone:
the second option should be discarded.
the first option should be rewritten, the tables need not (probably should not) be in the same schema. Division of tables, division of tables from code - very good idea.
I would definitely recommend avoiding distributed complexity (and performance impacts).
These two 'modules' want to be together.
Whatever happened to Sam of OH?
April 28, 2009 - 10am Central time zone
Reviewer: David Weigel from Wayne, PA USA
I'm wondering how that one-time offer to Sam's company worked out, and if minds were changed.
Maybe Tom can't reply because of confidentiality issues, but maybe Sam could..
Followup April 28, 2009 - 10am Central time zone:
never heard from them.
db_links
April 28, 2009 - 4pm Central time zone
Reviewer: John T. from Detroit, Michigan
I had a similar problem as Sam from OH when I was asked to manage SQL Server DBAs. I decided to
speak their language. It seemed to work although perhaps not technically correct.
Database = schema
Server = Oracle Instance
In which database join occurs
June 7, 2009 - 5pm Central time zone
Reviewer: Parikh84 from Florida
Hi Tom,
Suppose I have two Database(DB1 and DB2) with a DBlink...Now,suppose there are two views(vw1 and
vw2 on DB1)...Now, I create two synonym in DB2 for vw1 and vw2....Now if we do a join with the help
of synonyms on vw1 and vw2 of DB1 on DB2, will the join happen on DB1 or it will take place in
DB2...
Kindly help me on this.....
Followup June 8, 2009 - 1pm Central time zone:
it depends - it could take place in either server
use explain plan to see what the database decided to do.
passing host string as argument
June 10, 2009 - 7am Central time zone
Reviewer: Hashmi
Dear Tom,
Oracle 10G.
I need to pass host string as arguments during creation of database links dynamically.
I have to create various links for various locations.I have created a cursor containing db link
names and i have to pass them as arguments in the execute immediate statements.
For example,
for v in(select db_name from dblnk)loop
v_dblnk:=v.db_name;
execute immediate 'create public database link v_dblnk connect to usr identified by pwd using
v_dblnk';
But i am getting ora-02010 error while running the procedure.
How should i overcome this problem.
Thanks
Followup June 10, 2009 - 2pm Central time zone:
the format of the create database link is in general:
create public database link
LINKNAME
connect to userid identified by password using
'TNSNAME'
you are using a linkname of "v_dblnk" - you are not putting the value of the variable in there - but rather the string v_dblnk. Secondly - you need to quote the tns connect string.
You need to concatenate the value in:
for v in(select db_name from dblnk)
loop
execute immediate '
create public database link ' ||
v.db_name || '
connect to usr identified by pwd using ''' ||
v.db_name || '''';
end loop;
and you need to decide if you need to check for sql injection issues (google sql injection if that term means nothing to you)
June 11, 2009 - 2am Central time zone
Reviewer: Hashmi
Dear Tom,
I need to insert records from remote locations.For that I have done this:
db_lnk varchar2(100);
for v in(select db_name from dblnk)
loop
execute immediate '
create public database link ' ||
v.db_name || '
connect to usr identified by pwd using ''' ||
v.db_name || '''';
insert into usr.tab1 select * from tab1@'''||v.db_name||'''
end loop;
But I am getting ORA-01729: database link name expected
error.
If i use a variable like
db_lnk:=v.db_name;
insert into usr.tab1 select * from tab1@db_lnk;
It throws table or view does not exist.
All I want to do is achieve a functionality where I can dynamically create database links one by
one through a loop and insert data from those links in the loop.
Thanks
Followup June 11, 2009 - 8am Central time zone:
if you dynamically create something
in a block of code
and want to reference that something
in that same block of code
you must reference it dynamically as well - because the block of code cannot compile otherwise.
Do you by any chance have an experienced pl/sql developer nearby that can mentor you - some of this is fairly basic stuff - a mentor is the best way (in my opinion) to learn.
I see a lot of problems in your code.
first the create will commit. If your code fails halfway through - half of the inserts will be committed and the other half will not have happened. If you try to restart your code, it will fail immediately since you don't deal with "database link already exists". I can see that as the next question after I show you how to do this with a dynamic insert (hence - my suggestion of "mentor" - to head that probable outcome off...)
ops$tkyte%ORA10GR2> create table t
2 ( db_name varchar2(40), db_connect varchar2(40) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 'ora10gr2@loopback', 'ora10gr2' );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t2;
Table dropped.
ops$tkyte%ORA10GR2> create table t2( txt varchar2(80) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 db_link_exists exception;
3 pragma exception_init( db_link_exists, -2011 );
4 begin
5 for x in ( select * from t )
6 loop
7 begin
8 execute immediate '
9 create public database link ' ||
10 x.db_name || '
11 connect to ops$tkyte identified by foobar using ''' ||
12 x.db_connect || '''';
13 exception
14 when db_link_exists
15 then
16 dbms_output.put_line( 'skipping create of ' || x.db_name || '
already exists' );
17 end;
18
19 execute immediate 'insert into t2@' || x.db_name ||
20 '( txt ) select username from all_users where rownum <= 5';
21 end loop;
22 end;
23 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t2@ora10gr2@loopback;
TXT
-------------------------------------------------------------------------------
SYS
SYSTEM
OUTLN
MGMT_VIEW
MY_USER
ops$tkyte%ORA10GR2> @test
ops$tkyte%ORA10GR2> set echo on
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t
2 ( db_name varchar2(40), db_connect varchar2(40) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 'ora10gr2@loopback', 'ora10gr2' );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t2;
Table dropped.
ops$tkyte%ORA10GR2> create table t2( txt varchar2(80) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 db_link_exists exception;
3 pragma exception_init( db_link_exists, -2011 );
4 begin
5 for x in ( select * from t )
6 loop
7 begin
8 execute immediate '
9 create public database link ' ||
10 x.db_name || '
11 connect to ops$tkyte identified by foobar using ''' ||
12 x.db_connect || '''';
13 exception
14 when db_link_exists
15 then
16 dbms_output.put_line( 'skipping create of ' || x.db_name || '
already exists' );
17 end;
18
19 execute immediate 'insert into t2@' || x.db_name ||
20 '( txt ) select username from all_users where rownum <= 5';
21 end loop;
22 end;
23 /
skipping create of ora10gr2@loopback already exists
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t2@ora10gr2@loopback;
TXT
-------------------------------------------------------------------------------
SYS
SYSTEM
OUTLN
MGMT_VIEW
MY_USER
Sys is powerless
July 8, 2009 - 10am Central time zone
Reviewer: A reader
Hi tom,
I logged in as SYS and tried to drop a private dblink owned by another user.
It says
ORA-02024: database link not found
Though I do see it in dba_db_links.
How to make this happen ?
Followup July 8, 2009 - 3pm Central time zone:
database links are "special", there is no way to address them with a schema.
The implied/inferred schema is always the current user OR public.
Since you cannot specify the other user in the database link - you cannot drop it.
think about it - since you cannot specify a schema on a dblink (ever), what would happen if you had five users each with a database link named "foobar" and sys issues
drop database link foobar;
what database link could it/should it/would it drop?
Answer: unknown - we don't know what the intent was, we don't know what link to drop, we cannot drop anything
sys is powerless : DB version is 9208
July 8, 2009 - 10am Central time zone
Reviewer: A reader
Sys is powerless
July 8, 2009 - 5pm Central time zone
Reviewer: A reader
Thanx for the explanation.
At least Sys could have this special power to do.
Looks like this is the only thing Sys cannot do to other user objects.
Question :--
Why you cannot specify a schema on a dblink ?
If we are able to qualify other db objects with its owner why not a DB link . May be then Sys can
do the drop.
Followup July 8, 2009 - 7pm Central time zone:
because a database link is like a hostname,
create database line scott.foo.bar.hello.world .........;
is valid - scott is not a schema, there is no way to discern what a schema would be in that context.
You should never ever be using sys by the way, create you OWN account
Sys is powerless
July 9, 2009 - 10am Central time zone
Reviewer: A reader
Tom Thanx for your feedback.
May be we can put this as an enhancement request or a nice to have feature.
Now to drop the dblink we would need to know the password of the schema or change the password
,drop dblink and then revert the password thru identified by values if we dont know the password.
That is too much of work if we have to drop a lot of private db links.
It is a common requirement to give production copy for Qa purpose and we then clean up those
dblinks and give it to QA
Thoughts ?
More on ORA-02064
August 6, 2009 - 4pm Central time zone
Reviewer: Bob from Knoxville, TN
We are looking at the option of having a job submitted on the remote server to avoid the 2064
problem. If we pass parameters to and from the remote job using a table, can we use a trigger on
the table to submit the job when the data is inserted?
Or, can we take it one step further - what about having the trigger execute the procedure directly?
Or does this bring us back to the 2064 problem?
Followup August 7, 2009 - 8am Central time zone:
you would have to explain what is CAUSING the ora-2064 first.
August 17, 2009 - 4am Central time zone
Reviewer: James Mattew from Oklahama,USA
Hi Tom,
I have a question for you ? I have two databases supposedly ORCL and ORCL1 and I need to migrate
the data from ORCL to ORCL1 and both these databases are rhost connected with the dblinks connected
etc .. but I want to connect to the database ORCL from a third box (say NEW) which doesn't have
oracle and want to migrate ORCL to ORCL1 .. I need the imp/exp files of ORCL etc all on this NEW
box and need this to be fired from this box to ORCL1 via ORCL
Note : All the three boxes are connected in the same VPN and we can have the boxes connected using
rhosts/rsh/rcp
Is this do able ? Any help is appreciated
Thanks
Followup August 24, 2009 - 8am Central time zone:
you will have to install the oracle client on the "new" box if you want exp/imp to be present on the "new" box
or, export the file system of "new" box and mount it on ORCL, exp there to the mounted filesystem
Ora-12640
August 19, 2009 - 6am Central time zone
Reviewer: ashish from INDIA
Hi Tom,
I am getting Ora-12640 errors in my sqlnet.log. I am having oracle 10.1.0.2 with windows.
Present value of SQLNET.AUTHENTICATION_SERVICES is (NTS) and I can not set it to NONE. I have
windows users who access/login the database.
Also, this error get resolved when restarting the server. But appears anytime again after 3-4 days.
Can you suggest what can be the problem, which areas should I search to find the exact cause of
this error.
Regards
Ashish
from db1 to db2
August 21, 2009 - 3am Central time zone
Reviewer: Gayatri from India
The posts on this page are very useful. Thank you. Pl check my problem as below.
I have two databases db1 and db2. Day end I need to replicate from db1 to db2. I have plsql stored
procedure to do this for me. Now, I had to set up a new box for these servers.
Data from old to new servers is ported using Disk dump.
All such procedures having dblinks are not compiling (hanging forever). I need to kill the hanging
procedure explicitly to come out.
My old servers are still working fine....
Pl throw light on this....
|