Datapump Export Options Everything

Export/Import-->Datapump: ------------------------Steps to perform Export datapump: --------------------------------1.Create directory at specified location in unix prompt. $cd /u01/app/oracle/oradata/orcl $mkdir datapump_dir 2.Connect as a sysdba to database sqlplus "/as sysdba" 3.In sql prompt sql>create directory data_pump_dir as '/u01/app/oracle/oradata/orcl/datapump_dir'; 4.Grant permission to particular user sql>grant read,write on directory data_pump_dir on scott; 5.Invoke datapump from unix prompt $expdp scott/tiger directory=data_pump_dir1 dumpfile=scott_schema.dmp schemas=scott 6.Types of datapump export 1.Full database export 2.Schema level export 3.table level export 7.Full database export i)To export the full database, give the following command $expdp scott/tiger full=y directory=data_pump_dir dumpfile=fulldatabase.dmp job_name=fulldatabase ii)In some cases database in terrabytes,to overcome these space problems prvovide multible dumpfiles set like this. $expdp scott/tiger full=y directory=data_pump_dir dumpfile=full%u.dmp filesize=5g job_name=fulldatabase 8.Schema level export i)To export all the objects of particulrs schema, give the following command $expdp scott/tiger directory=data_pump_dir1 dumpfile=scott_schema.dmp schemas=scott ii)If you want to export the objects off multiple schemas $expdp scott/tiger directory=data_pump_dir1 dumpfile=scott_schema.dmp schemas=scott,test1,test2 9.Individual table level export i)We can take individual table export using datapump $expdp scott/tiger directory=dba_pump_dir dumpfile=tablelevel.dmp tables=emp,dept,locations ii)we can take tablespace level backup $expdp scott/tiger directory=dba_pump_dir dumpfile=tablespacelevel.dmp tablespaces=test,users,system,sysaux 10.Suspending and Resuming Export Jobs (Attaching and Re-Attaching to the Jobs) You can suspend running export jobs and later on resume these jobs or kill these

jobs using Data Pump Export. You can start a job in one client machine and then, if because of some work, you can suspend it. Afterwards when your work has been finished you can continue the job from the same client, where you stopped the job, or you can restart the job from another client machine. For Example, suppose a DBA starts a full database export by typing the following command at one client machine CLNT1 by typing the following command $expdp scott/tiger@mydb FULL=y DIRECTORY=data_pump_dir DUMPFILE=full.dmp LOGFILE=myfullexp.log JOB_NAME=myfullJob After some time, the DBA wants to stop this job temporarily. Then he presses CTRL+C to enter into interactive mode. Then he will get the Export> prompt where he can type interactive commands Now he wants to stop this export job so he will type the following command Export> STOP_JOB=IMMEDIATE Are you sure you wish to stop this job ([y]/n): y The job is placed in a stopped state and exits the client. After finishing his other work, the DBA wants to resume the export job and the client machine from where he actually started the job is locked because, the user has locked his/her cabin. So now the DBA will go to another client machine and he reattach to the job by typing the following command $expdp hr/hr@mydb ATTACH=myfulljob After the job status is displayed, he can issue the CONTINUE_CLIENT command to resume logging mode and restart the myfulljob job. Export> CONTINUE_CLIENT A message is displayed that the job has been reopened, and processing status is output to the client.