CPISOLUTION 22 23 Kensington Garden Square
cell 07983923439 
Understanding The Imdp 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 Imdphelp=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 |
Remap_schema Transform |
Transportable Tablespace | |
| Interactive Commands | Press Cntrl C To Reach Interactive Commands | |
| Add_File | Stop_Job |
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
- ALL
- METADATA_Only
- cause these are the options available
Dumpfile
- 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
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 indata corruption
KILL JOB This is for Killing a 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
REMAP_SCHEMA
Using this command you can import data from one schema to other schema
Even if the schema or username is not present it gets created.
Following is the syntax of creating Remap_Schema
Impdp scott/tiger@oracle directory=testdir dumpfile=bij.dmp remap_schema=scott:testscott
REMAP_DATAFILES
Remap_Tablespace
- Tablespace data are remapped to another tablespace.
Previousy using import if you needed to import data from one tablespace
And move it to other tablespace and specially if it had tables which contained LOBS etc it became cryptic.
The process involved were exporting the tables dropping gthe tables
And recreating the tables in different tablespace and importing using ignore=y since the tables are already present the rows gets copied into
The required tbalespace
Now in data pump it is more easier
We can use the
Remap Tablespace clause
Following is the example of how to use it
Impdp pauldragon/p dumpfile=tb01.dmp directory=testdir remap_tablespace=users:test
TRANSFORM
Transform tablespace has got further parameters
Transform name specifies the name of transform .
There are two options
SEGMENT_ATTRIBUTE Deafault =y ..If this specified then.
(Segment attributes like physical attributes,storage attributes,tablespaces and logging)
STORAGE
If value is specified as Y the storage clauses are included with appropriate DDL. The default is Y.
System tablespace or any sys objects cannot be imported using transportable tablespace
First step make the required tablesapce readonly
First check wether the tablespace is self contained by using
Dbms_tts.transport_set_check(‘tablespacenmae',TRUE);
YOU CAN CHECK ITF THERE IS VIOLATIONS BY Querying
Select * from TRANSPORT_SET_VIOLATIONS;
3 rd step export the tablespace metadata .. using the following command
Expdp system/oracle@oracle transport_tablespace=test directory=testdir dumpfile=bjtest01.dmp logfile=bijt.log
Now move the datafile to other required location . It could be the other computer too..Since it is a datafile the copy is faster
C:\data\test.dbf c:\newlocation\test.dbf
. Next make the tablespace readwrite ;
Now in the other database fire the following command.
Impdp system/oracle directory=testdir dumpfile=bjtest01.dmp logfile=bijt.log transport_datafiles=c:\newlocation\test.dbf
This results in fastest movement of data from tablespace from one database to the other.