Skip to Main Content
  • Questions
  • Impdp fails with errors on TEMP and UNDO

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, oracle.

Asked: January 09, 2019 - 11:14 am UTC

Last updated: January 29, 2024 - 4:01 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

In impdp i have received many times TEMP and UNDO tablespace issue, like unable to extend tablespace.

I am import the data into the default permanent tablespace then Why TEMP and UNDO is required.

---------
Thanks

and Connor said...

Just because we are using Datapump does not mean we cannot obey all the rules you have come to rely on for the Oracle Database (such as consistency and recoverability on instance crash).

What happens if the power goes out half way through your import? We can't just say:

"Whoops...sorry about your database, we lost the power and so corrupted a bunch of stuff"

So operations in datapump are protected by undo, so that we can roll them back if something goes wrong.

In terms of TEMP, don't forget that you are probably creating indexes as part of the import. And index creation means sorting the index keys. Sorting = TEMP.

For rare (and large) things like imports, I know clients that will temporarily create a massive TEMP (and potentially UNDO) tablespace (eg TEMP_PUMP and UNDO_PUMP) and use them for the duration of the import, and then resort to the standard smaller tablespaces afterwards.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle oracle, January 11, 2019 - 6:54 am UTC

Thanks for the reply.

"clients that will temporarily create a massive TEMP (and potentially UNDO) tablespace (eg TEMP_PUMP and UNDO_PUMP) and use them for the duration of the import, and then resort to the standard smaller tablespaces afterwards."

Can you please share the knowledge how we can do this (TEMP_PUMP,UNDO_PUMP) to use and then resort the standard smaller tablespace..
Connor McDonald
January 13, 2019 - 6:14 am UTC

Just via the normal DDL, eg

--
-- giant tspace
--
create temporary tablespace TEMP_PUMP datafile 'blah' 100G;
--
-- make it the default for this user
--
alter user MY_USER temporary tablespace TEMP_PUMP;
--
-- do your import
--
impdp myuser/mypass ....
--
-- reset when complete
--
alter user MY_USER temporary tablespace TEMP;
--
-- cleanup
--
drop tablespace TEMP_PUMP ;

UNDO is a little more awkward because of the undo_tablespace parameter, but I'd try with the temp first and see how you go

A reader, January 13, 2019 - 6:47 am UTC

Thanks for the reply,

Understood for TEMP and i did the same created more space with temp after to activity i drop and re-create new temp but what about the UNDO i want to Know that.

Thanks
Connor McDonald
January 14, 2019 - 12:35 am UTC

The process is similar

--
-- new giant undo tspace
--
create undo tablespace UNDO_PUMP datafile 'blah' 100G;
--
-- change the parameter
--
alter system set undo_tablespace = ....
--
-- do your import
--
impdp myuser/mypass ....
--
-- reset when complete
--
alter system set undo_tablespace = ....
--
-- cleanup
--
drop tablespace UNDO_PUMP ;

The reason I said a touch more awkward is that you need to mindful of active transactions when changing undo tablespace. But its normally rare to see *undo* issues with data pump.

oracle oracle, January 14, 2019 - 7:09 am UTC

Thanks,

Got it,

Can we avoid the high usage of TEMP and UNDO during import. like to add any additional parameter..

Connor McDonald
January 16, 2019 - 2:52 am UTC

TEMP - no, we need it to create indexes.

UNDO - I'm surprised you're seeing this. Data pump uses only a little undo because the operations are in direct mode.

https://asktom.oracle.com/pls/apex/asktom.search?tag=can-we-commit-during-import


what is wrong with resizing?

steve, January 25, 2024 - 9:26 am UTC

I found this these two commands worked before and after !
ALTER TABLESPACE TEMP RESIZE 400G;

ALTER TABLESPACE TEMP SHRINK SPACE KEEP 100G;

Connor McDonald
January 29, 2024 - 4:01 am UTC

TEMP is special because (by definition) all of its contents are temporary.

Generally it would probably be even quicker just to drop / recreate because the files are sparse

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.