Oracle Database Tutorial 53 : Data pump expdp - How to Export tablespace in Oracle Database

Key Takeaways

what's up internet I'm Manish from rebellionrider.com today's video is the second tutorial on the data pump in Oracle database Series in the previous video we learned how to export full database using expdp export utility of data pump if you haven't watched it then just for you link is in the description box and today we will learn how to export table spaces using expdp export utility of data pump in Oracle database exporting table spaces means only the tables contained in a specified set of table space are unloaded along with its dependent object exporting table space using expdp export bump utility is also a way of taking logical backup of table space of your database so let's move ahead and see how we can export the table space first log on to your database sis user v s DB privileges okay here we are now I'm connected to my database SS user now we have to decide which table space we want to export in order to do that first we need to know how many table spaces we have and what are their names for that we can query V do table space view provided by Oracle database as you can see I have six table spaces in my Oracle database but in your system it can be different from these six I want to export my users and example table space let's see how we can export these two table spaces using expdp export utility step one create a directory you can create a directory anywhere in your system or on your network if you remember in my previous tutorial I created a directory in my D drive by the name of data pump here it is now I will create another directory by the name of table space export inside this data bom directory the path of our table space export directory is D data pump table space export so our directory has been created let's move on to our second step step two create directory object and Grant it mandatory privileges let's get back to our Command Prompt once again and here we are we use create directory command to create a directory object let's see how this command starts with create directory keyword followed by expore tblsp which is the name of our directory object you can give whatever name you want to your directory object then we have keyword s which is followed by the directory path and closed in single code let's hit the enter directory created now we have to Grant read and write privileges on this directory object expore tblsp to the user from which you want to perform the export in my case I want to perform the export using HR user thus I will grant this privilege to my HR user this grant query is quite simple using this grant query we are granting the read and write privileges on the the directory table space export using the directory object expore tblsp to the user HR everything is set now it's time to perform the export as you can see right now I am on my SQL prompt and expdp is a command line utility which can only be executed on command prompt not on SQL prompt thus we have to come out of the SQL prompt for that simply write exit and hit enter here we are let me first clear the screen so now we are on the third and the last step of exporting table space using expdp export utility where we will perform the export of the table spaces as we decided in the beginning of the tutorial that we will export the users and example table spaces thus we will write the command to export these two table spaces let's see how let's take a look at this command at the starting we have expdp which is our data pump utility followed by the users credentials through which you want to perform the export though specifying the Sid is optional but it's a good practice for specifying a sid of the database just right at the rate followed by the Sid as I did here next we have directory parameter here we have to specify our directory object which we created in the Second Step as the value of this parameter this parameter tells export utility the location where all the files of export will get saved next we have dump file parameter dump files are the files which will contain all the exported data using this parameter you can set the name of your dump file just like here I have set the name as TBL space. DMP you can give whatever name you want if you want to have more than one Dum files then you can specify their names here separated by commas remember Dum files are written in binary language by server and they must not be tempered 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 DMP next we have loog file 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 lock file and at the end we have table space parameter first this parameter tells the data pump that we want to perform a table space export which means that we want to run a data pump expd utility in table space mode second using this table space parameter you can specify the name of the table space which you want to export for example here I have specified users and example table space let's get back to our Command Prompt and execute this expdp export command export is successful let's quickly take a look of our tabl space export directory as you can see here we have our Dum files and log files that's it for today hope you learned something you can also read my blog on the the same topic there you can find all these command which I performed here along with more in-depth knowledge thanks for all your love and support hope you all are enjoying my videos if you want to suggest some videos then comment below you can also comment your feedbacks and please hit the like button if you like my video that really makes me happy and keeps me motivated for doing more such videos also you can share my videos and blog links on your social and helps me reach out to more people and don't forget to subscribe this is Manish