This is an interesting post by Tanel Poder on “Your read only accounts aren’t that read only”. Basically a read-only account with the select privilege only on a table can issue the lock on that table even though it does not have any DML privileges. This means that it could prevent any further DMLs (causing denial of service) to other valid sessions. This behavior is not new. But explanation, examples and work-around/semi-solution by Tanel are clear.
Dec 04 2007
SQL*Plus Copy
The SQL*Plus COPY command can copy data between two databases via SQL*Net.
Syntax:
USER1 @srcdb> copy
usage: COPY FROM [db] TO [db] [opt] [table] { ([cols]) } USING [sel]
[db] : database string, e.g., hr/your_password@d:chicago-mktg
[opt] : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
[table]: name of the destination table
[cols] : a comma-separated list of destination column aliases
[sel] : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.
Sample:
@Target database, the table initially does not exist.
USER1 @targetdb> select * from rep_tab; select * from rep_tab * ERROR at line 1:ORA-00942: table or view does not exist
@Source database, run copy command within SQL Plus.
USER1 @srcdb> copy from user1/user1@srcdb - > to user1/user1@target_host:1521/target_sid - > create rep_tab - > using - > select * from rep_tab; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 80. (long is 80) Table REP_TAB created. 1 rows selected from user1@SRCDB. 1 rows inserted into REP_TAB. 1 rows committed into REP_TAB at user1@host1:1521/targetsid.
@Target database, now table is copied here.
USER1 @targetdb> select * from rep_tab; A ---------- 1
This is a quick and easy way to copy data between two databases especially on where the usage of database link is restricted. It only requires SQL*Net service name (or host/port/service) and proper privileges.
