CPISOLUTION 22 23 Kensington Garden Square
cell 07983923439
Understanding The Expdp Command
Go to command prompt .. Say in windows better to use cmd command
Since when in dos prompt you can use the doskey command.
- Type expdp help=y
- Will show you the available options available in expdp command
- Expdp help =y
| ATTACH | FLASHBACK_SCN | JOB_NAME |
|---|---|---|
| CONTENT | FLASHBACK_TIME | NETWORK_LINK |
| DUMPFILE | FULL | NO LOGFILE |
| ESTIMATE/ ESTIMATEONLY | HELP | PARRALLEL |
| FILE SIZE | EXCLUDE /INCLUDE | QUERY |
| Interactive Commands | Press Cntrl C To Reach Interactive Commands | |
| Add_File | Kill Job | |
| Continue Client | Exit Client | Parrallel/Status |
Attaches to the existing job .
- You can know the job name by query dba_datapump_jobs
- Following is the command you use to attach to a job
- Expdp system/oracle@oracle attach=SYS_EXPORT_SCHEMA_02
JobName
You can decide wether you need the data which is being exported
- Data_Only = Cotains data of the table
- ALL
- METADATA_Only Is data about data ..Means table structure , Tablespace structure ,
dumpfile=test%U.dmp
The %U incicates unique number to be generated
- Options has statistics or blocks
- Blocks it will estimate the total size in blocks of the dump file
- While estimation using statistics requires proper statistics to be Generated on the objects.
You don't need to export the dump file and you can get a estimate of block size for the data .
The command to do the following are as
• grant read,write on directory cpisolution to scott;
expdp scott/tiger NOLOGFILE=y ESTIMATE_ONLY=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "SCOTT"."EMP2" 21 KB
. estimated "SCOTT"."DEPT" 223 KB
. estimated "SCOTT"."DEPT2" 32 KB
. estimated "SCOTT"."EMP" 12KB
. estimated "SCOTT"."SALGRADE" 24 KB
. estimated "SCOTT"."BONUS" 0 KB
Total estimation using BLOCKS method: 173 KB
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:51
Note that the ESTIMATE_ONLY parameter causes export to estimate the space
needed, by multiplying the number of database blocks used by the target
objects times the appropriate block sizes.
File you can use this command to see to it the size of dump file is of a particular size .Now this you can find useful when certain operation file donot require size of files to be more than certain size..
This command below generates multiple dumpfile of file size 500 mb.
Expp scott/tiger directory=tes
expdp system/manager directory=test full=Y dumpfile=test%U.dmp FILESIZE=550m content=all
Able to get the dumpfile names as test01.DMP , test02.DMP ..... in the DIRECTORY location.
FLASHBACK_SCN (This is used with network_link parameter)
FLASHBACK_TIME (Network _lin Parmeter)
FULL
HELP
Exclude Exclude specific objects
INCLUDE
JOB_Name
LOGFILE
NETWORK_LINK
NOLOGFILE
PARALLEL
PARFILE
D:\> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_q.dmp
LOGFILE=expdp_q.log TABLES=emp,dept QUERY=emp:\"WHERE ename LIKE 'A%'\"
-- Example Unix platforms:
-- Note that all special characters need to be 'escaped'
% expdp scott/tiger DIRECTORY=my_dir \
DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \
QUERY=emp:\"WHERE ename LIKE \'A\%\'\"
SCHEMAS
STATUS
You can attach to job fire the status command to see the progress of a job.
And detach from a job.
TABLES
TABLESPACES
TRANSPORT_Full_check
TRANSPORT_TABLESPACES
VERSION
Understanding How to use Exclude and Include Clause for Data Pump
Exclude
Exclude certain metadata from the export operation .Note that for any object excluded anydependent objects are also excluded. For example exclusion of a table also excludes any indexes,triggers,constrains and reated table data.
You can also use wild card characters and SQL Statements to exclude specific ranges of objects
EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]
expdp <other_parameters> SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:”IN ('EMP','DEPT')”;
Include
allows you to export or imort certain objects which you will like to import.
INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]
examples:
impdp <other_parameters> SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION, PROCEDURE,TABLE:”='EMP'”
The name_clause is a SQL expression that is used as a filter on the object names of the object. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. If no name_clause is provided, all objects of the specified type are excluded/included. The name clause must be separated from the object type with a colon.
Examples of operator-usage:
EXCLUDE=SEQUENCE
or: EXCLUDE=TABLE:”IN ('EMP','DEPT')”
or: EXCLUDE=INDEX:”= ‘MY_INDX'”
or: INCLUDE=PROCEDURE:”LIKE ‘MY_PROC_%'”
or: INCLUDE=TABLE:”> ‘E'”
The parameter can also be stored in a par (parameter file) as shown
Parameter file:exp.par
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:”IN ('EMP', ‘DEPT')”
expdp system/manager parfile=exp.par
Following are the important tables while handling expdp
And impdp since it helps you determine which objects can be named
- Databae_export_objects
- Schema_export_objects
- Table_export_objects
Following are the contents of Database_Export_Objects
Object_Path
Table/grant
Schema/package
Trigger
Now if you see the object path.. The options are available for table level export,
Schema level export or database level export.
Following is a example of including only indexes in your import caluse.
Export DP Interactive Commans are as below to reach the interactive mode
Press control c. ..and You reach a screen which is the interarctive mode.
Here you can
ADD_FILE Add a file to list of dump files.. You might need this command to store the new dumpfiles to be created in some different drive etc since the space of the drive gets filled
Stop_job It stops the job after worker process job is stopped Stop_job immediate it stops the job immediately ..It doesn't wait for worker process job to be stopped and it doesn't result in data corruption
KILL JOB This is for Killing a job. You type KillJob and it will ask you wether to kill the attached job
CONTINUE CLIENT Continuing a stopped job.
EXIT_CLIENT
PARALLEL
parameter in expdp or impdp means your job will use multiple threads for execution of export jobs.
You can change the degree of parallelism on fly by using the attach command.
If you set the parralellism to 3 you will have 3 process to generate three export files
Following are the syntax for exporting files in parallel of 3
Expdp system/oracle@oracle DIRECTORY=dumpdir dumpfile=bij%U.dmp PARALLEL=3
STATUS
Status shows the status of the jobs.executed.
If you need to take a dump file of a readonly database using database link is the way since the master table required to be created can be created on the database which is the target.
Now if you have prime database
As York
And you have a database in a branch
Now if you need to import data from york to the new branch
Without utilizing the space in prime database the fastway will be to use thenetwork link parameter
To use following are the steps involved
In the branch database create a database link
Create database link york connect to scott identified by tiger
Using ‘ york ';
York is a service name in branch database created using sql net easy.
To import the data
Just fire the command
Impdp scott/tiger@branch directory=testdir dumpfile=jim.dp
Tables=scott Network_link= york
In The source database the export command is executed
And all metadata and data is generated there and transferred via network_link parameter to target database