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..
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
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..
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;
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
ok
Damon Parrish, May 09, 2024 - 4:30 am UTC
Since there aren't many files, it's usually faster to merely drop and reconstruct.