I have been asked several times from different forums about the question of how we can extract ddl from dump?
In a nutshell I will try to show the procedure of how we can extract ddl from dump without importing any data.
Before proceed you need to know by which command dump was taken? Is it by exp or by expdp. Both have different procedures.I demonstrates below these two in two sections.
A) Dump was taken by exp.
B) Dump wad taken by expdp.
A) Dump was taken by exp.
1) You can simply use any viewer command and search the specified strings for the contents within it. I tried with cat, less and strings command and it worked.
Suppose in unix , strings a.dmp |grep "CREATE TABLE"
or, cat dumpfile_name |grep "YOUR SEARCH STRING"
2)you can invoke imp command with dump file name and with show=y option. The DDL command inside the dump will be shown to the screen. Like,
imp userid/password file=dumpfilename show=y
You can include log=filename with show=y option in order to save the contents inside file. Like,
imp userid/password file=dumpfilename show=y log=logfile.txt
3)A preferred method to me is to use indexfile=file.txt with imp command. It is better than show=y option. As output from indexfile=file.txt can be easily used without removing rem keyword and that script you can use easily. But output by show=y can't be used easily as double quote places in awkward places. Like you should use,
imp userid/password file=dumpfilename indexfile=file.txt
B) Dump was taken by expdp.
1)With impdp you have to use sqlfile=file.txt in which the ddl commands will be exported to file file.txt.Like,
impdp userid/password dumpfile=dumpfilename sqlfile=file.txt
Related Documents
How to get different object_type creation scripts
How to get DDL from database objects
Shouldn't that read exp and expdp instead of emp?
ReplyDeleteSorry, Thanks for pointing that. It is corrected.
ReplyDelete