EXPORT AND IMPORT DATAPUMP BACKUP USING TABLESPACE PARAMETER
In this blog, we are going to learn about how to do export/import data pump backup by using the tablespace parameter
[oracle@oracle Desktop]$ cd [oracle@oracle ~]$ export ORACLE_SID=Trainee [oracle@oracle ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 4 10:42:03 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 8620080 bytes
Variable Size 436209616 bytes
Database Buffers 754974720 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
——————–
READ WRITE
SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
USERS
TBS1
SAMPLE
6 rows selected.
SQL> create tablespace exporttb datafile ‘exporttb.dbf’ size 10m;
Tablespace created.
SQL> conn shan1/dba1
Connected.
SQL> create table exportb(id int,name varchar2(20)) tablespace exporttb;
Table created.
SQL> insert into exportb values(1,’emptydata’);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from exportb;
ID NAME
———- ——————–
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
ID NAME
———- ——————–
1 emptydata
12 rows selected.
SQL> commit;
Commit complete.
SQL> conn sys/dba as sysdba
Connected.
SQL> desc dba_directories;
Name Null? Type
—————————————– ——– —————————-
OWNER NOT NULL VARCHAR2(128)
DIRECTORY_NAME NOT NULL VARCHAR2(128)
DIRECTORY_PATH VARCHAR2(4000)
ORIGIN_CON_ID NUMBER
SQL> select owner,directory_name,directory_path from dba_directories;
OWNER
——————————————————————————–
DIRECTORY_NAME
——————————————————————————–
DIRECTORY_PATH
——————————————————————————–
SYS
DIR
/u01/exportfile
SYS
DIR1
/u01/exportfile
OWNER
——————————————————————————–
DIRECTORY_NAME
——————————————————————————–
DIRECTORY_PATH
——————————————————————————–
SYS
TABSPACE
/u01/tablespace
SYS
PAREXP
OWNER
——————————————————————————–
DIRECTORY_NAME
——————————————————————————–
DIRECTORY_PATH
——————————————————————————–
SYS
ORACLE_BASE
/u01/app/oracle
SYS
ORACLE_HOME
OWNER
——————————————————————————–
DIRECTORY_NAME
——————————————————————————–
DIRECTORY_PATH
——————————————————————————–
/u01/app/oracle/product/12.2.0.1/db_1
18 rows selected.
SQL> col owner for a20
SQL> col directory_name for a25
SQL> col directory_path for a30
SQL> select owner,directory_name,directory_path from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
——————– ————————- ——————————
SYS DIR /u01/exportfile
SYS DIR1 /u01/exportfile
SYS TABSPACE /u01/tablespace
SYS PAREXP /u01/exppar
SYS EXPORT_DIR /u01/app
SYS EXP_DIR /u01/app/oracle/export
SYS XMLDIR /u01/app/oracle/product/12.2.0
.1/db_1/rdbms/xml
SYS XSDDIR /u01/app/oracle/product/12.2.0
.1/db_1/rdbms/xml/schema
OWNER DIRECTORY_NAME DIRECTORY_PATH
——————– ————————- ——————————
SYS ORA_DBMS_FCP_LOGDIR /u01/app/oracle/product/12.2.0
.1/db_1/cfgtoollogs
SYS ORA_DBMS_FCP_ADMINDIR /u01/app/oracle/product/12.2.0
.1/db_1/rdbms/admin
18 rows selected.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
[oracle@oracle ~]$ expdp directory=tabspace dumpfile=exporttb.dmp logfile=exporttb.log tablespaces=exporttbExport: Release 12.2.0.1.0 – Production on Thu Jan 16 16:59:13 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: shan1
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “SHAN1”.”SYS_EXPORT_TABLESPACE_01″: shan1/******** directory=tabspace dumpfile=exporttb.dmp logfile=exporttb.log tablespaces=exporttb
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 “SHAN1″.”EXPORTB” 5.687 KB 12 rows
Master table “SHAN1”.”SYS_EXPORT_TABLESPACE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SHAN1.SYS_EXPORT_TABLESPACE_01 is:
/u01/tablespace/exporttb.dmp
Job “SHAN1”.”SYS_EXPORT_TABLESPACE_01″ successfully completed at Thu Feb 4 11:00:08 2021 elapsed 0 00:00:40
[oracle@oracle ~]$ !sqsqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 4 11:00:32 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> drop tablespace exporttb including contents and datafiles;
Tablespace dropped.
SQL> commit;
Commit complete.
SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
USERS
TBS1
SAMPLE
6 rows selected.
SQL> conn shan1/dba1
Connected.
SQL> conn sys/dba as sysdba
Connected.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
[oracle@oracle ~]$ impdp directory=tabspace dumpfile=exporttb.dmp logfile=impexporttb.logImport: Release 12.2.0.1.0 – Production on Thu Feb 4 11:08:25 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: shan1
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SHAN1”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SHAN1”.”SYS_IMPORT_FULL_01″: shan1/******** directory=tabspace dumpfile=exporttb.dmp logfile=impexporttb.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Failing sql is:
CREATE TABLE “SHAN1″.”EXPORTB” (“ID” NUMBER(*,0), “NAME” VARCHAR2(20 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “EXPORTTB”
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
Job “SHAN1”.”SYS_IMPORT_FULL_01″ completed with at Thu Feb 4 11:08:37 2021 elapsed 0 00:00:03
[oracle@oracle ~]$ !sqsqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 4 11:09:04 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 8620080 bytes
Variable Size 436209616 bytes
Database Buffers 754974720 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> conn sys/dba as sysdba
Connected.
SQL> create tablespace exporttb datafile ‘exporttb.dbf’ size 10m;
Tablespace created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
[oracle@oracle ~]$ impdp directory=tabspace dumpfile=exporttb.dmp logfile=impexporttb.logImport: Release 12.2.0.1.0 – Production on Thu Feb 4 11:11:31 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: shan1/dba1
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SHAN1”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SHAN1”.”SYS_IMPORT_FULL_01″: shan1/******** directory=tabspace dumpfile=exporttb.dmp logfile=impexporttb.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SHAN1″.”EXPORTB” 5.687 KB 12 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “SHAN1”.”SYS_IMPORT_FULL_01″ successfully completed at Thu Feb 4 11:11:55 2021 elapsed 0 00:00:15
[oracle@oracle ~]$ !sqsqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 4 11:12:07 2021
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 shan1/dba1
Connected.
SQL> select * from exportb;
ID NAME
———- ——————–
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
1 emptydata
ID NAME
———- ——————–
1 emptydata
12 rows selected.