Data Pump Tablespace Mode.
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.
Let’s see how we can export tablespace using expdp export utility provided by Data pump in Oracle Database
Before moving ahead with the export of tablespace we have to decide which tablespace we want to export. In order to do that first we need to know how many tablespaces we have and what are their names? For that we can query “v$Tablespace” view provided by oracle database.
SQL> SELECT name FROM v$tablespace;
This query will return the names of all the tablespace available in your Database.
Note here that you must execute this query as Sys user with Sysdba Privileges as V$tablespace view is only available for privilege user and not for any unprivileged users.
Step1. Create a 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. If this export is a part of your backup strategy then it’s advisable to avoid making the folder on the same partition which also contains your Oracle home directory or OS bootable files.
Note here that this step must be performed by privileged user such as sys or system and directory must be created on server system rather than client system.
Say I created a directory by the name of Tablespace export for the demonstration and better understanding of the concept. The path of this directory is:
D:\ Data Pump\ Tablespace Export
Step2. Create a Directory Object and grant it mandatory privileges.
This step 2 is divided into few sub steps which you have to follow. Moreover it should be done by privileged users such as sys on server side.
2.1. Log on to database as sys user
For making a directory object, log onto your database as sys user with sysdba privileges
C:\> sqlplus / as sysdba
2.2. Create Directory Object
To create a directory object we use CREATE DIRECTORY command.
SQL> CREATE DIRECTORY exp_tblsp AS ‘ D:\Data Pump\Tablespace Export ’;
Here in this query exp_tblsp is a directory object (you can give whatever name you want to your directory object) which is just a name mapped over a directory path. Or you can say that it’s just a pointer pointing to a directory which you want your expdp utility to use when storing all exported files.
Mind here CREATE DIRECTORY command will not create any actual directory in your system. This command only helps you in creating a directory object.
2.3. Grant Read and Write Privileges on The Directory
After creating a directory object we have to grant read and write privileges on this directory object exp_tblsp to the user from which you want to perform the export. In my case I want to perform the export using my HR user thus I will grant these privileges to my HR user.
GRANT read, write ON DIRECTORY exp_tblsp TO hr;
Suggested Reading: How to grant System Privilege.
This grant query is quite simple. Using this grant query we are granting the read and write privileges on the directory tablespace export using the directory object exp tblsp to the user HR.
Step 3: Export the tablespace
Now that we have done all the required settings, we are good to go.
C:\> expdp hr/hr@ORCL DIRECTORY = exp_tblsp DUMPFILE = tablespace.dmp LOGFILE = tblsp_log.log
TABLESPACES = USERS,EXAMPLE;
Note here that expdp is an executable utility thus it must be executed on command prompt rather than the SQL prompt. Attempt to execute expdp command on SQL prompt will raise an error. To come out from the SQL prompt you just need to write EXIT on your SQL prompt and hit the enter button.
Let’s take a look at the command.
Expdp: At the starting we have expdp which is our data pump utility. Followed by the user credentials through which you want to perform the export. Although specifying the SID is optional yet it’s a good practice. For specifying a SID of the database just write @ followed by the SID as I have done it here.
Directory: Next we have DIRECTORY parameter. Here we have to specify our directory object which we created in the 2nd step as the value of this parameter. This parameter tells export utility the location where all the files of export will get saved.
DUMPFILE: Next we have DUMPFILE parameter. Dump file are the files which will contain all the exported data. Using this parameter you can set the name of your dump files. Just like, here I have set the name as tablespace.dmp. You can give whatever name you want. If you want to have more than 1 dump file then you can specify their names here separated by commas. Remember Dump files are written in binary language by server and they must not be tampered by any user. Regarding the extension of your dump file, you can give whatever extension you want to it but it’s recommended as well as a good practice to give them default extension which is dot (.) dmp.
LOGFILE: Next we have LOGFILE parameter. Using log file parameter you can set the name of your log files. Log files are human readable files which consist of all the logs of your export. Log files are very helpful in tracking the status of your export. You can set whatever name you want to your log file.
TABLESPACE: At the end we have tablespace parameter.
There are two significance of tablespace parameter in the above expdp command
- This parameter tells the data pump that we want to perform a tablespace export which means that we want to run data pump expdp utility in tablespace mode.
- Using this tablespace parameter you specify the list of tablespace names which you want to export. For example here I have specified USERS and EXAMPLE tablespace.
Restrictions with Tablespace Export (Tablespace Mode)
The length of the tablespace name list specified for the TABLESPACES parameter is limited to a maximum of 4 MB, unless you are using the NETWORK_LINK to an Oracle Database release 10.2.0.3 or earlier or to a read-only database. In such cases, the limit is 4 KB.
That’s all about Tablespace export. Hope it was helpful. Kindly please share it on your social media and help me reach out to more people. Thanks & have a great day!