REMAP_TABLESPACE :
REMAP_TABLESPACE is used when the target database does not have the same set of tablespaces. Also, if you wanted to import the dump content to another tablespace which is having more free space.
Check DEFAULT_TABLESPACE of user,
SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME ='HARI'; DEFAULT_TABLESPACE ------------------ USERS
SQL> select table_name, tablespace_name from dba_tables where owner='HARI'; TABLE_NAME TABLESPACE_NAME ------------ ----------------- EMP USERS SYS_EXPORT_SCHEMA_01 USERS SYS_EXPORT_SCHEMA_02 USERS SYS_EXPORT_SCHEMA_03 USERS SYS_EXPORT_SCHEMA_04 USERS
Export the schema “HARI”
[oracle@18c ~]$ expdp system/oracle directory=test_dir dumpfile=remap.dmp logfile=remap.log schemas=hari Export: Release 18.0.0.0.0 - Production on Wed Aug 22 23:43:03 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** directory=test_dir dumpfile=remap.dmp logfile=remap.log schemas=hari Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "HARI"."EMP" 873.1 KB 100000 rows . . exported "HARI"."SYS_EXPORT_SCHEMA_04" 796.5 KB 1484 rows . . exported "HARI"."SYS_EXPORT_SCHEMA_03" 615.3 KB 1463 rows . . exported "HARI"."SYS_EXPORT_SCHEMA_02" 434.3 KB 1445 rows . . exported "HARI"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is: /u01/empdata/remap.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at Wed Aug 22 23:43:45 2018 elapsed 0 00:00:40
Drop user HARI present in USERS tablespace
SQL> drop user hari cascade; User dropped.
Create new tablespace USERSDEMO
SQL> create tablespace USERSDEMO datafile '/u01/app/oracle/oradata/PROD18C/userdemo.dbf' size 2g; Tablespace created.
[oracle@18c ~]$ impdp system/oracle directory=test_dir dumpfile=remap.dmp logfile=imp_remap.log remap_tablespace=USERS:USERSDEMO
Import: Release 18.0.0.0.0 - Production on Thu Aug 23 00:02:27 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=test_dir dumpfile=remap.dmp logfile=imp_remap.log remap_tablespace=USERS:USERSDEMO
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HARI"."EMP" 873.1 KB 100000 rows
. . imported "HARI"."SYS_EXPORT_SCHEMA_04" 796.5 KB 1484 rows
. . imported "HARI"."SYS_EXPORT_SCHEMA_03" 615.3 KB 1463 rows
. . imported "HARI"."SYS_EXPORT_SCHEMA_02" 434.3 KB 1445 rows
. . imported "HARI"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Aug 23 00:02:51 2018 elapsed 0 00:00:23
Check the default tablespace for user HARI
SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME ='HARI'; DEFAULT_TABLESPACE ------------------------------ USERSDEMO SQL> select table_name, tablespace_name from dba_tables where owner='HARI'; TABLE_NAME TABLESPACE_NAME -------------------- --------------- EMP USERSDEMO SYS_EXPORT_SCHEMA_01 USERSDEMO SYS_EXPORT_SCHEMA_02 USERSDEMO SYS_EXPORT_SCHEMA_03 USERSDEMO SYS_EXPORT_SCHEMA_04 USERSDEMO
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
