Asset Suite / PassPortGeek

Using Asset Suite / PassPort Duty Free tools to copy Oracle data from one Oracle instance to another. Allows the use of a SQL where clause for selective unload and for the addition of data during loading.

PassPort ver 10.x.
Asset Suite ver 6.x.




Data unloading

Unix shell must have environment variables, $TOOLS_DIR, $PPDIR, etc, set for the source Asset Suite / PassPort region.

Copy $TOOLS_DIR/dftbunld into a working directory, example: $HOME/tmp.

Edit dftbunld:
a) comment out 2 occurrences of: “rm -f ${OSql} > /dev/null”.
b) change “tmp=/tmp” to “tmp=$HOME/tmp”.

Do not create directories $HOME/tmp/data/work. Running dftbunld will fail, but useful files will be created.

Run dftbunld as: ./dftbunld table

Within $HOME/tmp two files are created: tableL.sql and table.ctl. tableL.sql is the data extraction file. table.ctl is the load control file. A log file is created at $HOME/data/table.unl.

tableL.sql can be edited:
a) to restrict the data being unloaded by adding a WHERE clause,
b) tableL.sql might have to be updated with the table’s owner,
c) check that the spool commands: spool table.dat and spool off are uncommented.

Use sqlplus to run tableL.sql. Example:
cd $HOME/tmp
sqlplus userID/password@database < tableL.sql

If you don’t want to use the spool commands in tableL.sql, the above may also be run as:
sqlplus userID/password@database < tableL.sql > table.dat

Edit table.dat:
a) remove the leading and trailing records that record sqlplus information,
b) remove data you are not interested in,
c) add or edit data

Data loading

Unix shell must have environment variables, $TOOLS_DIR, $PPDIR, etc, set for the target Asset Suite / PassPort region.

Create directory “data” one level below your working directory, example: $HOME/tmp/data.

Place table.dat in the data directory.

Create directory “ldctl” one level below your working directory, example: $HOME/tmp/ldctl.

Place table.ctl in the ldctl directory.

Run the load utility from the working directory (you must be one directory level above directories data and ldctl:
cd $HOME/tmp
$TOOLS_DIR/dftbld table APPEND

APPEND adds data in table.dat to the Oracle table and REPLACE first deletes existing data and then loads.

A log file is created in $HOME/data/table.log. Read it carefully.