Insert /*+ APPEND */ select * …
should be the best you can do, because this is the best you can do in this moment.
append does a direct path load (if it can, it is not a promise, you are requesting and we may or may not do it for you - silently)
if you direct path load, the transaction that did the direct path load CANNOT query that segment - but other transactions can, they just cannot see the newly loaded data.
if you direct path load, you never use any existing free space, it always writes above the high water mark.
if you direct path load, we bypass UNDO on the table - only the table - modifications
if you direct path load, you’ll maintain indexes - we build mini indexes on the newly loaded data and merge them into the ‘real’ indexes in bulk. A direct path load of large amounts of data will maintain indexes very efficiently.
if you direct path load you can bypass redo on the TABLE in archivelog mode, if the database is set up to allow nologging and you have the segment set to nologging
direct path loading bypasses the buffer cache, you write directly to the datafiles.
direct path loading is only appropriate for the first load of a segment or an increment load of lots of data - or an increment load into a table that never has any deletes (so there is no free space to consider)
-transactional systems - you probably won’t use it.
-warehouses - a tool you’ll use a lot
So when UNDO is solved now TEMP tablespace …
But this valu depends directly on your select statement…how will it use TEMP and how much, and here you cannot do nothing if plans is like that…
But you can then try to optimize select statement only … to reduce TEMP usage. Try to use Toad explain plan and enable TEMP use column to see this value
Hope this helps