TABLESPACE LEVEL PARAMETER

DESCRIPTION:

In this article, we are going to see how to export and Import table space using Datapump.

Tablespaces are the logical storage units which are used by the database to store separate objects, such as tables, types, PL/SQL code, and so on. Typically, related objects are grouped together and stored in the same tablespace.

Using expdp export utility of data pump we can export tablespaces. Exporting tablespace is also a way of taking logical backup of the tablespace of your database. Exporting tablespace means only the tables contained in a specified set of tablespace are unloaded along with its dependent objects.

Exporting tablespace means

Only the tables contained in a specified set of tablespace are unloaded
If a table is unloaded, then its dependent objects are also unloaded
Tablespace export unloads both object metadata and Data.

DIRECTORY.

Create a directory anywhere in your system or on your network where expdp export utility can save the exported files such as dump files and log files.

TABLE SPACE:

Using this TABLESPACES parameter we can specify the list of tablespace names which you want to export. For example here I have specified USERS.

[oracle@oracle19c ~]$ expdp directory=My_Dir tablespaces=users dumpfile=tab00.dmp logfile=tabs00.log

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 09:22:37 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "DATA"."SYS_EXPORT_TABLESPACE_01":  data/******** directory=My_Dir tablespaces=users dumpfile=tab00.dmp logfile=tabs00.log

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

. . exported "DATA"."TABLE01"                            5.515 KB       5 rows

Master table "DATA"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DATA.SYS_EXPORT_TABLESPACE_01 is:

  /u01/app/oracle/oradata/tab00.dmp

Job "DATA"."SYS_EXPORT_TABLESPACE_01" successfully completed at Mon Jan 25 09:22:51 2021 elapsed 0 00:00:09

Export activity completed successfully.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 09:23:37 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn data/pump

Connected.

SQL> select * from table01;

ID NAME

---------- ------------------------------

 1 aaa

 2 bbb

 3 ccc

 4 ddd

 5 eee

SQL> drop table table01;

Table dropped.

SQL> select * from Table01;

select * from Table01

              *

ERROR at line 1:

ORA-00942: table or view does not exist


SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Import the tablespaces using the Dumpfile

[oracle@oracle19c ~]$ impdp directory=My_Dir  dumpfile=tab00.dmp  logfile=tabs00.log

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 09:24:28 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Master table "DATA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DATA"."SYS_IMPORT_FULL_01":  data/******** directory=My_Dir dumpfile=tab00.dmp logfile=tabs00.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DATA"."TABLE01"                            5.515 KB       5 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DATA"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 09:24:36 2021 elapsed 0 00:00:03

Import Activity successfully completed.Now we can access the tablespaces.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 09:24:51 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn data/pump

Connected.


SQL> select * from table01;

ID NAME

---------- ------------------------------

 1 aaa

 2 bbb

 3 ccc

 4 ddd

 5 eee

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

DESCRIPTION:

DIRECTORY.

TABLE SPACE:

Import the tablespaces using the Dumpfile

Share this:

Like this: