4 Replies Latest reply: Feb 15, 2013 3:12 PM by 991371 RSS

    UNDOTBS during Data Import (datapump)

    991371
      Hi,

      I want to know how much space does the data import occupies on the undotbs tablespace? will it consume the same exact space (size) of the table being imported? I am using impdp for importing data. (data_only).

      Also, i want to know when will ORACLE use UNDOTBS and TEMP tbs.

      Your assistance would be greatly appreciated!

      Thanks!!
      Christian George Medija
        • 1. Re: UNDOTBS during Data Import (datapump)
          sb92075
          988368 wrote:
          Hi,

          I want to know how much space does the data import occupies on the undotbs tablespace? will it consume the same exact space (size) of the table being imported? I am using impdp for importing data. (data_only).
          UNDO will be smaller than the table.
          Also, i want to know when will ORACLE use UNDOTBS
          always
          and TEMP tbs.
          Oracle uses TEMP when it needs to; like for BIG sort
          • 2. Re: UNDOTBS during Data Import (datapump)
            991371
            How do we know the exact space the import utility consumes on the UNDOTBS? is there a some sort percentage

            and during the data import will TEMP table space be used too?

            let's say the size of the table is 5GB.

            and by the way thanks for your response! :)
            • 3. Re: UNDOTBS during Data Import (datapump)
              TSharma-Oracle
              IMPDP just does the plain inserts that is what makes datapump platform independant. So if it just doing plain insert , it will definitely use Undo Tablespace. Now the question is how much?
              Impdp consume more undo tablespace because it has to run some additional metadata and some master table queries during import.BUt if you are just importing using parameter data_only, you might not go above your table size or might go little above that. For example, if you have 5GB of table, you might go 5.2 GB.

              Temporary tablespace: Impdp definitely use Temporay tablespace at lot of places while importing. One is when creating indexes, it has to do a sorting which uses you temporary tablespace.
              • 4. Re: UNDOTBS during Data Import (datapump)
                991371
                Thanks Tsharma!! that's really helpful.