Pesho said...Good blog
Amit Gawas said...Very Good
Kathir said...Wow!!! Great...............
Senthil Alagu said...Really Gr8. I had never known about this before. Thanks.
J Gil said...Clear & simple
Håkan said...Valuable article
Star Nirav said...Awesome explanation... it would be more helpful if you add common errors and explanation during this exercise. I mean, you should give proactive information if we encounter some common errors due to silly mistakes.
Rest... You are one of the best. Hats off to you :)
Star.Nirav@hp.com (India) / +919227258878
jcookie said...I am new to Oracle, and was asked to do a schema export/import. Your article is very clear and well written, I wish you had a book on all of Oracle.
G Hay said...Hi,
The example doesnt seem to work for external tables. (I am using Oracle 10g Express Edition)
If you create an external table with a 'select from' and specify a dmp file which was created using expdp as in the early part of the examples then try to do a select on the external table you get an error.
Also if you try to import a dmp file generated by an external table created with 'select from' but import using impdp you get an error.
It would appear (and this is borne out by the difference in size of the dmp files generated for the same table using the 2 different export techniques) the 2 types of .dmp file are incompatible with one another. Have I missed a command somewhere to force them the same format?
Anyway not too good as I want to use impdp and expdp and be able to query external tables using the impdp generated dump files. Anyone help?
G Hay said..... typo .
If you create an external table withOUT a 'select from' and AS
This is a limitation of the external table functionality. Currently, it will only work for files that were created using the "AS" clause. It won't work for files created by the normal datapump exports using dpexp.
It would be nice if this were included in a future release, but as of 10g it is not possible.
Suresh said...Hi Tim,
Thank you. Very useful blog.
Bikram said...Hi,i think this is the best site which describes about DATA PUMP so clearly.
Sandeep Kumar said...Grrreat !!
Sajith OD said...Simple and Good.......
rajeshkumar said...simple and superb. thanks u very much for ur article. thanks a lot.
Raman, USA said...Great! I would suggest adding a line at the very beginning of the page:
"create the folder manually then create the directory object pointing to that folder"
I made a note in the article about it. It does make things a little clearer for the uninitiated. :)
bals said...one of the gr8 sites for oracle
SRINATH NANDURI said...I really like this article... One of the things that need to be added is the sql to see if the directory object got created...
select * from ALL_DIRECTORIES;
Nawfal said...This is very useful thanks a lot.
dnunknown (Steve) said...Hey Tim,
Great article, I ran into some issues in 220.127.116.11 though w/ the INCLUDE and EXCLUDE portion using command line in RHEL 5, had to use escape characters before the special characters as so;
exclude=TABLE:\"IN \(\'TABLE1\', \'TABLE2\'\)\"
doing some research if using a parameter file for the export the escape characters are not needed (not tested though)
Jeff Moss said...As per dnunknown (Steve)...I get same issue on 10.2.0.4 on AIX, whereby I need to have the escape character (\) before the single and double quotes, when using INCLUDE...LIKE...found the suggestion on OTN forum (https://forums.oracle.com/forums/thread.jspa?threadID=951475).
Useful, as ever.
Sanjay Sonwani said...Hi,i think this is the best site which describes about oracle database adminstration.
Tim... said...Thanks. :)
Mindaugas said...Thanks, helped to start using datapump on 10g
Juan said...I donÂ´t know if is to late but thanks so much again for the way you present the info.
It's never too late to say nice things. :)
Ygor Alberto said...I have a problem, after installing Oracle XE 11g on my virtual machine, I can't use the commands on prompt to import/export tables/databases. What should be the reason?
You need to make sure the ORACLE_HOME/bin path is in your PATH variable. That way the OS will know where to look for them. Either that, or use an explicit path when calling them.
Lin said...External table using dump is a good one. Is it possible to use a dump to create external table taken using exp utility?
Unfortunately not. It must be done using this mechanism.
santosh said...Actually I was looking for implementing expdp on Linux oracle server, now I got an overall idea. Thanks for sharing.
Ahmed said...hi, i am using oracle DB 11g ( Express and Enterprise ), i created a table with virtual column, exported with both exp and expdp but can't import, always oracle is showing error(s). is there any limitation? oracle says Partitioning allowed on virtual column, if i had few schemas and had this virtual column on every schema, making backup using exp or expdp then ... ? is there any solution?
And the error message is?
Srinivasa said...Hi Tim, I have been reading your articles for many years. I just want to check using expdp can we take incremental OR change or rows for particular table?
ex: Emp table has 10 rows, I use expdp consistent=Y flashback_scn=78908909 after modify some data in the emp table (say added 5 new records and updated existing 5 records). In this scenario, when I tried to use expdp command, takes whole data
It is not designed to do this. If you have some way to identify the data, you can do it with a query. Like "WHERE created_date > ....", but I don't think this is a reliable method.
Muhammad Idrees said...there is an error in the QUERY part.
expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=full.dmp logfile=expdp_full.log query=SCOTT.EMP,SCOTT.DEPT:'"WHERE ROWNUM = 0"'
refer to my thread
The example was wrong and I've corrected it, but the answer is not as simple as that suggested in your thread. I've added clarification to the article and to that thread.
Anonymous said...Thank You. Nice one :)
Carlos said...Thanks for the hint on escaping special characters on the command line example
include=TABLE:\"IN (\'EMP\', \'DEPT\')\"
On my OS the brackets also had to be escaped e.g.
include=TABLE:\"IN \(\'EMP\', \'DEPT\'\)\"
Probably time for me to start using a parameter file though
Keep up the excellent work!
Gaurav Gandhi said...The correct syntax is as below for this section -
# Multiple WHERE clause on each table.
expdp scott/tiger@pdb1 schemas=scott directory=TEST_DIR dumpfile=scott4.dmp logfile=scott4.log query='SCOTT.EMP:"WHERE deptno=10"','SCOTT.DEPT:"WHERE deptno=20"'
Both tables should be within 'XXXX'.
Thanks for the blog.
Did you try having it all within a single set of single quotes? It works just fine.
Tedro said...This site is so useful. I have been helped numerous times. Thank you very, very much!
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!
If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.