Using Data pump Utility for Export and Import.
Activity : Exporting tables from one environment to another environment.
Connect to the Source Machine first.
CONN / AS SYSDBA
ALTER USER sbal012 IDENTIFIED BY password ACCOUNT UNLOCK;
CREATE OR REPLACE DIRECTORY test_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY test_dir TO sbal012
expdp PSHCM/ACC3SS1U@pshcmu1 tables=PS_A_HC_STAGING_ER,PS_A_HC_DED_BAL_ER directory=TEST_DIR dumpfile=PS_STAGING_BAL.dmp logfile=expdpPS_STAGING_BAL.log
Note*: Export Dump is prepared and placed in the test_dir ie: /tmp
used winSCP to move the dump to the Target Machine
On the target Machine
CONN / AS SYSDBA
ALTER USER sbal012 IDENTIFIED BY password ACCOUNT UNLOCK;
CREATE OR REPLACE DIRECTORY test_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY test_dir TO sbal012
impdp PSHCM/ACC3SS1U@pshcmd1 tables=PS_A_HC_STAGING_ER,PS_A_HC_DED_BAL_ER directory=TEST_DIR dumpfile=PS_STAGING_BAL.dmp logfile=impdpPS_STAGING_BAL.log
While Importing i received an error stating the table already exsists -- Therefore i had to backup the tables and drop them before importing.
Taking Backup of Tables:
SQL >create table table_name_bkp as select * from table_name
SQL> drop table table_name;
And executed the impdb statement to get the export to run successfully.
Traditional Import and Export
imp PSHCM/ACC3SS1D@pshcmd1 file='/tmp/export.dat' log='/oracle/ps/exports2/scripts/andy/export/export238294.log' tables='PS_A_HC_STAGING_ER'
