Forum Stats

  • 3,826,892 Users
  • 2,260,722 Discussions
  • 7,897,116 Comments

Discussions

Using PARALLEL and %u with datapump, metadata to import one table using just one of the dump files?

Dana N
Dana N Member Posts: 185

I'm experimenting with PARALLEL(=6 in my case) and ...%u.dmp to create faster datapump export files with expdp.

So far, I've seen a great improvement. And I know that, for impdp, it seems one must specify DUMPFILE=file1.dmp,file2.dmp, etc.

However. Would the log file and/or any internal dump file metadata, provide sufficient info to allow importing a specific single table *without* referencing all 6 dump files using impdp on the command line. Is this even a valid question to ask? In other words, is there any value in trying to identify a single constituent file for the dump of an entire schema (in my use case) when importing a single table? I'm not sure of all the details, but was asked about this and didn't know how to answer.

My suspicion is that you would, and should, specify all constituent dump files if you've got a series of files resulting from a single expdp command, using the PARALLEL switch with ...%u.dmp, when importing. But I could be wrong and often am. Thanks very much.

Answers

  • JuanM
    JuanM Member Posts: 2,155 Gold Trophy

    Hi,

    I don't know if undestand well but,

    If you export data with parallel and with sustution variablle %U, expdp utility may create a set of files in your export.

    So, when you importing, even if just to import a one table metadata, you must provide a file_name or template that contains the sustitution variable%U

    to let to impdp find all dumpfiles set, because you cannot know in which specific dumpfile resides your table.

    You can import without PARALLEL but you mut provide all dumpfiles.

    More info in http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL908

    Regards,

    Juan M

    Dana N
  • Dana N
    Dana N Member Posts: 185

    Thanks Juan. So just for others to chime in and confirm, if you use PARALLEL and specify multiple dumpfiles as an expdp option, there are two and only two options in the context of what I've asked:

    A) Specify a comma-separate list of all dumpfile names when using impdp

    or...

    B) Specify the %u wild card when using impdp; which, if I understand correctly, would be the functional equivalent of option A

    Reiterating what you said, there's no way for impdp to know which constituent dumpfile contains a given table object. Is that correct?

  • JuanM
    JuanM Member Posts: 2,155 Gold Trophy
    edited Jan 16, 2015 11:44AM
    Dana N wrote:
    
    Thanks Juan. So just for others to chime in and confirm, if you use PARALLEL and specify multiple dumpfiles as an expdp option, there are two and only two options in the context of what I've asked:
    
    A) Specify a comma-separate list of all dumpfile names when using impdp
    
    or...
    
    B) Specify the %u wild card when using impdp; which, if I understand correctly, would be the functional equivalent of option A
    
    Reiterating what you said, there's no way for impdp to know which constituent dumpfile contains a given table object. Is that correct?
    
    

    If your export information and the result is a set of dumpfiles, then, when import you must provide the same complete set of dumpfiles to impdp.

    No matter if you provide the set of dumpfile in a comma-separated way or using a template name using the %U substitution variable.

    Once, given to impdp the complete set of dumpfiles, then impdp can identify in which dumpfile(s) reside the data you want to import.

    Regards,

    Juan M

  • Richard Harrison .
    Richard Harrison . Member Posts: 2,065 Gold Trophy

    Hi,

    The master table (and the data which locates pointers to the file for the actual info)  is located in one of the files which should be enough (though i'm not completely sure) for datapump to know where the metadata/data is for a particular object. ANd in theory if this and the object data was in the same single file then only this file would be required.

    However in practice it doesn;t work - if you pick just the file with the master table in you get

    ORA-39059: dump file set is incomplete

    if you pick just another file you get

    ORA-39246: cannot locate master table within provided dump files

    SO you have to have all the files to do an import.

    Cheers,

    Rich

This discussion has been closed.