Datapump and export/import

Created user xxi and given necessaru priveliges.
And also created DIRECTORY=exp_dp_dir which is set to /d23/TEST/TEST_DVD_RESTORE/ICE. ( reqires for datapump)

Dmp files under /d23/TEST/TEST_DVD_RESTORE/ICE have been imported using data pump utility as below.


impdp system/xxx DIRECTORY=exp_dp_dir DUMPFILE=IGL_POS852_D012DMP.dmp logfile=imp_IGL_POS852_D012DMP.log REMAP_SCHEMA=xxi:xxz REMAP_TABLESPACE=APPS_TS_TX_DATA:USERS,APPS_TS_TX_IDX:USERS

impdp system/xxx DIRECTORY=exp_dp_dir DUMPFILE=IGL_POS852_D010DMP.dmp logfile=imp_IGL_POS852_D010DMP.log REMAP_SCHEMA=xxi:xxz REMAP_TABLESPACE=APPS_TS_TX_DATA:USERS,APPS_TS_TX_IDX:USERS

impdp system/xxx DIRECTORY=exp_dp_dir DUMPFILE=IGL_POS852_D001DMP.dmp logfile=imp_IGL_POS852_D001DMP.log REMAP_SCHEMA=xxi:xxz REMAP_TABLESPACE=APPS_TS_TX_DATA:USERS,APPS_TS_TX_IDX:USERS

impdp system/xxx DIRECTORY=exp_dp_dir DUMPFILE=IGL_POS852_H001.dmp logfile=imp_IGL_POS852_H001.log REMAP_SCHEMA=xxi:xxz REMAP_TABLESPACE=APPS_TS_TX_DATA:USERS,APPS_TS_TX_IDX:USERS


Dmp files under /d23/TEST/TEST_DVD_RESTORE/LEG have to be imported using imp utility.

Using below command,objects from WMS_LABEL_REQUESTS_HIST_WIP.dmp file have been imported.

$ imp userid=system/WEMXzX2X file=WMS_LABEL_REQUESTS_HIST_WIP.dmp log=imp_WMS_LABEL_REQUESTS_HIST.log fromuser=wms touser=xxz

one more eg:
"SQL> create or replace directory expdp_vrtx_dir as '/d01/VERTEXBKP/DUMPBKP';

Directory created.

SQL> grant read,write on directory expdp_vrtx_dir to system;

Grant succeeded.

$ expdp system/xxx DIRECTORY=expdp_vrtx_dir DUMPFILE=vertexo_expdp.dmp logfile=vertexo_expdp.log SCHEMAS=VERTEXO

Restore/Import VERTEXO schema
Note: VERTEXO schema will be imported only if its not there in SOURCE instance.
No need to import if below query returns rows in TARGET instance.
sqlplus ""/ as sysdba""
select object_type,count(*) from dba_objects where owner='VERTEXO'
GROUP BY OBJECT_TYPE;

OBJECT_TYPE COUNT(*)
------------------- ----------
SEQUENCE 12
TRIGGER 12
TABLE 325
INDEX 535
VIEW 7

Perform below steps to import VERTEXO schema only if no rows returned in above output.
$ sqlplus ""/as sysdba""

SQL> create or replace directory expdp_vrtx_dir as '/d01/VERTEXBKP/DUMPBKP';
Directory created.

SQL> grant read,write on directory expdp_vrtx_dir to system;

Grant succeeded.

CREATE TABLESPACE vertex_data datafile '/d03/ptch/db/apps_st/data/xxvertex_data01.dbf' SIZE 1000m
AUTOEXTEND ON
NEXT 500m
MAXSIZE 4096m;

CREATE TABLESPACE vertex_idx datafile '/d03/patch/db/apps_st/data/xxvertex_ind01.dbf' SIZE 1000m
AUTOEXTEND ON
NEXT 500m
MAXSIZE 4096m;
exit

# cd /d01/VERTEXBKP/DUMPBKP
impdp system/systempasswd directory=expdp_vrtx_dir dumpfile=vertexo_expdp.dmp SCHEMAS=VERTEXO



EXP/IMP examples:

exp eg:
$ exp transport_tablespace=y tablespaces=XXJDE_TS_DATA, XXJDE_TS_INDEX triggers=y constraints=y grants=y file=XXJDE.dmp log=XXJDE.log

exp and imp eg:
# more expLPSReviewTables.par
# Export of LPS Employee Review tables for support transition.
#
# Perform ""export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15"" prior to running.
#
# 20100603 BKWilson Recreated export After changing VARCHAR2(4000) columns
# into CLOB columns.
#
userid=fismim
FILE=exportLPSReviewTables.dmp
FULL=n
COMPRESS=N
GRANTS=N
INDEXES=N
CONSTRAINTS=N
CONSISTENT=Y
ROWS=Y
STATISTICS=NONE
TABLES=(FIS_MIM_RV_CONTROL,
FIS_MIM_RV_EMPL,
FIS_MIM_RV_OBJ,
FIS_MIM_RV_OBJ_TYPE,
FIS_MIM_RV_OBJ_NBR,
FIS_MIM_RV_RATING_TBL)
LOG=exportLPSReviewTables.log"
"$ more impReviewTable.par_bkp
# Import a FISMIM Review table into SYSADM schema as a test.
#
# ""export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15"", prior to running.
#
# 20100601 BKWilson Re-executed test with CLOB columns.
#
userid=mim
full=n
tables=(FIS_MIM_RV_CONTROL,FIS_MIM_RV_EMPL,FIS_MIM_RV_OBJ,FIS_MIM_RV_OBJ_TYPE,FIS_MIM_RV_OBJ_NBR,FIS_MIM_RV_RATING_TBL)
ignore=y
commit=y
statistics=none
buffer=50000000
file=exportLPSReviewTables.dmp
log=importLPSReviewTables.log"


to grant all privileges for all tables of a schema to another schema: SQL> begin
2 for i in (select distinct table_name from all_tables where owner='MIM')
3 loop
4 execute immediate 'grant all on '||i.table_name||' to apps';
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed."

Exporting a table and importing which belongs to apps schema

exp USERID=apps/apps file=CLX_value_set_update_temp_exp.dmp GRANTS=Y TABLE=CLX_VALUE_SET_UPDATE_TEMP

imp apps/st4ng file=CLX_value_set_update_temp_exp.dmp fromuser=apps touser=apps TABLES=CLX_VALUE_SET_UPDATE_TEMP

$ imp system/PcT3X45K FILE=expdat.dmp FULL=Y log=poimp.log

"Take export dump of concurrent tables
exp userid=applsys/qatapps09 file=conc.dmp log=exp.log
tables=FND_CONCURRENT_QUEUES,FND_CONCURRENT_QUEUES_TL,
FND_CONCURRENT_QUEUE_SIZE, FND_CONCURRENT_QUEUE_CONTENT



imp system/manager file=/d12/oradev/mine/XX_CASTLE.dmp fromuser=XX_CASTLE touser=XX_CASTLE log=XX_CASTLE_imp.log

imp sys/manager file=xxtm1.dmp fromuser=XXTM1 touser=XXTM1 log=xxtm1.imp.log

exp userid=system/password file=schema_name.dmp log=logfile owner=required schema

eg: How to Export a schema like MSDEM

exp userid=system/(passwd) file=msdem_CRP2.dmp log=msdem_CRP2.log direct=y feedback=1000 owner=MSDEM
exp " '/ as sysdba' " file=$REFRESH/msdem_CRP2.dmp log=$REFRESH/msdem_CRP2.log direct=y feedback=1000 owner=MSDEM