Step by step to test stream on 10gr2

db version: 10.2.0.4
test platform: windows xp
source db: orcl
dest db: orcl1
–default all execute on source database except specified
SQL> show parameter compati
NAME                                 TYPE        VALUE
———————————— ———– ————
compatible                           string      10.2.0.3.0
sga_max_size                         big integer 276M
sga_target                           big integer 276M
processes                            integer     150
timed_statistics                     boolean     TRUE
log_archive_dest_1                   string      LOCATION=D:\oracle\oradata\orcl\archive
log_archive_format                   string      ORCL_ARC%S_%R.%T
undo_retention                       integer     7200
remote_archive_enable                string      TRUE
global_names                         boolean     TRUE
open_links                           integer     10
job_queue_processes                  integer     10
parallel_max_servers                 integer     20
streams_pool_size                    big integer 44M

–enable [...]

oralce physical rowid structure

Since Oracle 8i, oracle physical rowid is in extended rowid format. Extended rowids show in 18 characters with base 64 encoding. The encoding characters are A-Z, a-z, 0-9, +, and /. It is stored in 10 bytes (80 bits).
An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:
The first 6 characters OOOOOO represent data object number, using [...]

Using SQL*Plus Copy Command to move data

SQL*Plus Copy Command usage：
COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query
Example:
SQL> copy from scott/tiger@orcl to scott/tiger@orcl create dept1 using select * from dept;
–You can ommit the from or to statement if the destination is as as the source database.

SQL> copy [...]

Compare Redo log size between commit outside of loop and inside of loop

Here is my test on commit outside of loop and inside of loop. It will show the generated redo size will be much less when commit outside of loop.
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.6.0 – Production
PL/SQL Release 9.2.0.6.0 – Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.6.0 – Production
NLSRTL Version 9.2.0.6.0 – Production
SQL> [...]

Scripts to simulate connections to oracle

UNIX, test.ksh
————————

#!/bin/ksh
i=0
while [ $i -lt 200 ]
do
i=`expr $i + 1`
sqlplus -S test/test@service @test.sql &
done
Windows, test.bat
————————
for /L %%G in (1,1,200) do start /b sqlplus username/password@service @test.sql

How to sorting and paging in oracle

1. Using the feature count(stopkey). Oracle will get rows in max rownum and then omit rows under min rownum.
select owner,object_name,object_id
from ( select rownum rno,a.*
from (select /*+first_rows*/ * from t where object_name < ‘Z’ order by object_id ) a
where rownum <= 30
)
where rno >= 20
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=53053 Card=30 Bytes=1800)
1 0 VIEW (Cost=53053 [...]