4 Replies Latest reply on Sep 20, 2019 3:21 PM by dba_since_7.2

    Is it possible to IMPDP a dump file without knowing the schema name/table name which was exported in 11g/12c ?

    2966102

      Hello Experts ,

       

       

      Is it possible to IMPDP a dump file without knowing the schema name/table name which was exported in 11g/12c ?

      If so how can this be achivded .

       

      Thanks in advance !

        • 1. Re: Is it possible to IMPDP a dump file without knowing the schema name/table name which was exported in 11g/12c ?
          EdStevens

          2966102 wrote:

           

          Hello Experts ,

           

           

          Is it possible to IMPDP a dump file without knowing the schema name/table name which was exported in 11g/12c ?

          If so how can this be achivded .

           

          Thanks in advance !

          Of course it is. In fact, the easiest, most default method does exactly that.  Have you looked at the docs to see the various options available?

           

          https://docs.oracle.com/database/121/SUTIL/GUID-E27D2DC9-A6D8-4F0B-AB72-6BF526B3AA18.htm

          • 2. Re: Is it possible to IMPDP a dump file without knowing the schema name/table name which was exported in 11g/12c ?
            GregV

            Yes it is possible, but I guess it's better to know beforehand in which schema you want to import your data, especially if that schema already exists. You can choose to import first into a sql script using the sqlfile parameter to see what will be created and in which schema(s).

            • 3. Re: Is it possible to IMPDP a dump file without knowing the schema name/table name which was exported in 11g/12c ?
              Dean Gagne-Oracle

              In order to import without knowing what was exported would take a lot of things lining up correctly.   Chances are that if you don't know where the export came from, it probably won't "JUST WORK".   Here are my reasons:

               

              1. do you know what the export was?  Was it a 'full = y', 'schemas=foo', or a 'tables=foo.tab1'.   These all present different issues:

                a. full=y will have tablespaces in the dumpfile and these tablespaces was to be created in the same directory structure.  If you don't have that directory structure

                   then your tablespaces won't import, then anything requiring those tablespaces won't work.

                b. full=foo will want to create objects in certain tablespaces, if those are not on the target database, then the import will not complete correctly.

                c. table=foo.tab1 will want to import into a tablespace and same thing, if it is not there, then the table will not be imported.  What if the table belongs to schema foo

                   and schema foo does not exist on the target database. The import will fail.

               

              2. If you are importing into a schema  that already exists and the table already exists, then you will have issues there.  That problem will exist in all three modes (full, schema, and table).

               

              3. for schema and table mode, if the tablespace exists, but you don't have privs to create object in it, then it will also fail.

               

              4. if the export was a schema mode export but was done from a non-prived account the syntax to create the schema will not exist in the dumpfile.   When the import is run, you will need to have the schema already created.  If not created, then none of the objects will be created.

               

              Your best best is to run the import with a parameter

               

                   sqlfile=my_random_import.sql

               

              This will cause the import job to write all of the create object ddl into the file 'my_random_import.sql' and not execute anything on your running database.   You can then look at that file with your favorite editor and see what it will be running.    Does it require some specific tablespace that is not created or not granted to the user?does it have the syntax to create the user if it doesn't exist on the target database?  it it a table mode job that will require the schema to exist.

               

              Also, and probably most important, let's say the dumpfile just created the create user command and then the grants.  Do you really want to run this on your database and possibly give someone an account with possibly tons of privileges?   I would really want to know what in in the dumpfile before I just import it, especially if it was in a production database.

               

              So yes, if everything lined up, like tablespaces and schemas existing, it will work, but I would check the contents of the file before blindly importing it.

               

              Hope this helps.

               

              Dean

              • 4. Re: Is it possible to IMPDP a dump file without knowing the schema name/table name which was exported in 11g/12c ?
                dba_since_7.2

                As Dean mentioned look up the SQLFILE parameter and run the import without actually importing anything and it will tell you what schemas exist in the export file.  I've done this several times.

                I didn't read everything else Dean wrote but the SQLFILE is the answer to what you said you needed.