Step1:Create tablespace and Expdp:
SQL> select table_name,tablespace_name from dba_tables where table_name=’BK’;
TABLE_NAME
——————————————————————————–
TABLESPACE_NAME
——————————
BK
INBA
SQL> select name from v$datafile;
NAME
——————————————————————————–
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_h1lpjm44_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_h1lpl0nb_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_h1lplsxv_.dbf
/u01/app/oracle/oradata/ORCL/datafile/inba01.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_h1lplv5f_.dbf
SQL> alter user remo default tablespace inba;
User altered.
SQL> conn remo/remo
Connected.
SQL> select * from bb;
ID
———-
22
23
24
25
SQL> create table jk(id int);
Table created.
SQL> insert into jk values(33);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from jk;
ID
———-
33
33
33
33
SQL> commit;
Commit complete.
SQL> host
[oracle@trichydoyen ~]$ expdp tablespaces=inba dumpfile=tablespace_inba.dmp log=tablespace_inba.log (Don ‘t mentioned the file location or directory name in Tablespace Level)
Export: Release 12.2.0.1.0 – Production on Sun Jan 12 13:30:16 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “log=tablespace_inba.log” Location: Command Line, Replaced with: “logfile=tablespace_inba.log”
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting “SYS”.”SYS_EXPORT_TABLESPACE_01″: /******** AS SYSDBA tablespaces=inba dumpfile=tablespace_inba.dmp logfile=tablespace_inba.log reuse_dumpfiles=true
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”JK” 5.093 KB 5 rows
Master table “SYS”.”SYS_EXPORT_TABLESPACE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/u01/app/oracle/admin/orcl/dpdump/tablespace_inba.dmp
Job “SYS”.”SYS_EXPORT_TABLESPACE_01″ successfully completed at Sun Jan 12 13:30:46 2020 elapsed 0 00:00:23
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 12 13:31:03 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Step2:Drop the tablespace and recreate the tablespace then Impdp.
SQL> drop tablespace inba including contents and datafiles;
drop tablespace inba including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> show user
USER is “SYS”
SQL> shut abort
ORACLE instance shut down.
SQL> startup force
ORACLE instance started.
Total System Global Area 834666496 bytes
Fixed Size 8626192 bytes
Variable Size 583012336 bytes
Database Buffers 239075328 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.
SQL> drop tablespace inba including contents and datafiles;
Tablespace dropped.
SQL> conn remo/remo
Connected.
SQL> select * from jk;
select * from jk
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn sys/dba as sysdba
Connected.
SQL> create tablespace inba datafile ‘/u01/app/oracle/oradata/ORCL/datafile/inba01.dbf’ size 10m;
Tablespace created.
SQL> conn remo/remo
Connected.
SQL> select * from jk;
select * from jk
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn sys/dba as sysdba
Connected.
SQL> host
[oracle@trichydoyen ~]$ impdp tablespaces=inba dumpfile=tablespace_inba.dmp log=tablespace_inba.log (Don ‘t mentioned the file location or directory name in Tablespace Level)
Import: Release 12.2.0.1.0 – Production on Sun Jan 12 13:41:58 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “log=tablespace_inba.log” Location: Command Line, Replaced with: “logfile=tablespace_inba.log”
Master table “SYS”.”SYS_IMPORT_TABLESPACE_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_TABLESPACE_01″: /******** AS SYSDBA tablespaces=inba dumpfile=tablespace_inba.dmp logfile=tablespace_inba.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”JK” 5.093 KB 5 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “SYS”.”SYS_IMPORT_TABLESPACE_01″ successfully completed at Sun Jan 12 13:43:11 2020 elapsed 0 00:00:59
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 12 13:43:22 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> conn remo/remo
Connected.
SQL> select * from jk;
ID
———-
33
33
33
33
33