Transfering data from one database table to another database table

[TomazZ]

Originally posted by abhaysk
Password of Remote DB can be known to any user who has got sufficient privilages to see sys objects.

There should not be a lot such users.

Even when DB link is private to the user x, user y can know the login credentials of the remote DB.

How?

Many bugs reported while using DB Link.

Many bugs reported while using Oracle DB... should we stop using it?

Now how would DB Link slow up process

* NOT IN clauses wont just turn up over DBs if Tables in Q are big.

* Even EQUI joins will eat time, due many round trips via N/W.

Rather, you can do some think like this ( On Local Sever )

* Create Temp_Table.

* Load data from remote DB through COPY ( by setting arraysize conisderably high ).

* Pick the data from temp_table load it to main table using Insert..Select ( the desired rows ).

For simple task as "insert into t1 select * from t2@db2" it is very convenient to use db_links (not much bugs on this one, i think).
If you need to do "not in" joins (i almost never do anyway) or something like that, you can still create temp table and insert in it through dblink, and process data single-sided afterwards.

[abhaysk]

Originally posted by adewri
Any way you need the password for connecting to remote database.

Amar :

PS, I said if only user X is intended/allowed/privilaged/right owner to access data from remote DB, & user Y didnt have privilaged/right owner to access data from remote DB, he can do by seeing PWD from sys objects & connect and access ... this is what I meant.

Rather you can skip hardcoding user/pswd while DB creation. This is work around.If you do this, it will call for User/Pswd to be same on local/remote servers.

Originally posted by adewri
When ??? never faced such problem. Have been replicating databases , moving data to and fro via PL/SQL for last three years over db links, never faced such problem.

Please see the link.

http://www.idg.net/ic_1311169_9677_1-5046.html

Originally posted by adewri
Name a few. Database link is nothing but simply a connect username/password@remotedb.

Ok, lemme show one of the flaws that i encountered.

Remote server FUNKY :

FUNKY:SYS> grant dba to abhay;

Grant succeeded.

-- At this time x, User Abhay has DBA role Privilage in DB Funky

Local Server TEST :

TEST:ABHAY> create database link funky connect to abhay identified by abhay using 'FUNKY';

Database link created.


Remote server FUNKY :

-- Now I revoke this privilage in the reomte server by SYS ACC.

FUNKY:SYS> revoke dba from abhay;

Revoke succeeded.

FUNKY:SYS> select * from dba_role_privs where grantee='ABHAY';

no rows selected

FUNKY:SYS> select * from dba_sys_privs where grantee='ABHAY';

no rows selected

FUNKY:SYS> conn abhay
Enter password:
ERROR:
ORA-01045: user ABHAY lacks CREATE SESSION privilege; logon denied


Local Server TEST :

-- Just see, how DB link works.

TEST:ABHAY> desc sys.obj$@Funky
 Name                                                                                                                                                                                                   
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 OBJ#                                                                                                                                                                                                   
 DATAOBJ#                                                                                                                                                                                               
 OWNER#                                                                                                                                                                                                 
 NAME                                                                                                                                                                                                   
 NAMESPACE                                                                                                                                                                                              
 SUBNAME                                                                                                                                                                                                
 TYPE#                                                                                                                                                                                                  
 CTIME                                                                                                                                                                                                  
 MTIME                                                                                                                                                                                                  
 STIME                                                                                                                                                                                                  
 STATUS                                                                                                                                                                                                 
 REMOTEOWNER                                                                                                                                                                                            
 LINKNAME                                                                                                                                                                                               
 FLAGS                                                                                                                                                                                                  
 OID$                                                                                                                                                                                                   
 SPARE1                                                                                                                                                                                                 
 SPARE2                                                                                                                                                                                                 
 SPARE3                                                                                                                                                                                                 
 SPARE4                                                                                                                                                                                                 
 SPARE5                                                                                                                                                                                                 
 SPARE6                                                                                                                                                                                                 

TEST:ABHAY> select name from v$database@Funky;

NAME
---------
FUNKY

TEST:ABHAY> drop database link Funky;

Database link dropped.

TEST:ABHAY> Create Database Link Funky Connect to Abhay Identified By Abhay Using 'FUNKY';

Database link created.

TEST:ABHAY> Desc SYS.OBJ$@Funky
ERROR:
ORA-01045: user ABHAY lacks CREATE SESSION privilege; logon denied
ORA-02063: preceding line from FUNKY

Irrespective of the privilage revoked after DB Link Creation, the local server will be able to access data as it were before.

Search DATABASE LINK + Bugs in metalink, you will see hundreds of threads/notes/bugs.

Originally posted by adewri
What ever you said will be true for a normal connection as well.

Amar, tell me comparing data over DB link and insert/update will be fatser or if some temp_table exists in local machine will be fast?????

And if u use clauses like NOT IN over DB, it would not just turn up...IMHO using this clause and it took 6 hours but didnt insert single record from remote DB to Local.

BTW, what's the harm if we COPY & comapre data & laod..?
Wouldnt that be wise if it were BIG tables?

Abhay.

[abhaysk]

Originally posted by TomazZ
How?

SYS.LINK$

Originally posted by TomazZ
Many bugs reported while using Oracle DB... should we stop using it?

Tomazz Ofcourse there are, but some potentially critical bugs in DB link. I aint saying you dont use, but jus listing some flaws & our DBA gurus suggest not to use DB link.

Originally posted by TomazZ
For simple task as "insert into t1 select * from t2@db2" it is very convenient to use db_links (not much bugs on this one, i think).
[/B]

They do, check on metalink.

Abhay.

[TomazZ]

Originally posted by abhaysk
Irrespective of the privilage revoked after DB Link Creation, the local server will be able to access data as it were before.

Are you sure that's not because db_link was still using same old session?
Because that's expected behaviour:
If you revoke privilege from user A, user A will still have this privilege if the session started before revoke.

[TomazZ]

Originally posted by abhaysk
SYS.LINK$

So this was basically repeating the first point you made about having sys privileges. I missunderstood.

[pando]

If you need to use a database link you will have to use it, not everyone can see sys objects, the one who can see are those who suppose to be the DBAs no? It´s just your security policy

[adewri]

Abhay what ever you showed happened because you were logged in the same session, thats a normal oracle behaviour, nothing new. Had you logged in another session you will not be able to select from the remote database. Try it.

And no one in his right mind gives a dba priviliges over a remote database link. Never seen any one do that.

And about bugs you search for any word related to oracle, you will get lots of links related. So whats the big deal ? We all know Oracle is full of bugs, but still we use it as we don't have anything better

[adewri]

And related to that buffer overflow issue, Oracle has released the patch. But is no where written that using DB link is a bad practice.

[akhadar]

If this a problem with DBlink for example Replication which works purely on the this would have failed long back and Oracle Would have discontinued this concept.

[abhaysk]

Originally posted by adewri
Abhay what ever you showed happened because you were logged in the same session, thats a normal oracle behaviour, nothing new. Had you logged in another session you will not be able to select from the remote database. Try it.

Amar/Tomazz :

I thought, whenever we query on remote server via DB link it would create session and execute commands there...in this regard how will the session that is connected to local DB will affect...

but i think its other way...

Abhay.