Exp/Imp in Oracle Applications

[Irfan]

Asallam O Allaykum,

What should be the ideal process of exporting a particular table in Oracle 11i and then importing it to another Oracle 11i instance?

What I am looking for over here is do we need to take care of triggers, constraints etc.

I have following table to export from Prod

RA_CUSTOMER_TRX_ALL

and import it to develpment instance.

It has 14 triggers. If any one can list out step by step the ideal process to do this activity then it would be of great help.

Jazaak-Allahu-khairan-Kaseera

Regards,
Irfan

[amirtai]

W.A

Dear Irfan

We Import / Export with a pipe, you can change given below scripts accordingly.

1. Make a pipe using the mnod command
2. Start the export using an export parameter file. Export to the pipe rather than a normal dump file.
3. At the same time start the import using an import parameter file. Import from the pipe rather than the normal dump file.
4. Clean up and remove the pipe

We used the following shell scripts:

1. Export:
============================
#!/bin/sh
passwd=$1
NLS_LANG=AMERICAN_AMERICA.UTF8;
export NLS_LANG;
#echo ==========================================
#echo Running script to Export your tables
#echo ??????????????
cat $oracle_home/connections/user.dat | awk -F= ?{print $2}?| exp PARFILE=/somedir/export.dat;

Your dat file will look like:

#!/bin/sh

#echo ==========================================
#echo niwc_export.dat script
#echo ??????????????

FILE=niwc.dmp
TABLES=(TABLE_LIST) #CSV
DIRECT=Y
LOG=export.log
COMPRESS=N
CONSISTENT=N
CONSTRAINTS=Y
FULL=N
GRANTS=Y
HELP=N
INDEXES=Y
RECORD=Y
ROWS=Y
STATISTICS=NONE

2. Import:
===========================
#!/bin/sh
passwd=$1
NLS_LANG=AMERICAN_AMERICA.UTF8;
export NLS_LANG;
#echo ==========================================
#echo Running script to Import your tables
#echo ??????????????
cat $oracle_home/connections/user.dat | awk -F= ?{print $2}?| imp PARFILE=/somedir/import.dat;

[Irfan]

Asallam O Allaykum,

Thanx for your reply, but you got me all wrong. I know the import export utility of Oracle. I am just concerned abt the integrity of Oracle Application table's here. You must be aware that we cannot update many of the tables in Oracle Applications at back hand, as it is not supported. Similarly export and import of many tables too are not supported. However, many a times DBA's are forced to do this with no option at hand. And this is the reason I want to know what should be the approach of an APPS DBA to do the same.

Regards,
Irfan