Instance Creating

select sid,serial# from v$session where username = 'USBANKSTG';

select 'alter system kill session ''' || sid || ',' || serial# || '''immediate;' from v$session where username = 'USBANKSTG';


--ORA-39097: Data Pump job encountered unexpected error -1427

DROP USER  USBANKSTG CASCADE;

DROP TABLESPACE USBANKSTG_DATA INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE USBANKSTG_IDX INCLUDING CONTENTS AND DATAFILES;

--Stop the DB services and delete the data and Index physical files.
--Start the DB services back

--Note : SID is case sensitive and must be sspecified in the appropriate case

CREATE TABLESPACE USBANKSTG_DATA DATAFILE 'C:\USBANK_STG_TBLSPACES\USBANKSTG_DATA.dbf' SIZE 3000M AUTOEXTEND ON NEXT 64 MAXSIZE UNLIMITED;
CREATE TABLESPACE USBANKSTG_IDX DATAFILE 'C:\USBANK_STG_TBLSPACES\USBANKSTG_IDX.dbf' SIZE 1000M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED;

CREATE USER USBANKSTG
IDENTIFIED BY USBANKSTG
DEFAULT TABLESPACE USBANKSTG_DATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;


GRANT RESOURCE TO USBANKSTG;
GRANT CONNECT TO USBANKSTG;
ALTER USER USBANKSTG DEFAULT ROLE ALL;
GRANT CREATE ANY TYPE TO USBANKSTG;
GRANT CREATE VIEW TO USBANKSTG;
GRANT CREATE SESSION TO USBANKSTG;
GRANT CREATE TYPE TO USBANKSTG;
GRANT CREATE SEQUENCE TO USBANKSTG;
GRANT CREATE ANY VIEW TO USBANKSTG;
GRANT CREATE ANY INDEX TO USBANKSTG;
GRANT CREATE TRIGGER TO USBANKSTG;
GRANT CREATE TABLE TO USBANKSTG;
GRANT ALTER SESSION TO USBANKSTG;
GRANT CREATE PROCEDURE TO USBANKSTG;
GRANT CREATE ANY TABLE TO USBANKSTG;
GRANT SELECT ANY DICTIONARY TO USBANKSTG;
GRANT CREATE MATERIALIZED VIEW TO USBANKSTG;
GRANT CREATE ANY TRIGGER TO USBANKSTG;
GRANT CREATE SYNONYM TO USBANKSTG;
GRANT UNLIMITED TABLESPACE TO USBANKSTG;
GRANT ALTER ANY TYPE TO USBANKSTG;
GRANT CREATE ANY PROCEDURE TO USBANKSTG;

ALTER USER USBANKSTG QUOTA UNLIMITED ON USBANKSTG_IDX;
ALTER USER USBANKSTG QUOTA UNLIMITED ON USBANKSTG_DATA;

--CREATE OR REPLACE DIRECTORY USB_PUMP AS 'C:\USBANKSTG_DUMP';

CREATE OR REPLACE DIRECTORY USB_PUMP AS '\\msi-l1196\DB_BKUP\Mar_21\';

--CREATE OR REPLACE DIRECTORY USB_PUMP AS '\\msi-aio90\LEN\DUMPAPR132015\';
GRANT READ, WRITE ON DIRECTORY USB_PUMP TO USBANKSTG;
GRANT READ, WRITE ON DIRECTORY USB_PUMP TO PUBLIC;
grant create any directory to USBANKSTG;


GRANT READ, WRITE ON DIRECTORY SYS.USB_PUMP TO EXP_FULL_DATABASE;
GRANT READ, WRITE ON DIRECTORY SYS.USB_PUMP TO IMP_FULL_DATABASE;

Note : When importing the dump present in the local machine we need to map the network drive by right clicking on the "My Computer" and Map network drive and specify the folder path in the folder option.
On doing so we will find another drive created in my computer with the folder name of the shared folder

impdp USBANKSTG/USBANKSTG@usbnkcrdb DIRECTORY=USB_PUMP DUMPFILE=PRODDUMPMAY52014.DMP LOGFILE=LOG_MAY6_2014.log FULL=Y REMAP_SCHEMA=USBANKPROD:USBANKSTG REMAP_TABLESPACE=USBANK_DATA:USBANKSTG_DATA,REMAP_TABLESPACE=USBANK_IDX:USBANKSTG_IDX TRANSFORM=OID:N

impdp USBANKSTG/USBANKSTG@usbnkcrdb DIRECTORY=USB_PUMP DUMPFILE=DUMPAPR132015.DMP LOGFILE=LOG_Apr21_New_2015.log FULL=Y REMAP_SCHEMA=USBANKPROD:USBANKSTG REMAP_TABLESPACE=USBANK_DATA:USBANKSTG_DATA,REMAP_TABLESPACE=USBANK_IDX:USBANKSTG_IDX TRANSFORM=OID:N

impdp USBANKSTG/USBANKSTG@db11203 DIRECTORY=USB_PUMP DUMPFILE=DUMPAPR132015.DMP LOGFILE=LOG_Apr21_New_2015.log FULL=Y REMAP_SCHEMA=USBANKPROD:USBANKSTG REMAP_TABLESPACE=USBANK_DATA:USBANKSTG_DATA,REMAP_TABLESPACE=USBANK_IDX:USBANKSTG_IDX TRANSFORM=OID:N